Userform for time entry: multiple controls/single value?

[smplogc]

This is an awesome site.

I just started using Excel. I would like to be able to have the time entered simply in a column with a userform that has a combobox for the hour, a combobox for the minutes, and two option buttons for AM/PM.

I made the userform with the controls laid out, and attached lists to the two comboboxes using a range of cells in a new sheet (1-12 for hours, 00-59 for minutes), but now I'm having trouble finding an example to work off of for a way to "link" the controls together so that they combine to produce a single value, as with how the calender control works. And, of course, I need code to make it work!

The question might make no sense at all, but I know nothing of VBA as of yet.

I would like to keep the code within the workbook rather than placing it externally.

If anyone knows of any examples I can use, or has some tips for me, I'll finallly be able to close my eyes and stop these hallucinations. Thanks!

I've attached a small image of my prototype time-input!

[MattShoreson]

Setup as follows:
Hours LOV name = cboHours
Minutes LOV name = cboMins
Option AM name = optAM
Option PM name = optPM

In the code behind form:
Option Explicit
Dim intHour As Integer
Dim intMin As Integer

Const cstrCOLON As String = ":"
Const cstrSPACE As String = " "

Const strSTARTMIN As String = "00"
Const strSTARTHOUR As String = "12"

Private Sub UserForm_Initialize()
Me.optAM = True
Me.cboMin.Value = strSTARTMIN
Me.cboHour.Value = strSTARTHOUR
End Sub

Function GenerateTime() As String
With Me
If .cboMin = vbNullString Or .cboHour = vbNullString Then
MsgBox "please select a valid time."
Else
GenerateTime = .cboHour & cstrCOLON & .cboMin & cstrSPACE & AMPM
End If
End With
End Function

Function AMPM() As String
With Me
If .optAM Then
AMPM = "AM"
Else
AMPM = "PM"
End If
End With
End Function

[K Dales]

I apologize because I could not open the link to your example but I will try
to sketch out the way to do this. I will assume the comboboxes are ComboBox1
for the hour, ComboBox2 for the minute, OptionButton1 for am and
OptionButton2 for pm. Also, I have an "OK" button I will call CommandButton1
to accept the input. I will also assume you want the result to go into the
currently selected cell on the worksheet.

Start by showing your form design in the VBA editor. Right-click on the OK
button and choose "View Code" from the pop-up menu. It will start you with:

Private Sub CommandButton1_Click()

End Sub

You need to put the code in between the Private Sub line and the End Sub
line; here is the whole thing - I have put comments to help you understand
and maybe start learning a bit of VBA:

Private Sub CommandButton1_Click()

Dim TStr As String
' Build the time string from the user input
TStr = Me.ComboBox1 & ":" & Me.ComboBox2 & " "
If Me.OptionButton2 Then TStr = TStr & "pm" Else TStr = TStr & "am"
' Check to make sure it is a valid time (otherwise give message):
If IsDate(TStr) Then
ActiveCell.Value = TimeValue(TStr)
' To hide the input form:
Me.Hide ' omit this if you want to keep the form showing
Else
MsgBox "You need to input a valid time", vbInformation, "ERROR:"
End If

End Sub

--
- K Dales


"smplogc" wrote:

>
> This is an awesome site.
>
> I just started using Excel. I would like to be able to have the time
> entered simply in a column with a userform that has a combobox for the
> hour, a combobox for the minutes, and two option buttons for AM/PM.
>
> I made the userform with the controls laid out, and attached lists to
> the two comboboxes using a range of cells in a new sheet (1-12 for
> hours, 00-59 for minutes), but now I'm having trouble finding an
> example to work off of for a way to "link" the controls together so
> that they combine to produce a single value, as with how the calender
> control works. And, of course, I need code to make it work!
>
> The question might make no sense at all, but I know nothing of VBA as
> of yet.
>
> I would like to keep the code within the workbook rather than placing
> it externally.
>
> If anyone knows of any examples I can use, or has some tips for me,
> I'll finallly be able to close my eyes and stop these hallucinations.
> Thanks!
>
> I've attached a small image of my prototype time-input!
>
>
> +-------------------------------------------------------------------+
> |Filename: example.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=4702 |
> +-------------------------------------------------------------------+
>
> --
> smplogc
> ------------------------------------------------------------------------
> smplogc's Profile: http://www.excelforum.com/member.php...o&userid=33887
> View this thread: http://www.excelforum.com/showthread...hreadid=536660
>
>

[smplogc]

Thank you, Matt! And thank you, K Dales!

Matt, I couldn't get your code to work. I renamed the controls as you stated (cboHours, cboMins, optAM, optPM), but in your code you make reference to cboHour and cboMin. I'm guessing that you forgot the "s", so I just omitted it in the control names.

I pasted your code (lines 'Option Expicit' to 'End Function') in the userform's code window with no other code present.

I made the userform appear in the worksheet by just adding test code under Worksheet_SelectionChange for one cell.

The way I would like this time-input form to work is that it would appear when any of a range of cells in a column is selected, and disappear when a different cell is selected (but using the form's close button is sufficient). I would like to have the user select the hour and minute, and when AM or PM is selected, the value would be entered, eliminating the need for a separate command button.

I'm trying to duplicate the way the calender control works. I've set up the calender control for a range of cells in a different column. When any cell in the range is selected, the calender appears, and when a different cell is selected, it disappears. I'm trying to figure out how to use SelectionChange for multiple forms so that a particular form will pop up for the appropriate range of cells, without them interfering with each other (appearing and disappearing properly).

Thanks so much for your help. I'm going to make more attempts to get your code to work. I'm like a monkey on a typewriter. And I feel guilty that you're not getting paid for this!

K Dales, I used your code and it worked great! I added the code twice for the two option buttons (my form has no command button), but I see that it's not ideal to use the option buttons for entering the time value because they need to be deselected and reselected to re-enter changed values, so I'm thinking of changing them to command buttons.

Any tips on having one of a number of forms appear based on cell selection? Hehe, I say "tips", but you guys do all the work!