can i have field on a userform as a selecable date?

darrenfinlayson

New Member
Joined
Feb 26, 2004
Messages
13
to cut down on user validation? like a little icon to the right of a textbox which will only accept dates from a calendar control?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jimbojones

Well-known Member
Joined
Apr 22, 2002
Messages
776
Best thing I've seen is this:

Sourced I think from Ozgrid (apologies if this is wrong): Just need to drive form from a button on your dialog.

1. Open the workbook for the calendar.  It is a good idea to use your Personal.xls for this, in which case you should first go to Window>Unhide

2. Go to Tools>Macro>Visual Basic Editor (Alt+F11).

3. Go to Insert>UserForm from within the VBE. This should automatically display the Control Toolbox, if not go to View>Toolbox

4. Right click on the Toolbox and select Additional Controls

5. Scroll through the list until you see: Calendar Control 10.0 (number will differ depending on Excel version) and check the checkbox and click OK

6. Now click the Calendar that is now part of the Toolbox and then click on the UserForm we inserted in step 3.

7. Use the Size Handles on both the UserForm and the Calendar Control to make them both a reasonable size. See Example below.


8. Now ensure the UserForm is selected (as shown above) then go to View>Properties Window (F4)

9. Select Caption from the Properties Window and replace: UserForm1 with the word Calendar.

10. Now go to View>Code (F7) and in the white Private Module in front of you, add the code exactly as show below:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value

ActiveCell.NumberFormat="mm/dd/yy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

11. Now go to Insert>Module and in this Public Module place this code

Sub ShowIt()
UserForm1.Show
End Sub

12. Ok, nearly done. Click the top right X (or push Alt+F11) to return back to Excel.

13. Go to Tools>Macro>Macros (Alt+F8) and then select ShowIt click Options and assign a shortcut key and you're done.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,332
Messages
5,769,470
Members
425,552
Latest member
learnerrr

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