VBA Help Please

[Scuba94]

Hi,

I need some guidance /help please, I have a workbook with 2 Userforms created, on the main excel workbook the user selects data from a set of drop downs, when a specific drop is selected, I wish to call a sub routine within either userform (linked to the save button in the user form.

Basically I have 2 useforms, one called 'SaveButtonAir_Click' and the other 'SaveButton_Click', if he user selects rLane = CFS Loading, then I wish to call the routine SaveButtonAir, this user from sis a simple menu for the user to input vairoies bits of data, th euser then clicks on thr save button and the user input data is saved to a worksheet called 'FormsControl Sheet', this bit works all fine.

What I'm struggling with is the actually calling of the sub routine, it fails to call it, gives me the below error message.

ANy help please of where I am going qwrong?

Ive tried to use the Application.run to cll the sub as its a private sub but is still fails

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range, rMode As Range, rLane As Range, rLoading As Range
    
    Set r = Target.Cells(1, 1)  'only check first cell
    Set rMode = Range("C3")
    Set rLane = Range("C4")
    Set rLoading = Range("C5")
            
    If r.Address <> rMode.Address And r.Address <> rLane.Address And r.Address <> rLoading.Address Then Exit Sub    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<
    If Len(r.Value) = 0 Then Exit Sub




    'Step 1 - configure
    Application.ScreenUpdating = False      '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Application.EnableEvents = False
    Unprotect Password:="dlm"




    If Target.Address = rMode.Address Then rLane.Value = "Please Select Origin..."
        
    Shapes("RFR20").Visible = False
    'ActiveSheet.CheckBoxes("RFR20").Value = xlOff
    Shapes("RFR40").Visible = False
    Shapes("RFR40HQ").Visible = False
    Shapes("Priority20").Visible = False
    Shapes("Priority40").Visible = False
    Shapes("Priority40HQ").Visible = False
    ActiveSheet.OLEObjects("RFR20").Object.Value = False
    ActiveSheet.OLEObjects("RFR40").Object.Value = False
    ActiveSheet.OLEObjects("RFR40HQ").Object.Value = False
    ActiveSheet.OLEObjects("Priority20").Object.Value = False
    ActiveSheet.OLEObjects("Priority40").Object.Value = False
    ActiveSheet.OLEObjects("Priority40HQ").Object.Value = False
        
    Rows("5:25").Hidden = True
    Rows("79:81").Hidden = True
    Rows("87:" & Rows.Count).Hidden = False
  
'  step 2 - based on Mode, select Lane and configure
    Select Case rMode.Value
        Case "Air"      '   -------------------------------------------------------------------------------------------- Mode = Air
            Select Case rLane.Value
                Case "Warsaw to New York"
                    rLoading.EntireRow.Hidden = False
                    If rLoading.Value = "CFS Loading" Then
                    Application.Run "SaveButtonAir_Click"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5").EntireRow.Hidden = True
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A16").EntireRow.Hidden = True
                    Range("A17").EntireRow.Hidden = False
                    Range("A18:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    Range("A32").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    End If

 

[rlv01]

The error is telling you that the application.run statement cannot find the macro "SaveButtonAir_Click". There are several reasons this might be the case, usually involving issues with which code module contains the calling macro vs. where the "SaveButtonAir_Click" is, and whether the "SaveButtonAir_Click" Sub is defined as public or private.

If Private Sub SaveButtonAir_Click() is located in the same worksheet code module as your Private Sub Worksheet_Change(ByVal Target As Range) code, then one thing you might try is to change this

 Application.Run "SaveButtonAir_Click"

to this

 Call  SaveButtonAir_Click

 

[Scuba94]

The error is telling you that the application.run statement cannot find the macro "SaveButtonAir_Click". There are several reasons this might be the case, usually involving issues with which code module contains the calling macro vs. where the "SaveButtonAir_Click" is, and whether the "SaveButtonAir_Click" Sub is defined as public or private.

If Private Sub SaveButtonAir_Click() is located in the same worksheet code module as your Private Sub Worksheet_Change(ByVal Target As Range) code, then one thing you might try is to change this

 Application.Run "SaveButtonAir_Click"

to this

 Call  SaveButtonAir_Click

Hello,
Thank you for your response, I have tried this but no amount of changing seems to get it to work.
Please allow me to elaborate further on this
SaveButton_Click = is in the FORMS Module (set to Public)
SaveButtonAir_Click = is in the FORMS Module (set to Public)

My VBA script is in the Sheet1(Rate Calc) module

I am at a loss as to why I can get this to work currently, hence me reaching out or additional help

 

[Scuba94]

Hello,
Thank you for your response, I have tried this but no amount of changing seems to get it to work.
Please allow me to elaborate further on this
SaveButton_Click = is in the FORMS Module (set to Public)
SaveButtonAir_Click = is in the FORMS Module (set to Public)

CallCFSAirMacro = Sheet1(Rate Calc Module)

My VBA script is in the Sheet1(Rate Calc) module

View attachment 100219
I am at a loss as to why I can get this to work currently, hence me reaching out or additional help

Sub CallCFSAirMacro()
Call SaveButtonAir_Click
End Sub

 

[rlv01]

Calling code that lives inside a userform code module from an outside module is tricky. You might be better off duplicating the SaveButtonAir_Click code in your Rate_Calc sheet's code module. Or, and this depends on what SaveButtonAir_Click code is doing, create a common subroutine in a standard module that is callable by both SaveButtonAir_Click and your worksheet code.

 

[Scuba94]

Calling code that lives inside a userform code module from an outside module is tricky. You might be better off duplicating the SaveButtonAir_Click code in your Rate_Calc sheet's code module. Or, and this depends on what SaveButtonAir_Click code is doing, create a common subroutine in a standard module that is callable by both SaveButtonAir_Click and your worksheet code.

Hi,
Yes this is what I am trying with the 'CallCFSAirMacro()' routine, this sits in the same module as the Rate Calc and is called when Rloading = CFS Loading, the sub rountie CallCFSAirMacro() then calls SaveButtonAir_Click but I cannot get this to work?

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Code to call another VBA function when certain cells match a given criteria
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Scuba94]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Code to call another VBA function when certain cells match a given criteria
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

HI,

Sorry I wasn't aware these 2 forums were linked, my intention is not to cause more work but to fix my issue as quick as possible.
Rule 13 contradicts itself

Quote

• Post the details of your question on our forum. Do NOT simply post a link/re-direct to the question in another forum with no details posted here.
• Make it clear that you have cross-posted and provide links to the cross-posts

Unquote

Do not post a link yet next point states to provide links, so which is is, do we provide or not?

What I can assure is, if resolved on either forum, both forum posts will be closed by me, you have my word on that.
regards

Richard

 

[Georgiboy]

Point one above is stating that you have to ask the question here, you can't ask the question elsewhere and then simply post a link to the other forum here.

 

[Peter_SSs]

Sorry I wasn't aware these 2 forums were linked,

They are not linked - other than that some people may volunteer to help on both forums.

Rule 13 contradicts itself

There is no contradiction. The first point does not say "Do not post a link" it says "Do not simply post a link .. with no details here".

If you read the link at the end of the rule as requested, you would better understand why all the Excel forums (that I know of) have a similar rule about this.