Combo Box Showing Date Selections

T

TiredAlison

Hi
I have a spreadsheet with a combo box setup. I want it to display in the
pull down dates in the format dd/mm/yy from todays date - 5 days to todays
date + 30 days. I have searched the help and discussion post and found code
to get it to pull down in the format mm/dd/yy from a specific date but I am
not clever enough to alter this to what I want. If you want me to post the
code I have so far let me know.
thanking you in advance for this help.
 
S

smartin

TiredAlison said:
Hi
I have a spreadsheet with a combo box setup. I want it to display in the
pull down dates in the format dd/mm/yy from todays date - 5 days to todays
date + 30 days. I have searched the help and discussion post and found code
to get it to pull down in the format mm/dd/yy from a specific date but I am
not clever enough to alter this to what I want. If you want me to post the
code I have so far let me know.
thanking you in advance for this help.

If you are using the VB combo, you could try something like this:

For i = -5 To 30
Sheet1.ComboBox1.AddItem Format(Date + i, "dd/mm/yyyy")
Next i
 
T

TiredAlison

smartin said:
If you are using the VB combo, you could try something like this:

For i = -5 To 30
Sheet1.ComboBox1.AddItem Format(Date + i, "dd/mm/yyyy")
Next i
.
Thanks for your help but when I cleared out all my code and only put your code in it doesn't display anything in the pulldown. When I try and type a value I get "Compile Error. Method or data member not found". I entered the code by going into design mode and view code. It called the subroutine ComboBox1_Change().
 
S

smartin

Try using a more generic event procedure, not one specific to the
combobox itself. Something like worksheet_activate. You can get there by
double-clicking "Sheet1" in the VBA Project navigator.

Then, from the two comboboxes above the code window (the ones that say
"(General)" and "(Declarations)" by default), pick Worksheet and
Activate, and paste the suggested code in the Sub stub.

Return to the workbook, toggle to some other worksheet and back again.
Check the combo. Any luck?
 
T

TiredAlison

smartin said:
Try using a more generic event procedure, not one specific to the
combobox itself. Something like worksheet_activate. You can get there by
double-clicking "Sheet1" in the VBA Project navigator.

Then, from the two comboboxes above the code window (the ones that say
"(General)" and "(Declarations)" by default), pick Worksheet and
Activate, and paste the suggested code in the Sub stub.

Return to the workbook, toggle to some other worksheet and back again.
Check the combo. Any luck?
.
No luck .. when I swapped to an alternate sheet it gave "Compile Error.
Method or data member not found" and .Combox1 went blue. When I close the
debugger and go back to original sheet it seems OK but there is nothing in
the pull down.

Code used wass as follows:

Private Sub Worksheet_Activate()
For i = -5 To 30
Sheet1.ComboBox1.AddItem Format(Date + i, "dd/mm/yyyy")
Next i
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top