Automatically insert : for hhmm textbox?

[renraw9002]

I have multiple userforms for a spreadsheet and some textboxes require time entry. We always write time using hhmm and using 4 digits. I'm not really worried about people accidentally typing in letters or using 3 numbers or anything like that. I'd just like it so that when people type 0123, 1234, etc it will automatically insert a : between the second and third numbers.

If there isn't a simple way to just automatically insert the colon between the 2nd and 3rd numbers is there a simple code I can put in to check the textbox for a colon and if it's missing, alert the user to insert one and try hitting save again?

 

[Akuini]

@renraw9002, welcome to MrExcel
Maybe something like this:

Private Sub TextBox1_Change()
With TextBox1
If .Text Like "####" Then 'if the text is 4 digit
   .Text = Left(.Text, 2) & ":" & Right(.Text, 2)
End If
End With
End Sub

 

[renraw9002]

@renraw9002, welcome to MrExcel
Maybe something like this:

Private Sub TextBox1_Change()
With TextBox1
If .Text Like "####" Then 'if the text is 4 digit
   .Text = Left(.Text, 2) & ":" & Right(.Text, 2)
End If
End With
End Sub

I could kiss you. I've been searching for weeks for this. Seen codes very similar, but it was never on textbox change and it never worked. It even works for me on both the search function and when saving info which is even better than I had hoped. Thank you so much.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Akuini]

Additionally, if you want, you can add a validation on Exit event, like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Text <> "" Then
    If Not TextBox1.Text Like "##:##" Then
        MsgBox "Wrong input"
        Cancel = True
    End If
End If
End Sub

It will prevent user to exit if the text doesn't match the pattern.

 

[renraw9002]

Additionally, if you want, you can add a validation on Exit event, like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Text <> "" Then
    If Not TextBox1.Text Like "##:##" Then
        MsgBox "Wrong input"
        Cancel = True
    End If
End If
End Sub

It will prevent user to exit if the text doesn't match the pattern.

Above and beyond. I was actually talking to a coworker about looking for something like this very thing after i showed off how your first code worked so well.

Due to a series of events starting 3 months ago somehow I got a really big spreadsheet project despite my admission I haven't used excel since probably 7th grade. Learned to add formulas and a bunch of other excel things. Next thing I know my spreadsheet becomes a formal big project and they want forms so people don't have to see the same big scary spreadsheet I do. I've gotten pretty far I think, but these weird coding protections elude me.

I can take a computer apart and put it back together fine probably, but computer coding? Forget it.

I also learned how to make textboxes only accept numbers tonight so that's... exciting lol.

I have a weird question though. So # is obviously the symbol for numbers. What's the symbol for letters in general? I have some textboxes that need info that is in an odd, specific format with numbers, letters and hyphens. I feel like I could pull something together like your hh:mm protection if I knew.

 

[Akuini]

So # is obviously the symbol for numbers. What's the symbol for letters in general?

Please, take a look at these:

Like operator

Office VBA reference topic

learn.microsoft.com

Like Operator - Microsoft Support

Use the Like operator to find values in a field that match the pattern you specify. For example, Like “Smith” or Like “Sm*”.

support.microsoft.com

I have some textboxes that need info that is in an odd, specific format with numbers, letters and hyphens

I probably can help you if you can give me the specific. Here's a discussion about validating textbox entry with some specific pattern:
Number restriction in VBA UserForm

 

[renraw9002]

Please, take a look at these:

Like operator

Office VBA reference topic

learn.microsoft.com

Like Operator - Microsoft Support

Use the Like operator to find values in a field that match the pattern you specify. For example, Like “Smith” or Like “Sm*”.

support.microsoft.com

I probably can help you if you can give me the specific. Here's a discussion about validating textbox entry with some specific pattern:
Number restriction in VBA UserForm

This is where my coding ignorance kicks in. I feel like I'm close to something, but not quite there. These textboxes have to read like "a-##aaa####-a", a meaning any letter. On an even more advanced level each textbox has to start with a specific letter. One entry will always start with "a-", another with "b-", etc.,

 

[Akuini]

One entry will always start with "a-"

1. Is it "a-" or "A-" or it can be both?
2. Are all the letters must be Uppercase or Lowercase or they can be both?

 

[renraw9002]

1. Is it "a-" or "A-" or it can be both?
2. Are all the letters must be Uppercase or Lowercase or they can be both?

Technically either is fine, but preference is capital just for ease of reading.