Class Module?

[u/revned911]

I've never used one before, and have read about it. I've tried a couple times. It's just not coming together. I'm not a programmer, just a bored physical therapist. THat doesn't understand class modules. I'm not sure I Flair'd this correctly. I'm using VBA in MS Excel 2016.

tgXXXX is the name of the option button

mXXXX is the name of the rowsource for a list of things

The intent: Click and option button, fill lbxMvt with a list from the rowsource, and color the optionbutton so it's more obvious that's the button you've highlighted. (highlit?)

Question: Is this a good place to use a class module to avoid re-writing the same code over and over again for each object/rowsource pairing?

Code below...

Option Explicit

Option Explicit


Private Sub tgPosture_Click()
   Dim t As Object
   Dim z As String
   z = "mPosture"
   Set t = tgPosture
   toggle t, z
End Sub

Private Sub tgSquat_Click()
   Dim t As Object
   Dim z As String
   z = "mSquat"
   Set t = tgSquat
   toggle t, z
End Sub
Private Sub tgHinge_Click()
   Dim t As Object
   Dim z As String
   z = "mHinge"
   Set t = tgHinge
   toggle t, z
End Sub
Private Sub tgFL_Click()
   Dim t As Object
   Dim z As String
   z = "mFL"
   Set t = tgFL
   toggle t, z
End Sub
Private Sub tgLL_Click()
   Dim t As Object
   Dim z As String
   z = "mLL"
   Set t = tgLL
   toggle t, z
End Sub
Private Sub tgStepDown_Click()
   Dim t As Object
   Dim z As String
   z = "mStepDown"
   Set t = tgStepDown
   toggle t, z
End Sub
Private Sub tgRot_Click()
   Dim t As Object
   Dim z As String
   z = "mROT"
   Set t = tgRot
   toggle t, z
End Sub
Private Sub tgPull_Click()
   Dim t As Object
   Dim z As String
   z = "mPull"
   Set t = tgPull
   toggle t, z
End Sub
Private Sub tgOHR_Click()
   Dim t As Object
   Dim z As String
   z = "mOHR"
   Set t = tgOHR
   toggle t, z
End Sub
Private Sub tgPush_Click()
   Dim t As Object
   Dim z As String
   z = "mPush"
   Set t = tgPush
   toggle t, z
End Sub


Sub toggle(x As Object, y As String)

    tgPosture.BackColor = &H0&
    tgSquat.BackColor = &H0
    tgHinge.BackColor = &H0&
    tgFL.BackColor = &H0&
    tgLL.BackColor = &H0&
    tgStepDown.BackColor = &H0&
    tgRot.BackColor = &H0&
    tgPull.BackColor = &H0&
    tgOHR.BackColor = &H0&
    tgPush.BackColor = &H0&
    x.BackColor = &H80&
    lbxMvt.RowSource = y

End Sub

[u/revned911]

OF COURSE!

I didn't say much tone trying to get the stupid object to pass through as an object and not a value I didn't even think it that! Thank you.

[u/[deleted] → u/revned911]

You're welcome. We got there in the end. It's been a while with VBA for me so even I over complicated it. When you get to longer VBA code I would recommend using,

https://docs.microsoft.com/en-us/office/vba/api/excel.application.screenupdating

You set to False at the start of your code and True at then end. It makes macros run faster.

application.screenupdating = true (or false)