Results 1 to 9 of 9

Option to run all Case statements

Threaded View

  1. #1
    Registered User
    Join Date
    03-04-2013
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2010
    Posts
    14

    Option to run all Case statements

    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
    Last edited by wjwelch1; 03-17-2017 at 11:53 AM. Reason: Post edited to add runReport() code.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Case Select Statement the best option?
    By skellz022782 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-19-2016, 03:05 PM
  2. VBA Case statements
    By Gurureddevils in forum Excel General
    Replies: 1
    Last Post: 07-19-2015, 02:44 AM
  3. [SOLVED] Case Statements and If Statements
    By hawsoon13 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-05-2012, 12:44 PM
  4. Multiple case statements
    By abhijit.pendharkar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2009, 04:12 AM
  5. Case Statements
    By anazarian in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-08-2008, 06:11 AM
  6. Select Case Statements
    By ladeda063610 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2008, 11:25 AM
  7. [SOLVED] Option Groups and Select Case
    By Nubby in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2006, 10:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1