Beyond Excel: VBA and Database Manipulation

Dec 3 2009   9:23PM GMT

Looking for a Date?

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post I introduced frmSelect_Multiple that helps users find:

  • Codes/Types by Code/Type or Description
  • Cities by Zipcode or Name
  • Accounts by Number or Description
  • Employees, Customers, or Vendors by ID/Number or Name
  • Inventory/Stock Items or Products by SKU/UPC/GTIN or Description
  • Books by ISBN or Title
  • etc., etc.

I promised to show in this post how frmSelect_Multiple integrates to frmPrompt without code changes.  I ask your forgiveness as I introduce another support form for frmPrompt instead, frmDatePicker.

Shown at right is frmDatePicker.  It is inspired heavily by an article in VBA Tips.  Why I haven’t included this long ago is only a testament to my ability to overlook the obvious sometimes.  Oh, well, better late than never.



First, get to the VBE (Alt-F11) and add the Microsoft Calendar Control using the menu path Tools > References  -or-  Tools > Additional Controls.  Add the following controls:

Name Type Properties
frmDatePicker UserForm Caption:=“Pick Date”
calCalendar Calendar ShowTitle:=False
cmdExit CommandButton Caption:=”OK”, Default:=True,
cmdOK CommandButton Caption:=”Exit”, Cancel:=True,

The command buttons should be behind the calendar control.  They need to be there to respond to the keyboard.  Remember there are still some of us around that like the keyboard (which is why I probably very inconsiderately overlooked this form for so long). 

Add this code 

'Version: 12/01/09
Option Explicit
Dim bOK As Boolean
'   Name:   frmDatePicker
'   Purpose:Display a status message under program control to the user
'     Date   Init Modification
'   12/01/09 CWH  Initial Programming
'   Example (How to use this form):
'Private Sub cmdFrom_Click()     
'    On Error GoTo ErrorHandler     
'    With frmDatePicker 
'        .Top = Me.Top + cmdFrom.Top + 20 
'        .Left = Me.Left + cmdFrom.Left + cmdFrom.Width + 8 
'        .pDate = IIf(IsDate(txtFrom), CDate(txtFrom), Int(Now())) 
'        .Show 
'        Do While .Visible 
'            DoEvents 
'        Loop 
'        If .pOK Then txtFrom = .pDate 
'    End With     
'    On Error GoTo 0
'End Sub
'   Date
Public Property Let pDate(dDate As Date)
    If IsDate(dDate) Then
        With calCalendar
             .Day = Day(dDate)
             .Month = Month(dDate)
             .Year = Year(dDate)
         End With
    End If
End Property
Public Property Get pDate() As Date
    With calCalendar
         pDate = DateSerial(.Year, .Month, .Day)
     End With
End Property
'   OK
Public Property Get pOK() As Boolean
    pOK = bOK
End Property
'   Event Handlers
Private Sub calCalendar_DblClick()
End Sub
Private Sub calCalendar_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    bOK = False
End Sub
Private Sub cmdExit_Click()
    bOK = False
End Sub
Private Sub cmdOK_Click()
    bOK = True
End Sub

Next post I promise most sincerely to integrate all of this into a better, frmPrompt.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: