none
Excel userform, Consolidate a large amount of "optionbuttonXX_click()" events into a simpler routine? RRS feed

  • Question

  • I have somewhere around 200 option buttons that do the exact same routine. Is there a way to eliminate the need to pound in the same private sub over and over...Create some sort of loop and consolidate the routine?

    Private Sub OptionButton19_Click() 'COMMENTS
    OptionBoxComX_Click OptionButton19
    OptionButton19.Value = False
    End Sub


    heads up

    Friday, December 19, 2014 4:20 PM

Answers

  • Your problem is not the fact there is a single routine getting called.....there's nothing wrong about that....now if you had CLONED THE CODE 200 times, THAT would be a problem.

    What you are requesting (I think) is a single EVENT HANDLER.
    To get that, you must establish a class and register each option button using a private variable declared WithEvents


    Friday, December 19, 2014 4:51 PM
  • Private WithEvents optAPP As MSFORMS.OptionButton

    Private mszName As String
    Private moRng as Range
    Private Sub Init(byref pOptionButton as OptionButton)
    Set optAPP = pOptionButton
    End Sub
    ' Do the below for the Range property as well
    Public Property Get prName() As String
    prName = mszName
    End Property
    Public Property Set prName(ByVal pszName As String)
    mszName = pszName
    End Property
    Private Sub optAPP_AfterUpdate()
    ' Put all of your data movement logic here
    End Sub

    Here you go.....just create a Class called clsOption and place the above code in it.
    Note: must add logic to store a range property which relates to the cell it affects, is related to.
    When the form loads, just put this code in a loop that captures a reference to each option button on the form.
    For Each oCtrl in Me.Controls

    If oCtrl.Type = optionbutton then ' I forget the enumerator value

      set oClsOption = new clsOption
      oClsOption.Init(oCtrl)
      oClsOption.Name = oCtrl.Name
      Set oClsOption.Range = Worksheets(3).Cells(2,3) ' get this from a worksheet that has these range addresses
    End If
    Next

    Now all of the logic is in ONE PLACE for all 200 option buttons.
    After each button is updated, you can move data from one location to the range specified for that option button.

    This will help keep you from spending the rest of your life maintaining your existing workbook.

    Friday, December 19, 2014 9:53 PM
  • Small mistake....see below.
    Public Property Let prName(ByVal pszName As String) 
     mszName = pszName 
    End Property 
    Private Sub optAPP_AfterUpdate() 
     ' Put all of your data movement logic here 
      Dim oRng as Range
      Set oRng = Range(optAPP.tag) ' assumes tag contains a valid address
    End Sub

    Monday, December 22, 2014 6:30 PM

All replies

  • Wouldn't it be easier to use a list box with 200 items instead of 200 option buttons? You'd only need a single On Click event procedure for the list box.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, December 19, 2014 4:31 PM
  • Your problem is not the fact there is a single routine getting called.....there's nothing wrong about that....now if you had CLONED THE CODE 200 times, THAT would be a problem.

    What you are requesting (I think) is a single EVENT HANDLER.
    To get that, you must establish a class and register each option button using a private variable declared WithEvents


    Friday, December 19, 2014 4:51 PM
  • I'm a nube, can you expand on the EVENT HANDLER?

    heads up

    Friday, December 19, 2014 6:59 PM
  • Actually the option button is associated with a Checkbox, or list box or textbox. By selecting the option, it opens a inputbox to add text that gets placed in the cell next to the checkbox,listbox or textbox entries.

    heads up

    Friday, December 19, 2014 7:01 PM
  • Private WithEvents optAPP As MSFORMS.OptionButton

    Private mszName As String
    Private moRng as Range
    Private Sub Init(byref pOptionButton as OptionButton)
    Set optAPP = pOptionButton
    End Sub
    ' Do the below for the Range property as well
    Public Property Get prName() As String
    prName = mszName
    End Property
    Public Property Set prName(ByVal pszName As String)
    mszName = pszName
    End Property
    Private Sub optAPP_AfterUpdate()
    ' Put all of your data movement logic here
    End Sub

    Here you go.....just create a Class called clsOption and place the above code in it.
    Note: must add logic to store a range property which relates to the cell it affects, is related to.
    When the form loads, just put this code in a loop that captures a reference to each option button on the form.
    For Each oCtrl in Me.Controls

    If oCtrl.Type = optionbutton then ' I forget the enumerator value

      set oClsOption = new clsOption
      oClsOption.Init(oCtrl)
      oClsOption.Name = oCtrl.Name
      Set oClsOption.Range = Worksheets(3).Cells(2,3) ' get this from a worksheet that has these range addresses
    End If
    Next

    Now all of the logic is in ONE PLACE for all 200 option buttons.
    After each button is updated, you can move data from one location to the range specified for that option button.

    This will help keep you from spending the rest of your life maintaining your existing workbook.

    Friday, December 19, 2014 9:53 PM
  • Here is one of the many repetitive private subs, how can I bend this into the WithEvents optAPP?

    Private Sub OptionButton49_Click() 'COMMENT

    OptionBoxComX_Click OptionButton49

    OptionButton49.Value = False 'Clear OpBut

    End Sub

    ....

    Public Sub OptionBoxComX_Click(ByVal CB As msforms.OptionButton)

      On Error GoTo ErrorHandler

      'Error Flag

        Errorflag = " OptionBoxComX"

    'MsgBox ("CB" & CB.Tag) 'Tag= wb location 'sheet1'!A1

       Dim MyQ As Variant 'move Msgbox result to string

       Dim CommentStr As String  'Inputbox Variable containing comment

       MyQ = MsgBox("Add?", vbYesNo, " Comment ?") 'decision box

        If MyQ = vbNo Then

          MsgBox "Comment Field Cleared"   'prompt

          Worksheets(WbkSheet).Range(CB.Tag) = "" 'Clears cell

          Exit Sub

        End If

        If MyQ = vbYes Then

           CommentStr = InputBox("Enter your Comment", "Comment Box")

             If CommentStr = vbNullString Then

                Exit Sub

             End If

           MsgBox "Comment Will Be Added : " & CommentStr

           Worksheets(WbkSheet).Range(CB.Tag) = CommentStr    'Writes comment to cell

        End If

        Exit Sub

    ErrorHandler:

    Call MyError

    End Sub


    heads up

    Sunday, December 21, 2014 2:18 AM
  • SysWizard,

    I tried this and ran into errors.(mszName = pszName ) I'm positive it is related to the note, but my skill set is not strong enough to hack thru the missing elements.  I have a routine during activate that sets the .Tag to store the sheet and range, but not sure how to implement this in you're suggested code.


    heads up

    Monday, December 22, 2014 3:48 PM
  • Small mistake....see below.
    Public Property Let prName(ByVal pszName As String) 
     mszName = pszName 
    End Property 
    Private Sub optAPP_AfterUpdate() 
     ' Put all of your data movement logic here 
      Dim oRng as Range
      Set oRng = Range(optAPP.tag) ' assumes tag contains a valid address
    End Sub

    Monday, December 22, 2014 6:30 PM