Limiting number of character in InputBox to 31 - VBA Help

[DJMXM]

I need to limit the number of Characters that can be inputted to the popup InputBox to just 31. How do I set MaxLength? Code I am using is listed below.

Sub NewRecipeSheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("1. Recipe Master Sheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
Application.Goto Reference:=Sheets("1. Recipe Master Sheet").Range("A1")
Range("A1").Value = InputBox("Menu Item Name?")
    
End Sub

 

[Rick Rothstein]

There is no way to force the InputBox to limit the typing to a maximum amount of characters (31 in your case). The best you can do is create a loop and don't exit it until the user enters less than that maximum. Something like this...

Dim Answer As String
....
....
Do
  Answer = InputBox("Menu Item Name?")
  If Len(Answer) > 31 Then MsgBox "You typed in too many characters... 31 maximum!"
Loop While Len(Answer) > 31
Range("A1").Value = Answer
....
....

 

[DJMXM]

It works Ok... It seems I have to answer the question twice before it excepts even if it's less than 31 characters

Mike

 

[DJMXM]

Here is the Code after I put it into my Macro

Sub NewRecipeSheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("1. Recipe Master Sheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
Range("A1").Value = InputBox("Menu Item Name?")

Dim Answer As String
    Do
        Answer = InputBox("Menu Item Name?")
    If Len(Answer) > 31 Then MsgBox "You typed in too many characters... 31 maximum!"
    Loop While Len(Answer) > 31
Range("A1").Value = Answer

Application.Goto Reference:=Sheets("1. Recipe Master Sheet (2)").Range("A4")
End Sub

 

[Rick Rothstein]

Here is the Code after I put it into my Macro

Sub NewRecipeSheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("1. Recipe Master Sheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
[COLOR=#FF0000][B]Range("A1").Value = InputBox("Menu Item Name?")[/B][/COLOR]

Dim Answer As String
    Do
        Answer = InputBox("Menu Item Name?")
    If Len(Answer) > 31 Then MsgBox "You typed in too many characters... 31 maximum!"
    Loop While Len(Answer) > 31
Range("A1").Value = Answer

Application.Goto Reference:=Sheets("1. Recipe Master Sheet (2)").Range("A4")
End Sub

Remove the line of code I highlighted in red... it is the source of your needing to answer the question twice (if you look at the code I posted, you will see it was not included there).

 

[DJMXM]

Much Better - Thank You VERY MUCH.... I am teaching myself VBA as fast as I can but still have a VERY LONG WAY to go!!