Solved: Option button seqence

[BENSON]

I have four option buttons within a group box.they are labeled "Week 1,Week 2"etc. Is there some way to force the user to press the options buttons in the correct sequence 1e: first "week 1" than "week 2 "etc after "week 4" has been pressed week1" would be the next to be pressed

Thanks

[Bob Phillips]

Why not have a commandbutton that you cycle through all of the options, first time pressed you assume Week1, 2nd time then use Week2, and so on.

[BENSON]

Do you mean I could get all four macros currantly linked to the option buttons to run in sequence each time I press the one command button ? That would be great,could you provide a bit of a sample code to start me of.

Thanks

[Bob Phillips]

Okay. In this example, the button is a Forms toolbar button, and it is called 'Button 1'. Make sure that you change teh default caption to 'Week 1'.

The procs I use should be obvious.



Public Sub Button1_Click()

Select Case ActiveSheet.Buttons("Button 1").Caption

Case "Week 1"
Call procWeek1
ActiveSheet.Buttons("Button 1").Caption = "Week 2"

Case "Week 2"
Call procWeek2
ActiveSheet.Buttons("Button 1").Caption = "Week 3"

Case "Week 3"
Call procWeek3
ActiveSheet.Buttons("Button 1").Caption = "Week 4"

Case "Week 4"
Call procWeek4
ActiveSheet.Buttons("Button 1").Caption = "Week 1"
End Select
End Sub

[BENSON]

Have built command button and pasted code as required ,not sure about caption default name do I change the the module name ie module 1 to week 1

[Bob Phillips]

No, select the button, then change the caption text (Button1 will probably be the default).

[BENSON]

Have entered corret caption"week 1" and inserted my code.Could use please check it for me as the marco will not run.

Many thanks for your help and patience


Sub Button955_Click()
Select Case ActiveSheet.Buttons("Button 955").Caption

Case "Week 1"

