User input textbox...help!

[geff]

Hi all,

I may be being really dumb with this question.

I have created a workbook which is a financial claim form. My users fill in their financial data and provide textual comments around their progress. The document is password protected and the users are forced to enable macro's (using the hidden sheets method). The form automatically validates the financial data that is inputted using VBA.

The only problem I have is around the textual input boxes. I simply want a text box that the users can complete with information on their progress and for it to be printed when they print the form out. I have been using the autoshape text box which works to an extent. Unfortunately I cannot character limit using the autoshape text box. And if the user inputs more words than fit in the box it wont print all the text.

I have tried to use the text box tool within the control toolbox, but as soon as I protect the sheet it stops the input of text by the user. It just doesn't seem to work properly even when allow "edit objects" from the protect sheet options. Then it just allows the user to select the actual text box and not actually input text into it.

Any help would be massively appreciated.

Thanks in advance.

 

[JamesW]

Hi,

In the textbox properties set the MaxLength to 10 and try that.

 

[pbornemeier]

Use a fixed width font in the textbox. Set the text box width to hold 30 characters. When the user finishes entering data, parse the string and count each vbCr character (entered with Ctrl+Enter; has an Asc value of 13) as 30 characters. if the count of all non-vbCr characters + (30 * the number of VbCr characters) is greater than your maximum. Return an error message and put the focus back into the textbox.

Note the 30 in the above parragraph can be changed to any value you want to use.

 

[geff]

Use a fixed width font in the textbox. Set the text box width to hold 30 characters. When the user finishes entering data, parse the string and count each vbCr character (entered with Ctrl+Enter; has an Asc value of 13) as 30 characters. if the count of all non-vbCr characters + (30 * the number of VbCr characters) is greater than your maximum. Return an error message and put the focus back into the textbox.

Note the 30 in the above parragraph can be changed to any value you want to use.

Thank you for this....unfortunately I'm a little bit dumbfounded. Any chance you could show me how to actually achieve this. I can certainly use a fixed width font, like Courier and set the width to hold '*30' characters. But I'm not sure how to parse the string and do the rest. Sorry I'm a bit of a newb.

Many thanks

Geoff

 

[pbornemeier]

Even easier than I thought. Found a property I didn't know about. Put this code in the code page of the worksheet that contains the textbox.

Private Sub TextBox1_Change()
    If ActiveSheet.TextBox1.LineCount > 15 Then
        MsgBox "Too many lines in textbox.  Edit to have less than 15 lines."
    End If
End Sub

 

[geff]

Even easier than I thought. Found a property I didn't know about. Put this code in the code page of the worksheet that contains the textbox.

Private Sub TextBox1_Change()
    If ActiveSheet.TextBox1.LineCount > 15 Then
        MsgBox "Too many lines in textbox.  Edit to have less than 15 lines."
    End If
End Sub

Fantastic!! The above is working brilliantly. Do you think there would be any way to add to the code above in order to; 1) Delete any characters that exceed the limit, and 2) Stop users from pasting in text that exceeds the limit?

Thank you so much for your help.

 

[pbornemeier]

This code almost works as I would like. If the textbox is completely empty and a too-large chunk of data is pasted then the code fires twice (even with Enable Events set to False). A way around it would be be initialize the textbox with a single space.

Option Explicit
Dim sTextBox As String
 
Private Sub TextBox1_Change()
    Application.EnableEvents = False
    If ActiveSheet.TextBox1.LineCount > 15 Then
        MsgBox "Too many lines in textbox.  Your change has been reverted."
        ActiveSheet.TextBox1.Value = sTextBox
    End If
    sTextBox = ActiveSheet.TextBox1.Value
    Application.EnableEvents = True
 
End Sub

 

[geff]

This code almost works as I would like. If the textbox is completely empty and a too-large chunk of data is pasted then the code fires twice (even with Enable Events set to False). A way around it would be be initialize the textbox with a single space.

Option Explicit
Dim sTextBox As String
 
Private Sub TextBox1_Change()
    Application.EnableEvents = False
    If ActiveSheet.TextBox1.LineCount > 15 Then
        MsgBox "Too many lines in textbox.  Your change has been reverted."
        ActiveSheet.TextBox1.Value = sTextBox
    End If
    sTextBox = ActiveSheet.TextBox1.Value
    Application.EnableEvents = True
 
End Sub

Works perfectly!! You're a star! Thank you so much.