Another Calendar Challenge

radar2000

Board Regular
Joined
Apr 15, 2002
Messages
85
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Board Regular
Joined
Apr 15, 2002
Messages
85
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

Board Regular
Joined
Apr 15, 2002
Messages
85
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.
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top