CODEWITHSUNDEEP
excelvba
Vince
Vince

Reputation: 557

Performance changing OptionButton OleObject backgroud color on click VBA

I have a Sub to change the background color of OptionButton in a group when clicked but i can't get this work fluently without any delay when switch the colorm too slow. Is there better aproach?

There are 50 OptionButtons with a unique caption each

class module

Public WithEvents Opt As MSForms.OptionButton

Private Sub Opt_Click()
  Application.ScreenUpdating = False
    Call toggleColor(Opt)
  Application.ScreenUpdating = True
End Sub

Sub toggleColor(opt As Object)    
    For Each ctl In Worksheets("controls").OLEObjects        
         If TypeName(ctl.Object) = "OptionButton" Then
            If ctl.Object.Caption <> optCaption.Caption Then
                ctl.Object.BackColor = &H80000011
            Else
                opt.BackColor = &H80000016
            End If
         End If            
    Next ctl        
End Sub

Upvotes: 0

Views: 271

Answers (1)

Tim Williams
Tim Williams

Reputation: 166341

Seems like you only need to set the color for 2 options, not 50 - you just need to remember between calls which one is the odd one out, reset that one, then set the color for opt.

ToggleColor insn't instance-specific (since you're passing in opt), so that could be in a regular module and re-worked to something like:

Sub toggleColor(opt As Object)
    Static optPrev As Object 'previous selection (if any)
    
    If Not optPrev Is Nothing Then
        optPrev.BackColor = &H80000011 'un-flag previous
    End If
    
    opt.BackColor = &H80000016 'flag the clicked one
    Set optPrev = opt          'remember it for next time
End Sub

Upvotes: 1

Related Questions