Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 26

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

  1. #1
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location

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

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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
    Attached Files Attached Files

  4. #4
    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.

  5. #5
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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

  6. #6
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    Hello,

    Please see post number 5 for answers

  7. #7
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Doesn't look like you tried suggestions in post #4.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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

  9. #9
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,182
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    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/
    Last edited by georgiboy; 10-13-2023 at 02:47 AM.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  12. #12
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location

    Post

    Quote Originally Posted by georgiboy View Post
    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.

  13. #13
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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

  14. #14
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    Quote Originally Posted by Scuba View Post
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  15. #15
    Quote Originally Posted by georgiboy View Post
    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.

  16. #16
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  17. #17
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,182
    Location
    Richard, please post your link to every other forum where you have posted this issue.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  18. #18
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    Quote Originally Posted by Aussiebear View Post
    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

  19. #19
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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

  20. #20
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •