UserForm for input date & time

[Airborne]

Hi. I've created a UserForm that's pops up when someone wants to use a hidden sheet in my workbook. On the UserForm ? have optionbuttons where they can select the sheet. But I also want to make it possible for people to choose the data and time and if they click [Ok] the date will come in one cell and the time in an other cell on the chosen sheet. I don't know how to set that up in my UserForm.

[johnske]

Hi Airborne,

Put two labels on your userform and put this code into the userform

Private Sub UserForm_activate()
Label1.Caption = Date
Label2.Caption = Time

End Sub
John

[Jacob Hilderbrand]

If you want to put the date and time on the worksheet use this:

Sheets("Sheet1").Range("A1") = Date
Sheets("Sheet1").Range("B1") = Time

[Airborne]

Thanks, maybe I've not been clear, sorry. But will this code not put the current date and time on the form? I want people to put in the date and time they want and if they click [Ok] the date and time will come in two seperate cells on a worksheet.

[Jacob Hilderbrand]

Put two text boxes on the userform to allow the user to type into then:

Sheets("Sheet1").Range("A1") = TextBox1.Value
Sheets("Sheet1").Range("B1") = TextBox2.Value

[Airborne]

Thanks, it works. One (final ) question about the form. People have to use the time format e.g. 15:40. If they use 15.40 the time won't be displayed correct. How can I force them (apart from a text in the form) to use the right format?

[johnske]

Just format the target cells in the time format you want to see

[Airborne]

I've tried that. The format of the cell is hh:mm. it will show e.g. 15:58 when you put 15:58 in the form. But if I try 15.58 it will show up as 13:55.

[johnske]

Hang on, you're using text boxes for them to input the time now aren't you?

If so, there's no need to format the target cells in time format, just format them as 'General' or 'Text' and if they type in either 9.30 or 9:30 then that's how it'll be displayed.

[Airborne]

, that works. Thanks Johnske and Jacob.

[Jacob Hilderbrand]

You're Welcome

Take Care