slow macto

[Fan924]

When this macro listed only 10 sheets, it worked fine & ended with a
single beep. Now that I made it work on 25 sheets, it hangs up for 3
seconds and ends with 7 beeps. I added Application.EnableEvents at the
front and end and it did not help. All sheets have these button names.
Where did I go wrong? excel 97

Sub SetSwitch1()
Dim ws3 As Worksheet
Set ws3 = Worksheets("Sheet1")
ws3.Range("A2").Value = "Switch1"
With Worksheets("Sheet2")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
With Worksheets("Sheet3")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
With Worksheets("Sheet4")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False

<snip>

With Worksheets("Sheet25")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
With Worksheets("Sheet26")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
Beep
End Sub

 

[Dave Peterson]

Application.enableevents doesn't apply to these optionbutton changes.

You have to do it yourself.

At the top of a General module (not in the ThisWorkbook and not behind a
worksheet), add this line:

Public BlkProc as boolean

Then in your code, toggle that variable:

Sub SetSwitch1()
Dim ws3 As Worksheet
Set ws3 = Worksheets("Sheet1")
ws3.Range("A2").Value = "Switch1" BlkProc = true '<---- Added
With Worksheets("Sheet2") .....
With Worksheets("Sheet26")
.OptionButton1.Value = True
.OptionButton2.Value = False
.OptionButton3.Value = False
End With
Beep blkProc = false '<-- added
End Sub

Then in each of those OptionButton_click or change or whatever events, you have
to add a line to check that variable:

Private Sub OptionButton1_Change() '
If BlkProc = true then exit sub
rest of code here
End sub

 

[Fan924]

Thank you Dave, worked great.