Short background:
I have 11 shape buttons, each with a unique name, and designed to run a different report. 1 of the 11 buttons is a "Run all Reports". I have 1 case statement that will recognize the name of the button selected and run that Case.
My question:
Is there anyway for when the "Run all Reports" button is selected I can have it run through the other 10 Case statements?
Below is the part of the code I have so far.
Thanks.
Sub test()
CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name
Select Case CallingShapeName
Case "Ark_E_Texas"
Set checkJ = Range("J1")
rgValue = "ARK_E_TEXAS"
Application.Run "runReport"
Case "Border_East"
Set checkJ = Range("J2")
rgValue = "BORDER_EAST"
Application.Run "runReport"
Case "Border_West"
Set checkJ = Range("J3")
rgValue = "BORDER_WEST"
Application.Run "runReport"
Case "Central_Texas"
Set checkJ = Range("J4")
rgValue = "CENTRAL_TEXAS"
Application.Run "runReport"
Case "Dallas"
Set checkJ = Range("J5")
rgValue = "DALLAS"
Application.Run "runReport"
Case "Fort_Worth"
Set checkJ = Range("J6")
rgValue = "FORT_WORTH"
Application.Run "runReport"
Case "Gulf_Coast"
Set checkJ = Range("J7")
rgValue = "GULF_COAST"
Application.Run "runReport"
Case "Louisiana"
Set checkJ = Range("J8")
rgValue = "LOUISIANA"
Application.Run "runReport"
Case "New_Mexico"
Set checkJ = Range("J9")
rgValue = "NEW_MEXICO"
Application.Run "runReport"
Case "Oklahoma"
Set checkJ = Range("J10")
rgValue = "OKLAHOMA"
Application.Run "runReport"
Case "Run_All"
'set loop to run through all reports
MsgBox ("Need to work on running all reports!!")
End Select
End Sub
Private Sub runReport()
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Shapes(CallingShapeName).Delete
Set sh = Sheets("Index")
sh.Range("H1").Value = rgValue
Application.Run "newWorkbook"
Application.ScreenUpdating = True
checkJ.Value = 1
Application.Run "check"
End Sub
Bookmarks