Let me preface this by saying that I'm in the unenviable position of building something that was designed by a committee.
I have a dashboard that has a series of controls along the side. The dashboard is composed of three worksheets, and these controls are mirrored on each.
One of these controls let you change the level at which you view the dashboard, and because it's mirrored on the other sheets, the selected value needs to be reflected in the mirrored controls.
My code:
Each of the two lines after the Call is meant to keep the values in the other two controls in sync. However, programmatically changing these values triggers the On Click Event, which changes the values again, and triggers the On Click Event again, so on and so forth.
It will, eventually, exit this loop, but runtime is at least three times as long as it should be. I've tried Application.EnableEvents = False, but it doesn't have any affect on the events for controls. I've also tried disabling the controls while the values are changed, but even that triggers the On Click Event.
Does anyone have an idea of prevent the value changes from triggering the event? Failing that, does anyone have a suggestion for a fix?
I have a dashboard that has a series of controls along the side. The dashboard is composed of three worksheets, and these controls are mirrored on each.
One of these controls let you change the level at which you view the dashboard, and because it's mirrored on the other sheets, the selected value needs to be reflected in the mirrored controls.
My code:
Code:
'Sheet 1
Private Sub cbCurrentViewCat_Click()
Call UserChange(Cat.cbCurrentViewCat.Value)
Dept.cbCurrentViewDept.Value = Cat.cbCurrentViewCat.Value
Emp.cbCurrentViewEmp.Value = Cat.cbCurrentViewCat.Value
End Sub
'------------------------------------------------------------------------------------
'Mirror for Sheet 2
Private Sub cbCurrentViewDept_Click()
Call UserChange(Dept.cbCurrentViewDept.Value)
Cat.cbCurrentViewCat.Value = Dept.cbCurrentViewDept.Value
Emp.cbCurrentViewEmp.Value = Dept.cbCurrentViewDept.Value
End Sub
'------------------------------------------------------------------------------------
'Mirror for Sheet 3
Private Sub cbCurrentViewEmp_Click()
Call UserChange(Emp.cbCurrentViewEmp.Value)
Cat.cbCurrentViewCat.Value = Emp.cbCurrentViewEmp.Value
Dept.cbCurrentViewDept.Value = Emp.cbCurrentViewEmp.Value
End Sub
Each of the two lines after the Call is meant to keep the values in the other two controls in sync. However, programmatically changing these values triggers the On Click Event, which changes the values again, and triggers the On Click Event again, so on and so forth.
It will, eventually, exit this loop, but runtime is at least three times as long as it should be. I've tried Application.EnableEvents = False, but it doesn't have any affect on the events for controls. I've also tried disabling the controls while the values are changed, but even that triggers the On Click Event.
Does anyone have an idea of prevent the value changes from triggering the event? Failing that, does anyone have a suggestion for a fix?