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.

 

[pbornemeier]

In Excel 2007 you can set the MaxLength property for an ActiveX Text Box, not sure about Excel 2003. Could the ActiveX Text Box be used instead?

 

[geff]

Thanks for your help and sorry it has taken so long to reply I've been on holiday and only just picked this up again now.

I assume the ActiveX text box is found within the "Control Toolbox"?

Unfortunately when I use the above there doesn't seem to be a way to lock the text box shape and still allow users to type within it.

It seems to be a bit of a dilema here. The standard drawing text box works exactly how I want it to. I can lock the shape but allow for text input. The only difficulty I'm having is that if a user types in more than the box size allows when the worksheet is printed it doesn't display all the text. If only there was a way to limit the characters within a drawing text box or somehow use the ActiveX text box to work like a user input text box.

As mentioned above the workbook code validates certain calculations by using macro buttons on each worksheet. Is there anyway to count the number of characters within a drawing text box? That way I could write the validation code to throw up an error message box should the text be over a certain number of characters within the textbox?

Any help would be greatly appreciated.

Thanks

 

[JamesW]

Hi,

Not sure about 'drawing boxes', buy with an activex control:

Private Sub TextBox1_LostFocus()
    If Len(TextBox1.Value) > 10 Then
        MsgBox "Length too long!"
        TextBox1.Activate
    End If
End Sub

If you are having problems with it moving/resizing then right click on it in design mode, and goto Format Control -> Properties -> Don't move and resize.

 

[geff]

Thanks for your response but that doesn't work, I still can't actually type into the box as soon as the worksheet is protected. I click the box but nothing happens no blinking cursor allowing me to type appears.

 

[JamesW]

Hi,

What is your macro security level set to? If macros are disabled then activex controls wont work.

 

[geff]

Hi,

What is your macro security level set to? If macros are disabled then activex controls wont work.

Seems that would be the answer....lol.

With my workbook having code in "ThisWorkbook" that hides all worksheets and removes all excel menus etc. I have gotten used to making any edits to it with Macros disabled. I simply didn't think about testing it with the macros enabled....der.

Anyway thanks for your help, that has sorted it.

 

[geff]

Actually the plot thickens. I have enabled multiline and enter behaviour to true as I need the users to present their text correctly.

This leaves a slight difficulty as the new paragraphs are not seen as characters unlike spaces. So the user can still, unfortunately, type more than the size of the box. Is there any way of limiting their text entry to the size of the text box?

 

[geff]

Actually the plot thickens. I have enabled multiline and enter behaviour to true as I need the users to present their text correctly.

This leaves a slight difficulty as the new paragraphs are not seen as characters unlike spaces. So the user can still, unfortunately, type more than the size of the box. Is there any way of limiting their text entry to the size of the text box?

Can anyone help me with the above?

 

[geff]

Anyone.....?