Macro to limit number of allowed characters in a dialog input field

[kappa0815]

Hello all,
I need to limit the number of allowed characters (actually values) in an input field within an input box

The input box syntax looks as follows

InputBox("If Code is needed for the file name, then enter code and click 'OK', else click 'Cancel'", "Optional Input", Environ("Code"))

Input Box1.JPG

User can optionally enter an 8 digit number in the format "12345678", but should not be able to enter >8 characters, but they can also simply skip that step (this is already working via the "Cancel" button)

Thanks in advance for your help :-)

Cheers,
Claus

[kasan]

I think that Inputbox is not so smart to restrict input. One option is check length of input after OK is pressed and if length is >8 then call InputBox again.
Another alternative could be small UserForm with TextBox for input. TextBox element have property MaxLength, and it can be set 8 in your case.
and what happens if I'll input 1234ABC8? Digits & Letters?

[kappa0815]

Thanks kasan,
I think you are right - simple inputbox seems actually not to be smart enough :-)

If, then the user should only input numbers, not letters. Assuming, this will not be covered by the pure check for number of characters, right?

Hence, I will give it a try with a smarter userform / textbox

Regards, Claus

[kasan]

Right, additional check on "Only numbers" is needed.
This could be done in Textbox event Exit. Like this:

Please Login or Register  to view this content.

[kappa0815]

Thanks Kasan!!!
very helpful indeed!
Still struggling how to get the textbox content into a certain cell

'Range ("G3") = TextBox1'
thought this would work the same way as the inputbox command, but this doesn't work :-(

You see, I am still learning :-)

[kasan]

I'm still learning too
Try this way:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[nigelog]

Please Login or Register  to view this content.

This code entered in textbox module with restrict entry to 8 numeric characters

[kappa0815]

very cool nigelog!!!

This comes already close :-)
Now I need to get the "OK" and the "Step Over" buttons to work - but this I will figure out (I hope :-)

Thanks!!!

Add: I tried it in different ways now, but obviously my vba skills are far away from being acceptable :-)
In the attached screenshot you see, what I am trying to do. The Textbox itself works pretty cool it appears, I can put in an 8 digit number; no more digits are allowed - BUT the OK button and the "Step Over" button do not work.
When user entered a value, upon clicking on OK system should write the value in cell G3 (or cell G3 should take the value from the inputbox) and then proceed with the rest of the macro; if user does not want to enter a value, the system should proceed with the macro w/o writing anything in cell G3

Maybe too dumb - but I do not get the buttons to work :-(

Appreciate every help :-)commandButtons actions1.JPG

[TheAnalyticalWolf]

Try this for your commandbuttons

Please Login or Register  to view this content.

also the code for the textbox could be greatly simplified by this
just add a small label below the textbox that shows the errormessage

Please Login or Register  to view this content.

[kappa0815]

Hello TheAnalyticalWolf,

thanks so much!!

Maybe a spupid question for an expert, but do I put this code into the userform code or into my main macro?

Cheers, Claus

[kappa0815]

Hello TheAnalyticalWolf,

thanks so much!!

Maybe a spupid question for an expert, but do I put this code into the userform code or into my main macro?

Cheers, Claus

[kappa0815]

Thanks a lot to all.

So, the final code looks like this and works perfectly

TextBox1
Sub CommandButton1_Click()
Me.Hide
End Sub
Sub CommandButton2_Click()
Me.Hide
End Sub

Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) = 0 Then
Exit Sub
End If
If Not IsNumeric(TextBox1.Text) Then
Label1.Caption = "Only Numbers allowed" 'prints a message in the label below textbox
Cancel = True
ElseIf Len(TextBox1.Text) > 8 Then
Cancel = True
Label1.Caption = "Maximum 8 Numbers" 'prints a message in the label below textbox
Cancel = True
End If
End Sub


within main macro

UserForm1.Show 1 'show 1 needed to run this userform and to stay'

ActiveSheet.Range("g2").Value = UserForm1.TextBox1
Unload UserForm1

[kappa0815]

Thanks a lot to all.

So, the final code looks like this and works perfectly

TextBox1
Sub CommandButton1_Click()
Me.Hide
End Sub
Sub CommandButton2_Click()
Me.Hide
End Sub

Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) = 0 Then
Exit Sub
End If
If Not IsNumeric(TextBox1.Text) Then
Label1.Caption = "Only Numbers allowed" 'prints a message in the label below textbox
Cancel = True
ElseIf Len(TextBox1.Text) > 8 Then
Cancel = True
Label1.Caption = "Maximum 8 Numbers" 'prints a message in the label below textbox
Cancel = True
End If
End Sub


within main macro

UserForm1.Show 1 'show 1 needed to run this userform and to stay'

ActiveSheet.Range("g2").Value = UserForm1.TextBox1
Unload UserForm1