Hi all, i have a spreadsheet that i need to insert either a list or
combo box (Not sure which is best), so that the user can sellect a date
(Jan-07 to Dec-07)with the selected date going into "B6". I then also
have to remove the Box as the user must send the sheet by e-mail but the
date in B6 must remain. Could somebody please help me with some code to
do this ??

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

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.

--
Jay


"Les Stout" wrote:

Hi all, i have a spreadsheet that i need to insert either a list or
combo box (Not sure which is best), so that the user can sellect a date
(Jan-07 to Dec-07)with the selected date going into "B6". I then also
have to remove the Box as the user must send the sheet by e-mail but the
date in B6 must remain. Could somebody please help me with some code to
do this ??

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

If you want to use a userform in VBA this is real simple code

1) on VBA insert menu select user form.
2) If toolbox doesn't show up thenlook for ICON with hammer and wrench.
3) From toolbox put listbox on the form.
4) Double click list box A new window should appear with two lines of code.
Add to this code the statementt - Userform1.hide
5) Run the code below from a module window.


Sub xyz()
Mydate = DateValue("1/1/" + CStr(Year(Date)))
Do While (1)
UserForm1.ListBox1.AddItem CStr(Mydate)
If Mydate = DateValue("12/31/" + CStr(Year(Date))) Then Exit Do
Mydate = Mydate + 1

Loop
UserForm1.Show
SelectedDate = UserForm1.ListBox1.Text
msgbox(SelectedDate)

End Sub


"Les Stout" wrote:

Hi all, i have a spreadsheet that i need to insert either a list or
combo box (Not sure which is best), so that the user can sellect a date
(Jan-07 to Dec-07)with the selected date going into "B6". I then also
have to remove the Box as the user must send the sheet by e-mail but the
date in B6 must remain. Could somebody please help me with some code to
do this ??

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thanks all for the input, i will try all and use the most suitable...
Great weekend... :-)

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***