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]

Cross posted at: VBA Code to call another VBA function when certain cells match a given criteria

Also at

VBA Code to call a sub within a Userform from outside the Userform

I am new to VBA but learning more each day, I have a rate calc created and with in it are 2 userforms created CFSCollectionAir & CFSCollectionOcean, which when invoked, the user can input various

stackoverflow.com

 

[rlv01]

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

If you cross-post, it is bad manners not to post a link(s) to the other forum(s) right away. Everyone helping you, either here or on the other forum you have posted to, is donating their time. By not posting a link, you are wasting our time.

 

[Scuba94]

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


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.

HI sorry my intention is not to break rules here, merely to find a solution to my issues, below is the link.

VBA Code to call another VBA function when certain cells match a given criteria

HI all, I have the below code that I need to adjust to include some further if and statements, basically I need to incorporate the below into my code. If Range("C4").Value = "Warsaw to New York" or Range("C4").Value = "Warsaw to Los Angeles" And Range("C5").Value = "CFS Loading" Then...

www.vbaexpress.com

 

[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,
OK I have successful managed to call the sub routine from the main page, however, if I run the VBA code using F5 key directly, all works fine and the data is saved correctly, however, when I call the sub from another sub, it does run and the user can input the data but upon closing the userform, the data is not copied over, it is copied over when run directly using the F5 key so I so close now but obviosky missing something, probably something simple too lol

 

[rlv01]

HI,
OK I have successful managed to call the sub routine from the main page, however, if I run the VBA code using F5 key directly, all works fine and the data is saved correctly, however, when I call the sub from another sub, it does run and the user can input the data but upon closing the userform, the data is not copied over, it is copied over when run directly using the F5 key so I so close now but obviosky missing something, probably something simple too lol

You just asked Aussiebear over at VBAExpress more or less the same thing at about the same time you posted this here. Not to mention your other concurrent post at StackOverflow. I feel like you have not read the link referred to you earlier or really understand why this is a problem

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →

excelguru.ca

Ask yourself this: How would you feel if you worked on an answer for an hour, only to find out that fifteen minutes after you started, someone already had a solution they accepted? I'd be willing to bet you'd be a little upset that you'd wasted your time.

Yes, I know that you are only trying to get an answer, but seriously evaluate what you've done. Would you go and pay four different experts to generate solutions and then pick the best one? Well maybe you would, but if that is your approach, ask for their Paypal accounts up front and pay for it.

Lest you choose to just flat out ignore my advice, YOU will be the one being ignored. News of chronic cross posters travels quickly, and you may find yourself with no replies at any forum. Remember that the online community is both larger and smaller than you think. Many people transcend boards, and are staunch about protecting their efforts and the efforts of their friends who don't.

 

[Scuba94]

All, I will close this post off to avoid any confusion, my intention is not to cause any more work, thank you all for you help on this forum, I will leave only on the other forum.

 

[Peter_SSs]

I will close this post off

In the MrExcel forum, the check mark at the right is to mark a solution to the question, not a thread closure mark. We generally don't actually close threads as somebody might come along later with a fantastic solution and we don't want to prevent that. Since post 16 isn't a solution I have removed the check mark. It is quite okay to have no post marked as the solution if they don't do that,