Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
slow macto
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
slow macto
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
slow macto
Thank you Dave, worked great.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Tab | Excel Discussion (Misc queries) | |||
VERY SLOW | Excel Discussion (Misc queries) | |||
Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
Slow VBA, Can someone please look at it? | Excel Programming | |||
Slow Down... | Excel Programming |