+ Reply to Thread
Results 1 to 8 of 8

Triggering Macros if condition is met

  1. #1
    Registered User
    Join Date
    10-20-2021
    Location
    Canada
    MS-Off Ver
    Microsoft Excel for Office 365 MSO - desktop version
    Posts
    16

    Triggering Macros if condition is met

    I have an excel form that takes data and then uses a submit button to export the data to a new sheet (New_Part_Request).

    I have a macro assigned to the submit button which calls three separate macros:
    1. is an error message to notify the user that the form is not fully complete (based on a number of cells).
    2. is the macro that exports to the 'new part request' sheet (data_input)
    3. Is a message box that notifies the user that the form has been submitted (Part_Submission).

    I am having an issue getting these to run independently of each other.
    For example, when the 1st macro appears showing the user the error about the form not being complete, I would like the other two not to run as a result. What is happening now, is that the error message comes up, followed by the form being submitted and then the msgBox saying that it has been submitted.

    How do I get it to run so that, if the error message appears, it stops. Else if, it gets submitted with no issues, then the 2nd and 3rd macro run.

    I know its pretty basic, I just cant get the if, else if to work.

    Current code for calling the macros is below:

    Sub buttonClick()

    Call errormsg1
    Call data_input
    Call Part_Submission1

    End Sub


    Thank you in advance.

  2. #2
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Triggering Macros if condition is met

    GW1994,

    Instead of having all 3 macros called in 1 event, you need to put them in the previously called macro. In other words, call only errormsg1. Put "Call data_input" at the end of the errormsg1 macro but have it run only if the right conditions are met. Then put "Call Part_Submission1" at the end of the data_input macro so it will run, but only if the right conditions are met. That way the first macro will run. If the right conditions are met, then it will continue with the second macro, and so on...

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Triggering Macros if condition is met

    Try:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Triggering Macros if condition is met

    You could get more complicated than that and pass a boolean variable on to the next macro so that macro would know whether to run or not, but that's not that hard either if you want it explained let me know. Then you could still call all 3 macros in 1 event. Either way accomplishes the same thing.

  5. #5
    Registered User
    Join Date
    10-20-2021
    Location
    Canada
    MS-Off Ver
    Microsoft Excel for Office 365 MSO - desktop version
    Posts
    16

    Re: Triggering Macros if condition is met

    Thank you.
    I tried that with the below code but still not working, I more than likely have the wrong placements of the IFS and ELSE IFS.
    Can you see what is wrong with the below by any chance?


    Sub buttonClickNP()

    If IsEmpty(Range("E10")) Then
    MsgBox "Enter which project the part request is for"
    GoTo ende
    Else

    If IsEmpty(Range("I8")) Then
    MsgBox "Enter the Part Type"
    GoTo ende
    Else

    If IsEmpty(Range("I11")) Then
    MsgBox "Enter the Product Type"
    GoTo ende
    Else

    If IsEmpty(Range("I14")) Then
    MsgBox "Enter a value for cell I14"
    GoTo ende

    Else

    ws_output1 = "New_Parts_Request"

    next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

    Sheets(ws_output).Cells(next_row, 1).Value = Range("Part_Number").Value
    Sheets(ws_output).Cells(next_row, 2).Value = Range("Project_Request").Value
    Sheets(ws_output).Cells(next_row, 5).Value = Range("Part_Standardisation?").Value
    Sheets(ws_output).Cells(next_row, 6).Value = Range("Detail").Value
    Sheets(ws_output).Cells(next_row, 3).Value = Range("Part_Type").Value
    Sheets(ws_output).Cells(next_row, 4).Value = Range("Product_Type").Value
    Sheets(ws_output).Cells(next_row, 7).Value = Range("Property_1").Value
    Sheets(ws_output).Cells(next_row, 8).Value = Range("Property_2").Value
    Sheets(ws_output).Cells(next_row, 9).Value = Range("Property_3").Value
    Sheets(ws_output).Cells(next_row, 10).Value = Range("Property_4").Value
    Sheets(ws_output).Cells(next_row, 11).Value = Range("FreeText").Value

    Else

    MsgBox "Your part request has been submitted. Please validate your request in the New_Parts_Request tab at the bottom of your screen"

    End Sub

    End If
    End If
    End If
    End If

    ende:
    End Sub

  6. #6
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Triggering Macros if condition is met

    It would be a lot easier if you could use the 'Go Advanced' button at the bottom of the reply window and upload a sample file with no private data in it.

  7. #7
    Registered User
    Join Date
    10-20-2021
    Location
    Canada
    MS-Off Ver
    Microsoft Excel for Office 365 MSO - desktop version
    Posts
    16

    Re: Triggering Macros if condition is met

    Thank you. I tried this but unfortunately, it runs the errormsg if needed, however if the form is correct, it does not seem to call the data_input or Part_Submission? its as if the button is inactive.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Triggering Macros if condition is met

    Please see the yellow banner at the top of the page.

+ 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. [SOLVED] Subsequent Change Events re-triggering other macros
    By Jarris93 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-21-2015, 08:23 AM
  2. [SOLVED] Exclude macros from triggering msgbox
    By Jamesera27 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-04-2015, 11:43 PM
  3. Triggering Macros from Validation List
    By Pratster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2010, 10:16 PM
  4. Userform combo box triggering macros
    By michaelberrier in forum Excel General
    Replies: 6
    Last Post: 06-10-2006, 08:30 AM
  5. Triggering Macros
    By Sloth in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2006, 11:25 AM
  6. triggering macros
    By gvm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2005, 12:05 PM
  7. Triggering an excel menu using macros
    By Sassy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2005, 08:05 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