Hi Les -
Have you considered a calendar control ? I have yet to find a case where a
combo or listbox outperforms the calendar control for selecting dates. You
can get an outstanding version from Martin Green's web site at:
http://www.fontstuff.com/downloads/index.htm
Download Martin's "Excel Calendar Workbook.zip". Open the workbook, Export
his frmCalendar form, Import it into your workbook, and modify the form code.
Change the three instances of "ActiveCell" to "Range("B6")" to suit your
application.
After doing that, you have several options to trigger the calendar (show it
to the user). Here are two options:
Option 1: You can use Martin's right-click shortcut menu control "Insert
Date". If this is your choice, copy all of the code in Martin's ThisWorkbook
module to your ThisWorkbook module. To choose a date for cell B6, just
right-click in any cell and choose "Insert Date" from the shortcut menu.
A sub-option here is to modify Martin's code to run in the worksheet's
Activate and Deactivate event procedures. Then, the Insert Date option will
only be available from a right-click in the single worksheet.
Option 2: Add a command button to your worksheet from the Control Toolbox,
then:
a. Right-click the button, choose |Format Control...|. Click the
[Properties] tab and clear the "Print Object" checkbox and click [OK].
b. Right-click the button again, choose Properties, and modify the Caption
property to "Enter Date" (or whatever you like on the button face).
c. Right-Click the button again, choose View Code, and add the following
code in the worksheet module that opens:
Private Sub CommandButton1_Click()
frmCalendar.Show
End Sub
Repost if you'd like to take a different approach or if you need help
modifying Martin's code.
