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

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


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=536660

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

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!


--
smplogc
------------------------------------------------------------------------
smplogc's Profile: http://www.excelforum.com/member.php...o&userid=33887
View this thread: http://www.excelforum.com/showthread...hreadid=536660