[SOLVED:] VBA Code to call another VBA function when certain cells match a given criteria

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.

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.

Code:

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

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.

1 Attachment(s)

Hi June7,

Thank you for responding so quickly :yes

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

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.

2 Attachment(s)

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.

http://www.vbaexpress.com/forum/imag...BJRU5ErkJggg==
Attachment 31105
Below is a snippet of the code i have tried to use

Code:

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

Hello,

Please see post number 5 for answers :)

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

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

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.

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.

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/

Quote:

Originally Posted by 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.

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.

1 Attachment(s)

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?
Attachment 31113

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

Quote:

Originally Posted by Scuba

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.

Quote:

Originally Posted by georgiboy

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.

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.

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

Quote:

Originally Posted by Aussiebear

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

1 Attachment(s)

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

1 Attachment(s)

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

Thank you very much Arnelgp,

I see my error now, I was calling the sub twice, when in fact I just needed to 'Show' the Userform first and only allow the sub to run once the user clicks on the Save button.

I also see you have improved my rudimentary coding, I knew I was doing it the long way round but my VBA knowledge is still very low and I'm learning more as each day passes, I will try my best to decipher this as I need to replicate it again for the other Userform(ocean one), my goal was to get this Air one working first, then to replicate for the Ocean form.

Once again thank you for your help, much appreciated :bow:

Upon checking this appears to only copy the first 3 lines of data over, lines 4, 5 & 6 are blank, even if data is keyed into the Userform?

Regards

Richard

like i said it will only copy 3 data, since on your form, you only have 3 (distinct) location. i don't see the logic of you filling up the carton/ pallets of six location with same location (if you select 6 "PL - 08-400 Garwolin").

HI,

My apologies if I was not clear, the reason there are 6 is because we potentially can have multiple collections from the same location, so I do need all 6 to be accounted for.:thumb

What do I need to change to include the other 3 lines?

1 Attachment(s)

ok, i removed the the dictionary object from the code.
test it now.

All perfect my friend, many thanks for your help, much appreciated:bow:

I will attempt to understand your coding so I can reuse elsewhere :yes

Thank you

Richard.

:hi: