Validating Textbox's on Worksheet using Class Modules`

[xluser2007]

Hi All,

I am considering preparing a survey using a simple worksheet approach for internal use (rather than design a complicated Userform for this mini project).

As such, I would like some entries to be manually typed by the User in a Textbox (based on general consensus by people using the survey).

As such, I understand that for each textbox, I could validate the input entry (e.g. ensuring a value between 0-1 and formatted as a percentage) using a Keypress Event as described in this site.

However if there are going to be many Questions and if the Textboxes are named logically e.g. Question1_Part1, Question1_Part2, Question2_Part1 etc, its may be easier to use Class modules.

As such, I found this very helpful example by Ozgrid member Reafidy to control textboxes on a Userform.

I have attached Reafidy's example but added in a second worksheet with the labelled textboxes and relaevant validation required.

Could anyone please show how to control the textboxes on the worksheet using the Class module approach?

Any help appreciated.

[Bob Phillips]

I wholeheartedly buy into your worksheet approach, but why would you use a textbox on a worksheet? What is a cell if it is not an input box? You can us Data v alidation to ensure that correct data is input.

[xluser2007]

I wholeheartedly buy into your worksheet approach, but why would you use a textbox on a worksheet? What is a cell if it is not an input box? You can us Data v alidation to ensure that correct data is input.

Bob, fantastic point - exactly the notion which I was trying to convince the internal survey users.

In the past I've posted a similar thread regarding Worksheet driven userforms, and together have always gone in the similar vein to your suggestion Bob, which I agree with from a maintenance point of view.

However in the past some clients for other related projects have asked for textboxes on Worksheets (even though we have mentioned your type of cell-inpout approach to them!).

As such, I thought for this simple internal survey, why not learn how to do this - for interest sake and to observe how Class modules can be used to simplify such event code.

Is this possible to do for textboxes on worksheets Bob using the already prepared Class module?

Since thanks and regards,

[Bob Phillips]

You can, but I really cannot advocate this approach.

Class module, clsWSCtls

[vba]

Option Explicit

Public WithEvents mTextBoxGroup As MSForms.TextBox

Private Sub mTextBoxGroup_Click()
End Sub

Private Sub mTextBoxGroup_Change()

End Sub

Private Sub mTextBoxGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
MsgBox Chr(KeyAscii)
End Sub
[/vba]

worksheet code module

[vba]


Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim cTBEvents As clsWSCtls
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox Then
Set cTBEvents = New clsWSCtls
Set cTBEvents.mTextBoxGroup = shp.OLEFormat.Object.Object
mcolEvents.Add cTBEvents
End If
End If
Next

End Sub
[/vba]

[xluser2007]

Hi Bob, Many thanks for your kind help.

I understand what you mean of not using this approach fro m a maintenance point oif view. Interesting to see how succintly you have coded it though, I was thinking for a worksheet it may be difficult and a new keypress needed for every textbox!


We (especially me ) are not programmers and we only develop these types of surveys as part of data collection for other projects, so it's awesome to get adcide from experts like yourself.

BTW, I was wondering if the textbox name begins with "Question1" how can I modify the event code to only allow numbers between 0 and 1 and formatted asa percentage.

Also can the "_Click" event be used to explain to the user type of input required when they click on the textbox i.e. if they click oin a textbox named "Question1_..." then have a Msgbox stating "enter a value between 0 and 1 please", if so how would that be adapted into the code you have given?

Kind regards and thanks.

[Bob Phillips]

I am sorry, I have to try and persuade you to not take this overly complex path when Excel already does everything you wa nt.

If they want textboxes, make them think they have got textboxes. Fool all of the people all of the time.