+ Reply to Thread
Results 1 to 2 of 2

Button click calls a module then jumps to a specific line or label

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2019
    Location
    Lewiston, Maine
    MS-Off Ver
    Office 16
    Posts
    1

    Button click calls a module then jumps to a specific line or label

    I have a file that currently has one button that goes to a "MainMacro" module that will call other modules and also run slightly different code for 17 different locations that will eventually send emails to these locations. I'm trying to create buttons for each location for the purpose of running this report for just a specific location when needed. What I'm envisioning is that I will click the location button and it will call one module then call the "MainMacro" module then goto the Line or Label to run that specific location.

    Location Button would run this
    Sub Location46572()
    Application.ScreenUpdating = False
    
       Call InsertOrderNum
       Call Main_Macro 'line 'BU 46572
       
    End Sub
    Which would then go to Line 'BU 46572 in this next code to what ever label needs to go there. I'd like to stay away from Line Numbers in case there are modifications to the code at a later date.

    Sub Main_Macro()
    
    
    Application.ScreenUpdating = False
       Call Backup_Files
       Call InsertOrderNum
    
    'BU 46571
        Workbooks.Open("File Location").Activate
        Sheets("Sheet Name").Activate
        Call Sort_PO_LINE
        Call NewData_CopyPaste
        Call Vlookup
        Call Del_Rows
        Call MyFormatting
        Call CleanUp
    
        Set wb = Workbooks
        Set Filename = ActiveWorkbook
        Application.CutCopyMode = False
        If ActiveWorkbook.ReadOnly Then
         ActiveWorkbook.SaveAs ("File Location" & Format(Now(), " - MMM-DD-YYYY") & ".xlsx")
         ActiveWorkbook.Close
         Else
        Workbooks("File Name").Close savechanges:=True
        End If
    
    
    'BU 46572
           Workbooks.Open("File Location").Activate
        Sheets("Sheet Name").Activate
        Call Sort_PO_LINE
        Call NewData_CopyPaste
        Call Vlookup
        Call Del_Rows
        Call MyFormatting
        Call CleanUp
    
        Set wb = Workbooks
        Set Filename = ActiveWorkbook
        Application.CutCopyMode = False
        If ActiveWorkbook.ReadOnly Then
         ActiveWorkbook.SaveAs ("File Location" & Format(Now(), " - MMM-DD-YYYY") & ".xlsx")
         ActiveWorkbook.Close
         Else
        Workbooks("File Name").Close savechanges:=True
        End If
    This Main_Macro will goes through 15 other locations with some slightly different code. Basically I just need it to just run the code for the septic location from the original button click

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Button click calls a module then jumps to a specific line or label

    One idea is using labels, so you can "goto" at the top of your code, and then put a condition in that would skip down to the bottom if your location wasn't "All":

    Sub Main_Macro(myLocation As String)
    
    
    Application.ScreenUpdating = False
       
    Select Case myLocation
        Case "BU_46571"
            GoTo BU_46571
        Case "BU_46572"
            GoTo BU_46572
    End Select
    'if the myLocation string was not on the list above (like if it said "All") then all locations would run from here on
       Call Backup_Files
       Call InsertOrderNum
    
    BU_46571:
        Workbooks.Open("File Location").Activate
        Sheets("Sheet Name").Activate
        Call Sort_PO_LINE
        Call NewData_CopyPaste
        Call VLookup
        Call Del_Rows
        Call MyFormatting
        Call CleanUp
    
        Set wb = Workbooks
        Set Filename = ActiveWorkbook
        Application.CutCopyMode = False
        If ActiveWorkbook.ReadOnly Then
         ActiveWorkbook.SaveAs ("File Location" & Format(Now(), " - MMM-DD-YYYY") & ".xlsx")
         ActiveWorkbook.Close
         Else
        Workbooks("File Name").Close savechanges:=True
        End If
    If myLocation <> "All" Then goto the_end
    BU_46572:
           Workbooks.Open("File Location").Activate
        Sheets("Sheet Name").Activate
        Call Sort_PO_LINE
        Call NewData_CopyPaste
        Call VLookup
        Call Del_Rows
        Call MyFormatting
        Call CleanUp
    
        Set wb = Workbooks
        Set Filename = ActiveWorkbook
        Application.CutCopyMode = False
        If ActiveWorkbook.ReadOnly Then
         ActiveWorkbook.SaveAs ("File Location" & Format(Now(), " - MMM-DD-YYYY") & ".xlsx")
         ActiveWorkbook.Close
         Else
        Workbooks("File Name").Close savechanges:=True
        End If
    If myLocation <> "All" Then goto the_end
    
    'other locations with same idea
    
    the_end:
    Applicaiton.ScreenUpdating = True
        
    End Sub
    Then:

    Sub Location46572()
    Application.ScreenUpdating = False
    
       Call InsertOrderNum
       Call Main_Macro("BU_46572") 'when calling from main button that needs to run all locations, just make the string "All"
       
    End Sub
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. On Button Click Run A Module Then Wait Till Complete Then Run Another Module...
    By Ourkid123uk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2019, 02:16 PM
  2. [SOLVED] how to click a button from the ThisWorkbook module
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-02-2017, 08:02 AM
  3. VBA update label per second once I click a button
    By toyborjaxk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2016, 06:06 PM
  4. Help calling a module from a button click
    By jpalmos in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-24-2013, 03:16 PM
  5. How change label value for each click button ??
    By WILKER in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 11-02-2012, 12:25 PM
  6. How to click the button from module?
    By OKLover in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2005, 06:05 PM
  7. Calls from sheet module to ThisWorkbook module
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-23-2005, 11:05 AM

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