type mismatch error passing multiple items with byref

[CsJHUN]

Hello guys,
in advance im just learing the passing values between subs (no clue about classes too ), also a lazy a.. for declare items.
I have a userform with (for example) a simple 2x2 + headline (3x3) crosstable. I have option buttons in the data field. I want to color the x(A or B) and y(1 or 2) headline of the clicked radio button.

tbl	Headline A	Headline B
Headline 1	o A1	o B1
Headline 2	o A2	o B2

<tbody>
</tbody>

So if clicked on A1 then headline A and headline 1 would be red.
Of course the original "table" is bigger than the example (currently 9x7)
The headlines are labels, the "data" is option button (radio button)


I have this:

Private Sub opbtnclick(ByRef opbtn As OptionButton, ByRef lblx As Label, ByRef lbly As Label)
    lblx.BackColor = vbRed
    lbly.BackColor = vbRed
End Sub

Private Sub opbtn_1_1_Click()
opbtnclick Me.opbtn_1_1, Me.lbl_1_0, Me.lbl_0_1
End Sub

Private Sub opbtn_1_2_Click()
opbtnclick Me.opbtn_1_2, Me.lbl_0_2, Me.lbl_1_0
End Sub

And this gives me type mismatch error.

Help me understand this, thanks
John

 

[theBardd]

They are forms controls, so fully qualify

Private Sub opbtnclick(ByRef opbtn As msforms.OptionButton, ByRef lblx As msforms.Label, ByRef lbly As msforms.Label)
    lblx.BackColor = vbRed
    lbly.BackColor = vbRed
End Sub

 

[CsJHUN]

Thanks Bardd, works.

i have this in another file, why is that works? (this was a code snipet from here, and edited to my need, but works)

Private Sub tbtnbigclick(ByRef tbtn As ToggleButton, btnvalue As Boolean, betu As String)
If btnvalue = True Then
tbtn.BackColor = RGB(255, 0, 0)
Else
tbtn.BackColor = RGB(0, 255, 0)
End If
Dim ctrl As Control
For Each ctrl In Me.Controls
If Mid(UCase(ctrl.Name), 6, 1) = betu Then
ctrl.Value = btnvalue
End If
Next ctrl
End Sub

Private Sub tbtn__A_Click():
tbtnbigclick tbtn__A, tbtn__A.Value, "A"
End Sub

Private Sub tbtn__B_Click(): tbtnbigclick tbtn__B, tbtn__B.Value, "B": End Sub

As you can see not fully qualifed togglebutton is used here too.

 

[theBardd]

Because ToggleButton only exists in the MSForms library, so there is no need to qualify it. If you go into the Object Browser in the VBIDE and search on OptionButton, you will see an entry for Excel as well as MSForms (and an MSHTML), so you have to let your code know which library you want to access. But search on togglebutton, and you only see one for MSForms, so no need to qualify it (but some would argue you should qualify it anyway).

 

[CsJHUN]

Thanks for everything.

If you have time, the code look like this "..." means some more row skipped before inserting here:

Private Sub opbtnclick(ByRef opbtn As MSForms.OptionButton, ByRef lblhol As MSForms.Label, ByRef lblmi As MSForms.Label)
Dim ctrl As Control
For Each ctrl In Me.Controls

    ctrl.BackColor = vbMenuBar
Next ctrl
    lblmi.BackColor = vbRed
    lblhol.BackColor = vbRed
End Sub
Private Sub opbtn_1_1_Click(): opbtnclick opbtn_1_1, lbl_1_0, lbl_0_1: End Sub
Private Sub opbtn_1_2_Click(): opbtnclick opbtn_1_1, lbl_1_0, lbl_0_2: End Sub
Private Sub opbtn_1_3_Click(): opbtnclick opbtn_1_1, lbl_1_0, lbl_0_3: End Sub
Private Sub opbtn_1_4_Click(): opbtnclick opbtn_1_1, lbl_1_0, lbl_0_4: End Sub
...
Private Sub opbtn_1_8_Click(): opbtnclick opbtn_1_1, lbl_1_0, lbl_0_8: End Sub
Private Sub opbtn_1_9_Click(): opbtnclick opbtn_1_1, lbl_1_0, lbl_0_9: End Sub
Private Sub opbtn_2_1_Click(): opbtnclick opbtn_1_1, lbl_2_0, lbl_0_1: End Sub
Private Sub opbtn_2_2_Click(): opbtnclick opbtn_1_1, lbl_2_0, lbl_0_2: End Sub
Private Sub opbtn_2_3_Click(): opbtnclick opbtn_1_1, lbl_2_0, lbl_0_3: End Sub
...
Private Sub opbtn_7_7_Click(): opbtnclick opbtn_7_1, lbl_7_0, lbl_0_7: End Sub
Private Sub opbtn_7_8_Click(): opbtnclick opbtn_7_1, lbl_7_0, lbl_0_8: End Sub
Private Sub opbtn_7_9_Click(): opbtnclick opbtn_7_1, lbl_7_0, lbl_0_9: End Sub

By any chance is there a way to make it look more sophisticated?

 

[theBardd]

You could set up a controls events class for the form, load a collection with your option button controls, then add the collection to the events class and code a generic method in that class to set the colours based upon your nicely standardised control naming convention.

That way, you can remove all of the option button click subs, and any new option buttons will automatically work (as long as you keep to the naming convention).

You up for that?

 

[theBardd]

BTW, when a button is clicked, should all previous labels be cleared of the red colour?

 

[pbornemeier]

I am not why the above fails, but if you dimension all as objects then it works

 

[theBardd]

I am not why the above fails, ...

Read the responses, I told him why.

 

[pbornemeier]

This is a bit different, and I believe a bit easier to maintain, I am not sure it could be considered more sophisticated"

Option Explicit

Private Sub opbtnclick(ByRef opbtn As msforms.OptionButton)
    
    Dim sLblXName As String, sLblYName As String
    Dim sCaller As String
    
    sCaller = opbtn.Name
    
    sLblXName = "lbl_" & Mid(sCaller, 7, 1) & "_0"
    sLblYName = "lbl_0_" & Right(sCaller, 1)
    
    Me.Controls(sLblXName).BackColor = vbRed
    Me.Controls(sLblYName).BackColor = vbRed

End Sub

Private Sub opbtn_1_1_Click(): opbtnclick Me.opbtn_1_1: End Sub
Private Sub opbtn_1_2_Click(): opbtnclick Me.opbtn_1_2: End Sub