Another Calendar Challenge

[radar2000]

Hi everyone.

I have a spreadsheet which contains a bunch of fields that have validation drop-down lists.

For this one field (Date of Sale) I was wondering if there is a way that instead of a validation drop-down menu, that a calendar box could pop-up instead. This pop-up would allow the user to select a date from the calendar.

If someone has any suggestions, that would be appreciated.

Thank you.

 

[Ian Mac]

My suggestion would be:

Create and button - 'CommandButton1'

from the Controls menu and place in in the cell next to the one you need the date in.

goto VBE and add a UserForm - 'UserForm1'
Add the calander control - 'Calander1'
Add a button - Caption = 'OK' Name = 'CommandButton1'

Not ad the following code:

To the sheet button:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

To the Form button:

Private Sub btnOK_Click()

Unload UserForm1

Sheets("sheet1").Range("c7").Value = Me.Calendar1.Value
Sheets("sheet1").Range("c7").Select

End Sub

Change Sheet and cell references to suit.

Sorry it's a bit rushed and may not be clear, but I have to leave work now,
If you have and trouble post back and I'm sure others will help.

 

[radar2000]

Hi Ian.

Thank you for your quick reply. This forum really works!!!

I got the calendar to display as soon as I hit the button on the spreadsheet. The only problem, is that it does not display the result in the specified cell (c7..as per your code). Please let me know if you have any suggestions.

Ian, the purpose of this spreadsheet is for sales people to keep track of sales on a daily basis. Therefore I need this calendar to appear on each row when they hit the Date Of Sale field.

For example:

Cell A2 will be DEALER ID (This will have a validation drop down list).

Cell B2 will be EMPLOYEE NAME (This will have a validation drop down list).

Cell C2 will be DATE OF SALE (This is where I would like to have a small pop-up calendar appear. Which ever date that person selects, will need to be populated into that field. Please note that if need be, the result can appear in a different cell..for example D2, but I would prefer the first option.

I know I could just create a validation drop-down list linked to a specified date of data, but that might take a while to scroll through.

Ian, if you can work your magic on this one, that would be much appreciated.

Thank you.

 

[radar2000]

Hi Ian.

Thank you for your quick reply. This forum really works!!!

I got the calendar to display as soon as I hit the button on the spreadsheet. The only problem, is that it does not display the result in the specified cell (c7..as per your code). Please let me know if you have any suggestions.

Ian, the purpose of this spreadsheet is for sales people to keep track of sales on a daily basis. Therefore I need this calendar to appear on each row when they hit the Date Of Sale field.

For example:

Cell A2 will be DEALER ID (This will have a validation drop down list).

Cell B2 will be EMPLOYEE NAME (This will have a validation drop down list).

Cell C2 will be DATE OF SALE (This is where I would like to have a small pop-up calendar appear. Which ever date that person selects, will need to be populated into that field. Please note that if need be, the result can appear in a different cell..for example D2, but I would prefer the first option.

I know I could just create a validation drop-down list linked to a specified date of data, but that might take a while to scroll through.

Ian, if you can work your magic on this one, that would be much appreciated.

Thank you.