Controlling Many TextBoxes on One Form 1

[RichF01]

I have a userform with 66 Text Boxes and 66 Scroll bars, which are paired up (the text box shows the value of the scroll bar, and if the text box is written in by the user, the scroll bar changes accordingly).

I have code that works for one text box/scroll bar pair and is easily replicated to all pairs (See below)

Private Sub sbEAResiDem_Change()
    tbEAResiDem = sbEAResiDem / 2
End Sub

Private Sub tbEAResiDem_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Call TxtBoxValidation(Cancel, tbEAResiDem, sbEAResiDem)
If Cancel = False Then
    sbEAResiDem = tbEAResiDem * 2
Else
    tbEAResiDem = CurVal
End If

End Sub

Private Sub tbEAResiDem_Enter()
    CurVal = sbEAResiDem / 2
End Sub

Function TxtBoxValidation(ByVal Cancel As MSForms.ReturnBoolean, TxtBx, ScrlBr)

If IsNumeric(TxtBx) = True Then
    If TxtBx >= -100 And TxtBx <= 100 Then
        ScrlBr = Round(2 * TxtBx, 0)
    Else
        Error_outofrange_per
        TxtBx = CurVal
        Cancel = True
    End If
Else
    Error_Numeric
    TxtBx = CurVal
    Cancel = True
End If

End Function

What I want is for a general system, such that if textbox A is updated, then the relevant scroll bar is updated, without doing it 66 times.

I'm thinking something to do with control types altho I've never used them before

All help appreciated
R

 

[Bogiemon]

One way to do it would be to create a sub that requires controls as inputs:

Sub myStuff(c1 as Control, c2 as Control)
Do Stuff
End Sub

And on each of your BeforeUpdate sub's call the MyStuff routine:

Private Sub tbEAResiDem_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
mystuff myUserForm.Controls("tbEAResiDem"), myUserForm.Controls("sbEAResiDem")
End Sub

You will obviously have to copy the above code 66 times and change the Control names.

There may be better ways of doing this but this is the only way I know.

Bogiemon

 

[combo]

You can create a separate class and move common code to it. For two pairs of control,, sample code:
class module namec cPair:

Public WithEvents cTextBox As MSForms.TextBox
Public WithEvents cScrollBar As MSForms.ScrollBar

Private Sub Class_Terminate()
Set cTextBox = Nothing
Set cScrollBar = Nothing
End Sub

Private Sub cScrollBar_Change()
cTextBox.Text = cScrollBar.Value
End Sub

Private Sub cTextBox_Change()
cScrollBar.Value = Val(cTextBox.Text)
End Sub

userform module:

Private x(1 To 2) As cPair

Private Sub UserForm_Initialize()
Set x(1) = New cPair
Set x(1).cTextBox = Me.TextBox1
Set x(1).cScrollBar = Me.ScrollBar1
Set x(2) = New cPair
Set x(2).cTextBox = Me.TextBox2
Set x(2).cScrollBar = Me.ScrollBar2
End Sub

combo

 

[Zathras]

combo: Neat! A star from me for sure. I wish Microsoft had better documentation on how to exploit class modules. I have never really understood how to make them useful.

 

[RichF01]

Combo, brilliant start for me, thanks a lot.

However, one little niggle is that I am validating the textbox value, (obviously a scroll bar has finite values it can take) and I want to do it on the BeforeUpdate event, but in the Class Module editing pane, I do not have the beforeupdate event available, but I do in UserForms. Am I missing a reference library?

Thanks in advance

 

[RichF01]

I am still interested why my last post is, but I have found a way round it using keypress event

Using a variation on Combo's code above . . .

Private Sub cTextBox_Change()

If cTextBox.Value <> "-" Then
    If Abs(cTextBox.Value) > 100 Then
        cTextBox = Left(cTextBox, Len(cTextBox) - 1)
    End If
    cScrollBar = 2 * cTextBox
End If

End Sub

Private Sub cTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Only allows 0-9, .,  -
If KeyAscii < 45 Or KeyAscii > 57 Or KeyAscii = 47 Then
      KeyAscii = 0
End If
   
End Sub

 

[combo]

Thanks to WithEvents keyword combined with custom class, the code can be moved from single class module to multiple custom object modules. The drawback is that not all events can be handled this way (in the above case derived from Control class). This is why there are no After/BeforeUpdate events and Enter/Exit. You need to try other available events.
Zathras,
VBA help files indeed suffer from lack on details, but MS has quite good tutorial in VB section of MSDN library .

combo