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

[Scuba]

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    Call SaveButtonAir_Click()
Else
    'Do nothing

I tried to insert it below for Warsaw to New York to begin with and was going to follow with Warsaw to Los Angeles once I got it working but I cannot get to work for New York currently.

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
            If rLoading.Value = "CFS Loading" Then
            Call SaveButtonAir_Click
            rLoading.EntireRow.Hidden = False
            Else
             rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True
                           
            Select Case rLane.Value
                Case "Warsaw to New York"
                    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
                                           
                Case "Warsaw to Los Angeles"
                    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("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

[June7]

What does "cannot get to work" mean - error message, wrong result, nothing happens? Have you step debugged?

Suggest you provide file for analysis. Follow instructions at bottom of my post.

[Scuba]

Hi June7,

Thank you for responding so quickly

I have uploaded the file.

Basically when rMode = Air &
rLane = 'Warsaw to New York' or 'Warsaw to Los Angeles' &
rLoading = 'CFS Loading' then I need to call a routine called 'SaveButtonAir_Click', this brings up a FormsControl menu for the user to input various other bits of information and when the close it, it saves the information off to a specific tab called 'FormsControl Sheet', this bit I have all working and saving etc.

Its the adding the code in without disrupting everything else.

Once i have the air bit working correctly, I can then look at adding ins same for ocean, see below

I also need to do something similar for the Ocean (rMode= Ocean_EU_to_US), to call 'SaveButton_Click'

in a nutshell, whenever rLoading = CFS Loading, I need to call upon the routine 'SaveButtonAir_Click' if rMode = Air or 'SaveButton_Click' if Ocean
If rLoading = Factory Loading, then no need to call any routine as the excel sheet takes care of the rest.

Hopefully I've explained this well enough, if not, let me know and thank you for your help.

Regards

Richard

[arnelgp]

the sub SaveButtonAir_Click() is Private on userform CSFCollectionAir, you need to make it Public and make the userform Available, thus call it using UserFormName.SaveButtonAir_Click.
another approach is to move the bulk of the code of the button to a Public Sub/Function in a Module.

[Scuba]

Hello,

OK I've tried but have not been able to get this to work and need some help please, I have attached the report for reference, I get the below error when running the sub routine.


VBA Error.jpg
Below is a snippet of the code i have tried to use

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
                    Call CallCFSAirMacro
                    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

[Scuba]

Hello,

Please see post number 5 for answers

[June7]

Doesn't look like you tried suggestions in post #4.

[Scuba]

HI,

Yes I did try the suggestions from post 4 but could not get them to work, hence why I reached out for further help

Regards

Richard

[June7]

You are still calling button click without form name prefix.

I am not sure about syntax in Excel VBA but in Access it could simply be: Forms!CFSCollectionAir.SaveButtonAir_Click

The form must be open. I don't find code opening form.

Otherwise, move code to a general module. Call that procedure wherever needed, even from button click.

[Aussiebear]

Richard, I have to agree with both arnelgp & June7. You currently have the Sub SaveButtonAir_Click as Private with the form CFSCollectionAir. Did you try changing it from Private to Public? Also try putting this sub in a Module of its own. Currently your error message is simply telling you that it cannot find the sub. Also I'm kind of wondering why you haven't got this as a database using a front end and a back end. No doubt someone may have already suggested this to you.

[georgiboy]

Have you cross posted this question?

If you have, you should post a link to the thread on the other forum.

Someone could be working on this here while it is solved elsewhere & vice versa.

Cross post link:
https://www.mrexcel.com/board/thread...lease.1246773/

[Scuba]

Have you cross posted this question?

If you have, you should post a link to the thread on the other forum.

Someone could be working on this here while it is solved elsewhere & vice versa.

HI

I didn't realise these 2 forums were linked, I thought they were separate, my bad.
If it get fixed on either forum, I will be closing both down for sure, my intention is not to create more work here but to find a solution as quick as possible.

[Scuba]

HI Aussiebear,

Yes this I have tried, currently both Sub SaveButton_Click & Sub SaveButtonAir_Click are both set to Public.

The sub CallCFSAirMacro() is also Public.

When i run and change rLoading to read CFS Loading I get the below error message?
Error Message.jpg

As for you reference to a database, no one one as mentioned this yet but I am a very new novice when it comes to VBA so baby steps for me

[georgiboy]

I didn't realise these 2 forums were linked, I thought they were separate, my bad.
If it get fixed on either forum, I will be closing both down for sure, my intention is not to create more work here but to find a solution as quick as possible.

While these forums are not interconnected, it's worth noting that a substantial number of our members also participate in other forums. It's a general practice across these platforms not to prohibit cross-posting. Instead, we kindly request that when you share a question or issue on multiple forums, you include a link to each of these posts. This approach helps us avoid duplicating efforts and allows users to check if a solution already exists on another forum, ultimately respecting the valuable time freely given by our members.

[rlv]

Have you cross posted this question?
Someone could be working on this here while it is solved elsewhere & vice versa.

Cross post link:
https://www.mrexcel.com/board/thread...lease.1246773/

Someone was

Also cross-posted on at least one other:

https://stackoverflow.com/questions/...e-the-userform

OP has not been too receptive to hints about linking to his cross-posts.

[June7]

As already advised, need to prefix form name to the event name when calling. See posts 4 and 9. Form must be open when calling its code. I did not find any code that opens form.

Also advised alternative to move code to a procedure in general module which can then be called from wherever, including button click.

[Aussiebear]

Richard, please post your link to every other forum where you have posted this issue.

[Scuba]

Richard, please post your link to every other forum where you have posted this issue.

HI,

Apologies for delay, please find below link

VBA Help Please | MrExcel Message Board

[Scuba]

Hi Aussiebear,

I have now successfully managed to call my sub from within another sub, the Userform opens correctly and the user can input the data, upon closing the Userform, using the 'Close & Submit' button nothing is saved off to the excel tab, however, if I run the called sub directly using F5 key, it saves all OK, so I'm very close but obviously missing something no doubt simple.

So I'm hoping someone on this forum can point me in the right direction.

I've uploaded the latest worksheet here.

If you open the workbook, select Air and then 'Warsaw to New York' and change the Factory or CFS Loading to 'CFS Loading' (if its not already) and then the Userform will open all fine, key in the data, click the close button and then navigate to the tab 'FormsControl Sheet', no data will have been copied over.

If you open the VBA Editor screen and view the code being called under the Forms, CFSCollectionAir, hit F5, again the userform open fine, key in the data, click the save button but this time the data is being stored/saved in the tab 'FormsControl Sheet, it just does save when called/run from the main page?

Help please

[arnelgp]

you are calling the Sub two times. first time it will save the data and close the userform, then again you call it again but all the variable values are lost when you close the userform before, so the values on the worksheet are then overwritten with nothing.

now it only save once, only the first, unique location will be accounted for.

not yet fully tested and i will leave that to you