Range("H4").Select
Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton5_Click"
Sheets("SEA POINT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\SEA POINT WEEKLY STOCKSHEET.xls"
Application.Run "'SEA POINT WEEKLY STOCKSHEET.xls'!Button123_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton1_Click"
Sheets("WATERFRONT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton1_Click"
Sheets("REGENT RD").Select
Range("H4").Select
Workbooks.Open Filename:="L:\REGENT RD WEEKLY STOCKSHEET.xls"

Application.Run "'REGENT RD WEEKLY STOCKSHEET.xls'!Button123_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button13_Click"
Sheets("GArRDENS").Select
ActiveWorkbook.Save

ActiveSheet.Buttons("Button 955").Caption = "Week 2"

Case "Week 2"

Range("H4").Select
Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton6_Click"
Sheets("SEA POINT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\SEA POINT WEEKLY STOCKSHEET.xls"
Application.Run "'SEA POINT WEEKLY STOCKSHEET.xls'!Button123_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!SEAPOINT_OptionButton2_Click"
Sheets("WATERFRONT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton2_Click"
Sheets("REGENT RD").Select
Range("H4").Select
Workbooks.Open Filename:="L:\REGENT RD WEEKLY STOCKSHEET.xls"

Application.Run "'REGENT RD WEEKLY STOCKSHEET.xls'!Button123_Click"
ActiveWindow.SmallScroll ToRight:=1
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button14_Click"
Sheets("GArRDENS").Select
ActiveWorkbook.Save

ActiveSheet.Buttons("Button 955").Caption = "Week 3"

Case "Week 3"

Range("H4").Select
Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton2_Click"
Sheets("SEA POINT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\SEA POINT WEEKLY STOCKSHEET.xls"
Application.Run "'SEA POINT WEEKLY STOCKSHEET.xls'!Button123_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton3_Click"
Sheets("WATERFRONT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton3_Click"
Sheets("REGENT RD").Select
Range("H4").Select
Workbooks.Open Filename:="L:\REGENT RD WEEKLY STOCKSHEET.xls", UpdateLinks _
:=3
Application.Run "'REGENT RD WEEKLY STOCKSHEET.xls'!Button123_Click"
ActiveWindow.SmallScroll ToRight:=3
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button15_Click"
Sheets("GArRDENS").Select
ActiveWorkbook.Save

ActiveSheet.Buttons("Button 955").Caption = "Week 4"

Case "Week 4"


Range("H4").Select
Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!OptionButton4_Click"
Sheets("SEA POINT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\SEA POINT WEEKLY STOCKSHEET.xls"
Application.Run "'SEA POINT WEEKLY STOCKSHEET.xls'!Button123_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!SEAPOINT_OptionButton4_Click"
Sheets("WATERFRONT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
Application.Run "'WATERFRONT WEEKLY STOCK.xls'!Button7_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_OptionButton4_Click"
Sheets("REGENT RD").Select
Range("H4").Select
Workbooks.Open Filename:="L:\REGENT RD WEEKLY STOCKSHEET.xls"

Application.Run "'REGENT RD WEEKLY STOCKSHEET.xls'!Button123_Click"
ActiveWindow.SmallScroll ToRight:=4
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!Button16_Click"
Sheets("GArRDENS").Select
ActiveWorkbook.Save


ActiveSheet.Buttons("Button 955").Caption = "Week 1"
End Select
End Sub

[Bob Phillips]

Can you just post the workbook?

[Bob Phillips]

I stripped your code down to the fundamentals



Sub Button955_Click()
Select Case ActiveSheet.Buttons("Button 955").Caption

Case "Week 1"

Range("H4").Select
ActiveSheet.Buttons("Button 955").Caption = "Week 2"

Case "Week 2"

Range("H4").Select
ActiveSheet.Buttons("Button 955").Caption = "Week 4"

Case "Week 4"

Range("H4").Select
ActiveSheet.Buttons("Button 955").Caption = "Week 1"
End Select
End Sub


and it worked fine, so maybe it is the other code, which I cannot test, that fails.

[Bob Phillips]

It would also be better to segment the code for maintainability



Sub Button955_Click()
Select Case ActiveSheet.Buttons("Button 955").Caption

Case "Week 1"
Call DoStuff("Button7", "OptionButton5", "Button123", _
"OptionButton1", "Button13")
ActiveSheet.Buttons("Button 955").Caption = "Week 2"

Case "Week 2"
Call DoStuff("Button7", "OptionButton6", "Button123", _
"OptionButton2", "Button14")
ActiveSheet.Buttons("Button 955").Caption = "Week 3"

Case "Week 3"
Call DoStuff("Button7", "OptionButton2", "Button123", _
"OptionButton3", "Button15")
ActiveSheet.Buttons("Button 955").Caption = "Week 4"

Case "Week 4"
Call DoStuff("Button7", "OptionButton4", "Button123", _
"OptionButton4", "Button16")
ActiveSheet.Buttons("Button 955").Caption = "Week 1"
End Select
End Sub

Private Sub DoStuff(btn1, btn2, btn3, btn4, btn5)

Range("H4").Select
Workbooks.Open Filename:="L:\GARDENS WEEKLY STOCK SHEET.xls"
Application.Run "'GARDENS WEEKLY STOCK SHEET.xls'!" & btn1 & "_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!" & btn2 & "_Click"
Sheets("SEA POINT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\SEA POINT WEEKLY STOCKSHEET.xls"
Application.Run "'SEA POINT WEEKLY STOCKSHEET.xls'!" & btn3 & "_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!" & btn4 & "_Click"
Sheets("WATERFRONT").Select
Range("H4").Select
Workbooks.Open Filename:="L:\WATERFRONT WEEKLY STOCK.xls"
Application.Run "'WATERFRONT WEEKLY STOCK.xls'!" & btn1 & "_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!WATERFRONT_" & btn4 & "_Click"
Sheets("REGENT RD").Select
Range("H4").Select
Workbooks.Open Filename:="L:\REGENT RD WEEKLY STOCKSHEET.xls"

Application.Run "'REGENT RD WEEKLY STOCKSHEET.xls'!" & btn3 & "_Click"
Application.Run _
"'Copy of Copy of Weekly Stocksheet 2 ( Version 3) .xls'!" & btn5 & "_Click"
Sheets("GArRDENS").Select
ActiveWorkbook.Save

End Sub

[BENSON]

thanks for the ammended code ,when it runs it shows compile error message "Sub Or Function Not Defined".Ihave highlighted the part of the code that it refers to


Sub Button955_Click()
Select Case ActiveSheet.Buttons("Button 955").Caption

Case "Week 1"
Call DoStuff("Button7", "OptionButton5", "Button123", _
"OptionButton1", "Button13")
ActiveSheet.Buttons("Button 955").Caption = "Week 2"

Case "Week 2"
Call DoStuff("Button7", "OptionButton6", "Button123", _
"OptionButton2", "Button14")
ActiveSheet.Buttons("Button 955").Caption = "Week 3"

Case "Week 3"
Call DoStuff("Button7", "OptionButton2", "Button123", _
"OptionButton3", "Button15")
ActiveSheet.Buttons("Button 955").Caption = "Week 4"

Case "Week 4"
Call DoStuff("Button7", "OptionButton4", "Button123", _
"OptionButton4", "Button16")
ActiveSheet.Buttons("Button 955").Caption = "Week 1"
End Select
End Sub

[Bob Phillips]

You have to add the function DoStuff as I showed you.