When my spreadsheet starts a userform comes up up with a recommendation for
"time in". I can change the time, but I do not want to be able to delete or
change the colon. Actually I do not want to be able to delete the recommended
time either, just be able to change it: I do not want my textbox to be empty.
How can I do this?

Here's the code:

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False

Dim TimeOut As String
Dim TimeNow As Date
Dim TimeNowMinus As String
Dim Time as String

TimeNow = WorksheetFunction.Text(Now(), "hh:mm")
Time = "14:50"
TimeNowMinus = Format(Now - Date - TimeValue("00:03:00"), "hh:mm")

Me.StartUpPosition = 0
Me.Top = 186
Me.Left = 382

Select Case True
Case TimeNow < Time
OptionButton1.Value = True
TextBox1.Text = TimeNowMinus

Case Else
OptionButton2.Value = True
TextBox1.Text = "16:00"
End Select

With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With

Application.ScreenUpdating = True
End Sub

There are several ways, including masked text boxes (look in additional
controls). After doing it several different ways i've come to rely on 2 text
boxes seperated by a label with a colon, one textbox for hours, the other for
minutes and you can put them together through code with the :. another add to
help them is to set the max length of the text boxes to 2 and you can run
code on textbox change that gets the len(textbox1)=2 then select textbox2.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Erik" wrote:

When my spreadsheet starts a userform comes up up with a recommendation for
"time in". I can change the time, but I do not want to be able to delete or
change the colon. Actually I do not want to be able to delete the recommended
time either, just be able to change it: I do not want my textbox to be empty.
How can I do this?

Here's the code:

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False

Dim TimeOut As String
Dim TimeNow As Date
Dim TimeNowMinus As String
Dim Time as String

TimeNow = WorksheetFunction.Text(Now(), "hh:mm")
Time = "14:50"
TimeNowMinus = Format(Now - Date - TimeValue("00:03:00"), "hh:mm")

Me.StartUpPosition = 0
Me.Top = 186
Me.Left = 382

Select Case True
Case TimeNow < Time
OptionButton1.Value = True
TextBox1.Text = TimeNowMinus

Case Else
OptionButton2.Value = True
TextBox1.Text = "16:00"
End Select

With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With

Application.ScreenUpdating = True
End Sub