?
Solved

Userform Time question

Posted on 2009-11-03
6
Medium Priority
?
82 Views
Last Modified: 2014-04-23
I have a userform that has a text box (txtFollowupTime1) that the user will enter a time.  This text box needs to be controlled by a spinner (spnFollowupTime1).  The user needs to be able to select the hours part and adjust it by one and then select the minutes and adjust in 15 minute increments all using the spinner.  The times will be between 8am and 9pm.  I have seen this done before but cannot find it anymore.  Any help would be appreciated.
0
Comment
Question by:Hamilj03
6 Comments
 
LVL 9

Expert Comment

by:experts1
ID: 25734380
Hamilj03,

Could you provide example of the current code which you are using to control the textbox?
0
 

Author Comment

by:Hamilj03
ID: 25734470
Here is the code.  It works but it only does it one minute at a time,  I want to change the hours idependent of the minutes and change the minutes in 15 minute increments.
Private Sub UserForm_Initialize()
     
     txtFollowupTime1.Value = Format(0.3333333, "hh:mm AM/PM")
     spnFollowupTime1.Value = 480
 
End Sub
Private Sub spnFollowupTime1_SpinDown()
    txtFollowupTime1 = Format(txtFollowupTime1.Value, "00.00")
If txtFollowupTime1 = "" Then
    txtFollowupTime1.Value = spnFollowupTime1.Value
Else: txtFollowupTime1 = spnFollowupTime1.Value / 1440 - CInt(txtFollowupTime1.Value)
End If
txtFollowupTime1 = Format(txtFollowupTime1.Value, "hh:mm AM/PM")
End Sub
 
Private Sub spnFollowupTime1_SpinUp()
    txtFollowupTime1 = Format(txtFollowupTime1.Value, "00.00")
If txtFollowupTime1 = "" Then
    txtFollowupTime1.Value = spnFollowupTime1.Value
Else: txtFollowupTime1 = spnFollowupTime1.Value / 1440 + CInt(txtFollowupTime1.Value)
End If
txtFollowupTime1 = Format(txtFollowupTime1.Value, "hh:mm AM/PM")
End Sub

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 25734679
I would recommend using two textboxes (one for hours, one for minutes) if that's an option?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Accepted Solution

by:
experts1 earned 2000 total points
ID: 25746472
Hamilj03,

Please see attached example excel file along with the code snippet below for the userform code.

This example utilize the selStart property of the userform textbox to govern which segment of the displayed time to adjust.

The textbox is disabled, but the text can still be selected.

This just meant as a rough guide for you  to proceed with your idea.

Regards



Dim sel_st As Integer
Dim sel_ln As Integer
Dim init_disp As String
Dim x_disp As String
Dim x_ampm As String
Dim x_hour As String
Dim x_minutes As String
Private Sub spnFollowupTime1_SpinDown()
Call store_disp
If sel_st <= 2 Then
  x_hour = Right("00" & CInt(x_hour) - 1, 2)
  If CInt(x_hour) = 0 Then x_hour = "12"
  'MsgBox x_hour
End If
If sel_st > 2 And sel_st < 5 Then
  'x_minutes = Right("00" & CInt(x_minutes) - 15, 2)
  If CInt(x_minutes) >= 15 Then
    x_minutes = Right("00" & CInt(x_minutes) - 15, 2)
  Else
    x_minutes = Right("00" & (60 + CInt(x_minutes)) - 15, 2)
  End If
End If
If sel_st > 4 Then
  If UCase(x_ampm) = "AM" Then
    x_ampm = "PM"
  Else
    x_ampm = "AM"
  End If
End If
x_disp = x_hour & ":" & x_minutes & " " & x_ampm
Call reStore_disp
End Sub
Private Sub spnFollowupTime1_SpinUp()
Call store_disp
If sel_st <= 2 Then
  x_hour = Right("00" & CInt(x_hour) + 1, 2)
  If CInt(x_hour) > 12 Then x_hour = "01"
End If
If sel_st > 2 And sel_st < 5 Then
  x_minutes = Right("00" & CInt(x_minutes) + 15, 2)
  If CInt(x_minutes) > 59 Then x_minutes = Right("00" & CInt(x_minutes) - 60, 2)
End If
If sel_st > 4 Then
  If UCase(x_ampm) = "AM" Then
    x_ampm = "PM"
  Else
    x_ampm = "AM"
  End If
End If
x_disp = x_hour & ":" & x_minutes & " " & x_ampm
Call reStore_disp
End Sub
Private Sub UserForm_Click()
 
End Sub
Private Sub UserForm_Initialize()
     UserForm1.txtFollowupTime1.Locked = True
     UserForm1.txtFollowupTime1.Value = Format(0.333333, "hh:mm AM/PM")
     init_disp = Trim(UserForm1.txtFollowupTime1.Value)
End Sub
Sub store_disp()
x_disp = Trim(UserForm1.txtFollowupTime1.Value)
sel_st = UserForm1.txtFollowupTime1.SelStart
sel_ln = UserForm1.txtFollowupTime1.SelLength
x_ampm = Right(x_disp, 2)
x_minutes = Mid(x_disp, 4, 2)
x_hour = Left(x_disp, 2)
End Sub
Sub reStore_disp()
UserForm1.txtFollowupTime1.SetFocus
UserForm1.txtFollowupTime1.Value = x_disp
UserForm1.txtFollowupTime1.SelStart = sel_st
UserForm1.txtFollowupTime1.SelLength = sel_ln
End Sub

Open in new window

UserForm-Time-Question.xls
0
 

Author Closing Comment

by:Hamilj03
ID: 31649658
Thanks for the help.  I have found out a real eassy way using the dtpicker control built into VBA.  It does exactly what I am looking for.  Thanks for your help.
0
 
LVL 1

Expert Comment

by:tracyms
ID: 40019189
Hi experts1,

I've posted a question about your UserForm and thought you may want to take a look since you posted the original code. The link is below:


http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28418910.html

Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question