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?

 

[dmt32]

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-",

Hi

You should be able to do that by creating a Function for all textboxes from any userform in your workbook that need to call it and passing to it, the required user entry elements for each textbox as arguments. Once entry correctly completed, the function returns True which you use to determine how your calling code proceeds.

The benefit of using a Function is that it saves repeating the same code throughout your project.

See if following will do what you want

Place following code in a STANDARD module

Function IsCorrect(ByRef Box As Object, ByVal Prefix As String, ByVal Suffix As String) As Boolean
        Box.Value = UCase(Box.Value)
        IsCorrect = Box.Value Like UCase(Prefix) & "##[A-Z][A-Z][A-Z]####" & UCase(Suffix)
End Function

Example use

Private Sub TextBox1_Change()

  If IsCorrect(Me.TextBox1, "A-", "-A") Then
  
    'entry is correct
     Me.TextBox1.BackColor = rgbLightGreen
     
  Else
    'entry not correct
    Me.TextBox1.BackColor = rgbRed
  
  End If
End Sub

In this example, the textbox should turn red until the value entered, matches the pattern specified where it should then turn green. I have also included the automatic change of all text entered to Uppercase.

Hope Helpful

Dave

 

[Akuini]

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.,

Does the data that will be entered into the textbox already exist in the workbook?
If it does, you should consider using a combobox instead of a textbox. With a searchable combobox, you won't have to type the entire word. Just enter a few characters then you can select an item from the list.

 

[Akuini]

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.,

Utilizing the textbox Exit event for input validation, here is my suggestion:

Private Sub TextBox1_Change()
With TextBox1
    .Text = UCase(.Text) 'convert the text to Uppercase
End With
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
    If .Text <> "" Then
        'pattern: "A-" & 2 digit & 3 letter & 4 digit & "-" & 1 letter > example: A-21BGF5634-W
        If Not .Text Like "A-##[A-Z][A-Z][A-Z]####-[A-Z]" Then
            Beep
            MsgBox "Wrong input! It must follow this pattern:" & vbLf & "A-##@@@####-@" & vbLf & _
                    "# means any number" & vbLf & "@ means any letter" & vbLf & _
                    "Example: A-21BGF5634-W"
            Cancel = True
        End If
    End If
End With
End Sub

Note:

• Any lowercase letters entered by the user will automatically be converted to uppercase.
• The message box provides more detailed instructions to help user understand what input is required.
• You can adjust the code for other textboxes to suit. How many textboxes need input validation?

 

[renraw9002]

Hi

You should be able to do that by creating a Function for all textboxes from any userform in your workbook that need to call it and passing to it, the required user entry elements for each textbox as arguments. Once entry correctly completed, the function returns True which you use to determine how your calling code proceeds.

The benefit of using a Function is that it saves repeating the same code throughout your project.

See if following will do what you want

Place following code in a STANDARD module

Function IsCorrect(ByRef Box As Object, ByVal Prefix As String, ByVal Suffix As String) As Boolean
        Box.Value = UCase(Box.Value)
        IsCorrect = Box.Value Like UCase(Prefix) & "##[A-Z][A-Z][A-Z]####" & UCase(Suffix)
End Function

Example use

Private Sub TextBox1_Change()

  If IsCorrect(Me.TextBox1, "A-", "-A") Then
 
    'entry is correct
     Me.TextBox1.BackColor = rgbLightGreen
    
  Else
    'entry not correct
    Me.TextBox1.BackColor = rgbRed
 
  End If
End Sub

In this example, the textbox should turn red until the value entered, matches the pattern specified where it should then turn green. I have also included the automatic change of all text entered to Uppercase.

Hope Helpful

Dave

Oh nice. It's similar to inserting a macro into a userform. But using byref box as object you can get it to automatically reference which textbox you want affected by the function. Dang I wish I'd have known this weeks ago. I could use a function to check the textboxes that have to have the oddball format I mentioned earlier, the time format ones, and the ones that can only have numbers.

Also I'm thinking of a couple applications for turning a textbox a different color. That's useful too. Thank you.

Does the data that will be entered into the textbox already exist in the workbook?
If it does, you should consider using a combobox instead of a textbox. With a searchable combobox, you won't have to type the entire word. Just enter a few characters then you can select an item from the list.

No. Old place. I'm basically transcribing from paper into the spreadsheet because they're finally trying to find a way to trend data. Now my spreadsheet is here and my nightmares have begun.

Utilizing the textbox Exit event for input validation, here is my suggestion:

Private Sub TextBox1_Change()
With TextBox1
    .Text = UCase(.Text) 'convert the text to Uppercase
End With
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
    If .Text <> "" Then
        'pattern: "A-" & 2 digit & 3 letter & 4 digit & "-" & 1 letter > example: A-21BGF5634-W
        If Not .Text Like "A-##[A-Z][A-Z][A-Z]####-[A-Z]" Then
            Beep
            MsgBox "Wrong input! It must follow this pattern:" & vbLf & "A-##@@@####-@" & vbLf & _
                    "# means any number" & vbLf & "@ means any letter" & vbLf & _
                    "Example: A-21BGF5634-W"
            Cancel = True
        End If
    End If
End With
End Sub

Note:

• Any lowercase letters entered by the user will automatically be converted to uppercase.
• The message box provides more detailed instructions to help user understand what input is required.
• You can adjust the code for other textboxes to suit. How many textboxes need input validation?

There's less than 5 with the oddball format. There's maybe 20 time textboxes. Then over a hundred that should only have numbers in them. Between around 8 userforms.

I think between what you both have shared I can see how to add a few things to what I'm doing to make things more error proof. I greatly appreciate you both.

 

[dmt32]

I could use a function to check the textboxes that have to have the oddball format I mentioned earlier, the time format ones, and the ones that can only have numbers.

As I mentioned, Functions (as well as common procedures) avoid the need to keep repeating the Same code (or duplicating Userforms) throughout a project especially one like yours that sounds to be quite complex.

The Time Textboxes you mention & just a thought, your code may be able to be simplified a little like this maybe

Private Sub TextBox1_AfterUpdate()
    Me.TextBox1.Value = Format(Val(Me.TextBox1.Value), "00:00")
End Sub

This event occurs after data in a control is changed by the user
and I assume that you are only allowing Numeric values in the control otherwise "00:00" will be returned

I think between what you both have shared I can see how to add a few things to what I'm doing to make things more error proof. I greatly appreciate you both.

Glad we were able to assist & appreciate your feedback

Good luck with your project

Dave

 

[Akuini]

There's less than 5 with the oddball format. There's maybe 20 time textboxes. Then over a hundred that should only have numbers in them. Between around 8 userforms.

I think between what you both have shared I can see how to add a few things to what I'm doing to make things more error proof. I greatly appreciate you both.

Best wishes for your project, feel free to return whenever you require further assistance.

 

[renraw9002]

Is there a way to have a textbox linked to the comment box of a cell?

Idk how better to explain this. I have a textbox that always goes to column 58 for quantity of product removed at a specific step in the process. However there's several reasons why the product might be removed at that point. I'd like to add another textbox that if filled out will add a comment to that cell saying why it was removed.

 

[Akuini]

Is there a way to have a textbox linked to the comment box of a cell?

You should consider starting a new thread since this question is unrelated to your original one.