Solved: Repeating Sub

[wl_tinus]

Hi there,

I'm quite a newbie to VBA for Word and Excel.

I'm developing a wizard for Wordt that fills out a document with data from a userform. To make it foolproof I need to add te code below to my userform. However I need to enter it about 20ish times. And only the last nuber changes from 1 to 20, including the last number in the sub title. Is there any way to make these 20 subs more compact, or to make it one sub?

Kind Regards,

Martijn


Private Sub F1Chk1_Click()
If F1Chk1.Value = False Then
FEuri1.BackColor = &H80000004
FEuri1.Enabled = False
FEuri1.Tag = ""
Else
FEuri1.BackColor = &H80000005
FEuri1.Enabled = True
FEuri1.Tag = "Save"
End If
End Sub

[TonyJollans]

Hi Martijn,

Welkom bij VBAX!

Every checkbox must have its own On Click routine but you could make it call a generic procedure with parameters, something like this if I understand correctly what you wantPrivate Sub F1Chk1_Click()
Click_Routine
End Sub

Private Sub F1Chk2_Click()
Click_Routine
End Sub

Private Sub Click_Routine()
If ActiveControl.Value = False Then
With Me.Controls("FEuri" & Mid(ActiveControl.Name, 6))
.BackColor = &H80000004
.Enabled = False
.Tag = ""
End With
Else
With Me.Controls("FEuri" & Mid(ActiveControl.Name, 6))
.BackColor = &H80000005
.Enabled = True
.Tag = "Save"
End With
End If
End Sub

[Killian]

Hi Martijn and welcome :hi:

When dealing with a large number of controls (or any object) that you want to behave in the same way, it's useful to write a class (with an object variable of the control type declared "WithEvents") that describes the behavoiur in its click event.

Then when the form initializes, make each target control an instance of the class and add a reference to it to a collection. The collection will hold the "custom" controls for the lifetime of the form. When they're clicked, the class code fires.

Ive attached an example so you can see how it fits together

[wl_tinus]

Thanx!!!

I probably need a bit of time to figure out how it works, but that doesn't matter (especially the second solution).

[wl_tinus]

I just wanted to say:

YOU GUYS ARE AWESOME

it took me quite a bit of time how Killian's solution worked and how I could adjust to my specific needs (it only needs to work for certain checkboxes, not for all), but I've implemented it succesfully in my userform.

:thumb