IF statment nested within VBA Code

[Scuba]

Hello everyone, Noob Scuba here :)

I have only just began to dabble with VBA code and have created the below code, which works perfectly for me, until such time as I introduce some extra IF statements and then it breaks, so I'm looking for some help please, if possible.

I have an excel spreadsheet (its a rate calculator) and I would the existing code below to still run as is but also be able to insert additional if statements under each 'CASE' headings as I need to hide or unhide certain rows depending on what text is inserted into cell C4 of the excel sheet.

I'm using Office 365.

Hopefully some here can help me understand what I'm doing wrong.

Thanks
Richard


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If
Dim changed As Range
Set changed = Intersect(Target, Range("C3"))
If Not changed Is Nothing Then
Select Case Target.Value
Case "Air"
ActiveSheet.Unprotect Password:="dlm"
Range("A10:A19").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A56:A58").EntireRow.Hidden = True
If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Warsaw to New York") Then Range("A23").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"
Case "Ocean_Asia_to_EU"
ActiveSheet.Unprotect Password:="dlm"
Range("A8:A15").EntireRow.Hidden = True
If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"
Case "Shanghai to Genoa"
ActiveSheet.Unprotect Password:="dlm"
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
Range("C15:D15").ClearContents
Range("C14:D14").ClearContents
Range("D17:D19").ClearContents
Range("C8:D8").ClearContents
Range("C9:D9").ClearContents
ActiveSheet.Protect Password:="dlm"
Case "Overland"
ActiveSheet.Unprotect Password:="dlm"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C11:D11").ClearContents
Range("C12:D12").ClearContents
Range("C13:D13").ClearContents
Range("C14:D14").ClearContents
Range("C15:D15").ClearContents
Range("D17:D19").ClearContents
ActiveSheet.Protect Password:="dlm"
End Select
Range("C3").Select
End If
End Sub

PS i have no idea why emojis are replacing my text, when i edit and change to text, the emojis appear again?

[Aussiebear]

Welcome to VBAX Richard. It an unfortunate thing within Excel that Smileys invade our text, formula and code. Microsoft have much to answer for in this aspect.

In relation to your code,


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If
Dim changed As Range
Set changed = Intersect(Target, Range("C3"))
If Not changed Is Nothing Then

I'm struggling to understand the logic of your intention here. From your code I can only assume that the sheet is password protected, so how does one select cell C3? If cell C3 is not the target what then was the value of cell C4? This type of code should be something like this

Please note that in VBA, all "If" statements are bound by "If" and "End If". You have a number of "If" statements that have no "End If", which is non logical from a VBA point of view.

[Scuba]

Hi,
Firstly thank you for you quick answer and second apologies for my rudimentary coding.
I'm still very new to VBA and learning by the day :)

This code works alongside excel, I have 2 drop cells )first one (c3) and 2nd one C4, which is conditional on what has been selected from C3, when the users selects from a drop down cell (C3), I auto automictically insert the value 'Please Select Origin...' into C4, because if I did not do this a lot other cells in excel show 'False' because the formulas can't calculate as the 2nd conditional drop down has not been selected ie cell (C4).
the issue I'm having is everything works fine if don't add in additional IF statements, but the second I do nothing works, this worksheet has evolved now from the original where I could just hide and unhide specific rows base don the value in C3, i now need to add additional criteria to hide extra rows and also Active X check boxes but one step at a time, I would be happy to just be able to hide and unhide specific rows base of a value in C4, I then work on the check boxes afterwards.

I hope I'm making sense, sorry if I'm not, in summary I have a set of criteria that runs when C3 is selected, then under each heading that the user selects from C3 I need to run additional criteria based of cell C4 ie just like a conditional drop works

[Aflatoon]

Please note that in VBA, all "If" statements are bound by "If" and "End If".

That is not actually true. You can have single line If ... Then ... statements that do not require an End If. So this is perfectly valid syntax:


If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"


but the blue part is the only part that is contingent on the If statement. The three lines that follow are only contingent on the original Case statement.

[Scuba]

Understood, so how do you make so both parts are work together, i.e. the Case statement runs and then the additional If statements within each Case statement run as well.
I only shows you 2 additional IF statements, I have more but just showed 2 because if I can get those 2 to work, I can probably work out how to get the rest working :)

[June7]

if you mean there were emojis in your posted code, it was probably because you did not initially post code between CODE tags. Aussiebear edited your post to correct this. The forum will substitute certain character sequences for emojis unless you go to Advanced Editor and turn this off. Turn off is only for that edit session. Have to do it every time. Has nothing to do with MS or Excel or VBA. The takeaway here is to post code between CODE tags.

[Scuba]

Sorry, very new to this forum and full rules etc, I'll learn, my questions were not relating to the way this forum changes my code when copying but how to get my example VBA to work correctly :)

[June7]

i know that but you still needed to know how to correctly post code and why you got emojis.

Are you saying the two IIf() statements cause failure? What does "failure" mean - error message, wrong result, nothing happens?

Might provide your workbook for analysis. Follow instructions at bottom of my post.

[Aflatoon]

If all of those lines should be part of the If test, then it does require an End If as it is no longer a single line construction:


If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then
Range("A23").EntireRow.Hidden = True Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"
End If

[Scuba]

thanks VBAS Master, I'll try to add in some 'End If's' and see how I get one, I'm about to log off for the day but I'll be back on tomorrow, thanks for all your help everyone :)

[Aussiebear]

Okay, now that I've had 4 hours of sleep, lets deal with a couple of issues.

Firstly @ June7, Emojis can and often do occur outside of the "forum". One can be constructing a document in word and using sub paragraphs denoted by (a), (b), © etc and the emojis will appear. it is definitely a MS thing, and one they have not fixed.

@Afltoon, After having a second thoughts about your statement, I agree, I was wrong, however as the code is currently presented by Richard, I am inclined to believe that there were a number of conditional statements attached to the "If" occurring on another sheet namely "Rate Calc". On that basis I believe you require an "End If" to enable returning to the Active sheet to continue the Case Select process. I may well be wrong, but that is my reading from the code as presented in Post #1.


If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True


It's unclear to me, whether the ActiveSheet and ActiveWorkbook.Sheets('Rate Calc") are one and the same or different. If they are diferent sheets then this begs the question, on which sheet are we trying to hide the rows A23 and A51:A74?

@ Scuba, As I currently understand your ActiveSheet has two dropdown cells namely C3 and C4. Cell C3 has 4 options namely, "Air", "Ocean_Asia to EU", "Shanghai to Genoa" and "Overland", and you desire certain actions to take place depending on which option has been selected. The relevance of the message in cell C4 is still unclear to me. Is it because you are asking the user to select an option in cell C3 or C4? If its to select an option in cell C3 then a simple Messagebox would suffice.

Lets look at the actions undertaken in the first option of the case select, namely "Air"

I believe you could write this as ;


Case "Air"
ActiveSheet.Unprotect Password:="dlm"
Range ("A10:A19, A39:A43, A56:A58").EntireRow.Hidden = True _
.....
ActiceSheet.Protect.Password:= "dlm"


Now comes the misleading bit. Can you clarify if the Activesheet and ActiveWorkbook.Sheets("Rate Calc") are different or the same sheet? In post #1 you suggested the cell C4 has a value "Please select Origin...". Did you intend for "Warsaw to New York" to be one of the options for the dropdown in cell C4? If it was an option then it messes with lines 2 and 3 of your original code as presented.



If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = "Warsaw to New York" Then _
Range("A23").EntireRow.Hidden = True



Then we look at the case select option "Ocean_Asia to EU"



Case "Ocean_Asia_to_EU"
ActiveSheet.Unprotect Password:="dlm"
Range("A8:A15").EntireRow.Hidden = True
If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"


This gets back to the point raised by Aftloon, as to whether ranges A51:A74 are part of the If statement or part of the conditions of the Case Select option. Also the line "Range("A57").entireRow.Hidden = true" is unnecessary since in the next line you have included it within the range A51:A74 as hidden.

In the next select option "Shanghai to Genoa", you have conflicting objectives.


Case "Shanghai to Genoa"
ActiveSheet.Unprotect Password:="dlm"
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
Range("C15:D15").ClearContents
Range("C14:D14").ClearContents
Range("D17:D19").ClearContents
Range("C8:D8").ClearContents
Range("C9:D9").ClearContents
ActiveSheet.Protect Password:="dlm"


You are both hiding and unhiding certain rows, such as A12 for example. Which do you need it to be hidden or unhidden? Next you are attempting to Clear Contents of Rows that are hidden. What purpose does this serve?

Then we look at the next Select option "Overland"


Case "Overland"
ActiveSheet.Unprotect Password:="dlm"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C11:D11").ClearContents
Range("C12:D12").ClearContents
Range("C13:D13").ClearContents
Range("C14:D14").ClearContents
Range("C15:D15").ClearContents
Range("D17:D19").ClearContents
ActiveSheet.Protect Password:="dlm"


Again you are over complicating things here by suggesting to hide rows which are then included within the next lines grouping to be hidden. Further down you suggest that Row A14 is not hidden but three lines down you surest its part of a group of lines that are hidden. Then again further down in the code you are clearing the contents of rows that are hidden from the User. What is the intent of doing this?

Can you either attach a sample workbook for us to see what you are attempting to complete, or failing that, please write out in plain English the logic flow of what you would like to happen please?

[deger]

Hello everyone, Noob Scuba here :)

I have only just began to dabble with VBA code and have created the below code, which works perfectly for me, until such time as I introduce some extra IF statements and then it breaks, so I'm looking for some help please, if possible.

I have an excel spreadsheet (its a rate calculator) and I would the existing code below to still run as is but also be able to insert additional if statements under each 'CASE' headings as I need to hide or unhide certain rows depending on what text (https://gbwadownload.pro/gb-whatsapp-download/) is inserted into cell C4 of the excel sheet.

I'm using Office 365.

Hopefully some here can help me understand what I'm doing wrong.

Thanks
Richard


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If
Dim changed As Range
Set changed = Intersect(Target, Range("C3"))
If Not changed Is Nothing Then
Select Case Target.Value
Case "Air"
ActiveSheet.Unprotect Password:="dlm"
Range("A10:A19").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A56:A58").EntireRow.Hidden = True
If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Warsaw to New York") Then Range("A23").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"
Case "Ocean_Asia_to_EU"
ActiveSheet.Unprotect Password:="dlm"
Range("A8:A15").EntireRow.Hidden = True
If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"
Case "Shanghai to Genoa"
ActiveSheet.Unprotect Password:="dlm"
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
Range("C15:D15").ClearContents
Range("C14:D14").ClearContents
Range("D17:D19").ClearContents
Range("C8:D8").ClearContents
Range("C9:D9").ClearContents
ActiveSheet.Protect Password:="dlm"
Case "Overland"
ActiveSheet.Unprotect Password:="dlm"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C11:D11").ClearContents
Range("C12:D12").ClearContents
Range("C13:D13").ClearContents
Range("C14:D14").ClearContents
Range("C15:D15").ClearContents
Range("D17:D19").ClearContents
ActiveSheet.Protect Password:="dlm"
End Select
Range("C3").Select
End If
End Sub

PS i have no idea why emojis are replacing my text, when i edit and change to text, the emojis appear again?Firstly, there are a few issues in your code:

In the Set changed = Intersect(Target, Range("C3")) line, you're using Range("C3"), but you mentioned you want to check cell C4. You should change that line to Set changed = Intersect(Target, Range("C4")).

In your Select Case Target.Value statements, you are checking ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value, which is always checking the value of cell C4 on the "Rate Calc" sheet. If you want to check the value in cell C4 of the current worksheet, you should replace it with Range("C4").Value.

Here's an updated version of your code with these corrections:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If

Dim changed As Range
Set changed = Intersect(Target, Range("C4"))

If Not changed Is Nothing Then
Select Case Target.Value
Case "Air"
' Your code for hiding/unhiding rows for "Air" goes here
Case "Ocean_Asia_to_EU"
' Your code for hiding/unhiding rows for "Ocean_Asia_to_EU" goes here
Case "Shanghai to Genoa"
' Your code for hiding/unhiding rows for "Shanghai to Genoa" goes here
Case "Overland"
' Your code for hiding/unhiding rows for "Overland" goes here
End Select
End If
End Sub
You can now add additional Case statements under the Select Case structure for other conditions you want to check in cell C4 and adjust the code accordingly for each case.


Please make sure to replace ' Your code for hiding/unhiding rows for ... goes here with your actual code for hiding/unhiding rows based on the condition in cell C4.

[Scuba]

Hello Aussiebear,

Glad you had some sleep, although 4 hours is not long really lol

I'll do my best to answer all your point as clearly as possible

Confirming there are some IF statements on the rate Calc sheet and that ActiveSheet and ActiveWorkbook.Sheets('Rate Calc") are one and the same, that's just my ham fisted way of trying to get it to work for me, sorry my :banghead: bad.

Cell 3 is my primary drop down list and consists of the below:


Air
Ocean_EU_to_US
Ocean_Asia_to_EU
Overland


Cell C4 is my Secondary drop down and is dependent on what is selected from the primary dropdown (C3)


Air
Warsaw to New York
Warsaw to Los Angeles
Malpensa to New York
Malpensa to Los Angeles
Heathrow to New York
Heathrow to Los Angeles



Ocean_EU_to_US


Genoa to New York


Genoa/La Spezia to Los Angeles


Gdynia to New York


Gdynia to Los Angeles


Hamburg to New York


Hamburg to Los Angeles


FXT/SOU to New York


FXT/SOU to Los Angeles






Ocean_Asia_to_EU
Shanghai to Genoa
Xiamen to Genoa
Shanghai to Gdynia/Gdansk
Xiamen to Gdynia/Gdansk
Shanghai to SOU/FXT
Xiamen to SOU/FXT

Overland
PL to UK
UK to PL



I insert the value '"Please select Origin..' into cell C4 so its a prompt to tell the user to select from that drop down, it also serves another purpose in that, if that is displayed in C4 then my Rate Calc fields are all blank otherwise I get odd data appearing in the main rate calc sheet.



I am bastardising this Rate Calc from another customer of mine and so some of the actual lines that are showing hidden/unhidden/clear etc will be incorrect, this I'm not concerned with as I will clean this up once I understand how to incorporate the Case statements alongside the additional IF statements.

There will be some cross over between what is asked to be hidden and what is asked to be shown, based on what is selected in the primary drop down C3 and then further hidden/reveal requests when the secondary drop list activated.
for example when Air is selected I don't need to show rows 23 to 25 as these are container information that is only used for Ocean movements.

If its easier i can share the actual document, once i work out how to attach it to this forum, i looked at all the icons but couldn't see once for attaching something, only for inserting an image or a video, no documents

Once again thank you for you help and patience :yes

[Scuba]

Hi Deger,

Thank you for your guidance, I will attempt to try this and will revert if I get any issues.

However when I think about this, I don't think this will work, unless I am misunderstanding something here(which is possible lol).

The Case values are referring to cell C3, whereas the IF statements will be referring the cell C4 so not sure if this change will work as Case Air = cell C3 and IF statements will = Cell C4 so when using Case "Air" wont it fail as we have said 'Set changed = Intersect(Target, Range("C4"))'?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If
Dim changed As Range
Set changed = Intersect(Target, Range("C4"))
If Not changed Is Nothing Then
Select Case Target.Value
Case "Air"
' Your code for hiding/unhiding rows for "Air" goes here
Case "Ocean_Asia_to_EU"
' Your code for hiding/unhiding rows for "Ocean_Asia_to_EU" goes here
Case "Shanghai to Genoa"
' Your code for hiding/unhiding rows for "Shanghai to Genoa" goes here
Case "Overland"
' Your code for hiding/unhiding rows for "Overland" goes here
End Select
End If
End Sub



Once again thank you and I am so glad I have chanced across this forum, it is really great.

regards

Richard

[Aussiebear]

Welcome to VBAX Deger, Thank you for your contribution, for it's nice to see new contributors. Just a couple of quick housekeeping hints, we try to wrap any supplied code with code tags. Please refer to the first line in my signature for a hint. Also it's not necessary to fully quote a response when making a post. I know they do it on some of the other forums, but here we take the view that it simply is a waste of whitespace. That aside, please keep up the great work.

[Aussiebear]

@Scuba, click on Go Advanced, Manage Attachments, choose file, and Upload to attach a workbook. Make a note of the file size limits.

From your indications in post #13, there's probably no need to have Cell C4 reflect any message as its dependant dropdown depending on what has been selected in Cell C3. Other than that follow the advice offered by Deger, and workout clearly what needs to hidden or unhidden. BTW the same housekeeping advice go to you as well, please wrap your code with code tags.

[Scuba]

Noted and understood, file uploaded :)

[Scuba]

Stupid noob question, but what and where do I find 'Code Tags'?

[georgiboy]

How does it do with a few changes below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
Application.EnableEvents = True
Exit Sub
ElseIf Target.Address = "$C$4" Then
ActiveSheet.Unprotect Password:="dlm"
Select Case Target.Offset(-1).Value
Case "Air"
Range("A10:A19").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A56:A58").EntireRow.Hidden = True
If Target.Value = "Warsaw to New York" Then
Range("A23").EntireRow.Hidden = True
End If
Case "Ocean_Asia_to_EU"
Range("A8:A15").EntireRow.Hidden = True
If Target.Value = "Shanghai to Genoa" Then
Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
End If
Case "Shanghai to Genoa"
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
Range("C15:D15").ClearContents
Range("C14:D14").ClearContents
Range("D17:D19").ClearContents
Range("C8:D8").ClearContents
Range("C9:D9").ClearContents
Case "Overland"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C11:D11").ClearContents
Range("C12:D12").ClearContents
Range("C13:D13").ClearContents
Range("C14:D14").ClearContents
Range("C15:D15").ClearContents
Range("C17:D17").ClearContents
Range("C18:D18").ClearContents
Range("C19:D19").ClearContents
End Select
ActiveSheet.Protect Password:="dlm"
Range("C3").Select
End If

Application.EnableEvents = True
End Sub

In the code above the RED parts relate to C3 dropdown, the BLUE parts relate to C4 dropdown

[georgiboy]

Stupid noob question, but what and where do I find 'Code Tags'?

When you are writing the post, it is the # symbol in the mini ribbon

[georgiboy]

Just updated post 19

[Scuba]

Hello,

OK I have substituted your code for mine and inserted the relevant code for hiding/unhiding certain rows, all works fine if left like that, but the minute I add in an 'IF statement', I get the below error, keep in mind I was on Air for cell C3 & 'Warsaw to New York' for cell C4, I didn't get to change the cell C4 to match 'Malpensa to New York' before the error happened, the moment i selected Air in Cell C3 I got the error

Below is the code I was using.


Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If
Dim changed As Range
Set changed = Intersect(Target, Range("C4"))
If Not changed Is Nothing Then
Select Case Target.Value
Case "Air"
ctiveSheet.Unprotect Password:="dlm"
ActiveSheet.CheckBox1.Visible = False
ActiveSheet.CheckBox2.Visible = False
ActiveSheet.CheckBox3.Visible = False
ActiveSheet.CheckBox4.Visible = False
ActiveSheet.CheckBox5.Visible = False
ActiveSheet.CheckBox6.Visible = False
Range("A5:A6").EntireRow.Hidden = True
Range("A8:A11").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = True
Range("A16").EntireRow.Hidden = True
Range("A18:A25").EntireRow.Hidden = True
Range("A13:A14").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A52:A58").EntireRow.Hidden = True
If Range("C4").Value = "Malpensa to New York" Then
Range("A17:A21").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"
Case "Ocean_EU_to_US"
ActiveSheet.Unprotect Password:="dlm"
ActiveSheet.CheckBox1.Visible = True
ActiveSheet.CheckBox2.Visible = True
ActiveSheet.CheckBox3.Visible = True
ActiveSheet.CheckBox4.Visible = True
ActiveSheet.CheckBox5.Visible = True
ActiveSheet.CheckBox6.Visible = True
Range("A13").EntireRow.Hidden = True
Range("A8:A15").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = False
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
ActiveSheet.Protect Password:="dlm"

[Scuba]

thank you

[georgiboy]

Have you tried replacing all of your code with the code from post 19?

NOTE: It will run when C4 has been changed, the reson for this is that you reference C4 being different values in your code - but the beginning of your code changes C4. This means that the value of C4 will always be the same whenever your code fires. I edited your code (post 19) to change the value of C4 only when C3 has been changed, when you then change C4 it will fire the rest of the code.

[georgiboy]

Here is another way of looking at it, seperating the two parts:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$C$3" Then
Range("C4") = "Please Select Origin..."
ActiveSheet.Unprotect Password:="dlm"
Select Case Target.Value
Case "Air"
Range("A10:A19").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A56:A58").EntireRow.Hidden = True
Case "Ocean_Asia_to_EU"
Range("A8:A15").EntireRow.Hidden = True
Case "Shanghai to Genoa" 'This one seems to be an issue
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
Range("C15:D15").ClearContents
Range("C14:D14").ClearContents
Range("D17:D19").ClearContents
Range("C8:D8").ClearContents
Range("C9:D9").ClearContents
Case "Overland"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C11:D11").ClearContents
Range("C12:D12").ClearContents
Range("C13:D13").ClearContents
Range("C14:D14").ClearContents
Range("C15:D15").ClearContents
Range("C17:D17").ClearContents
Range("C18:D18").ClearContents
Range("C19:D19").ClearContents
End Select
ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
Exit Sub
End If

If Target.Address = "$C$4" Then
ActiveSheet.Unprotect Password:="dlm"
Select Case Target.Offset(-1).Value
Case "Air"
If Target.Value = "Warsaw to New York" Then
Range("A23").EntireRow.Hidden = True
End If
Case "Ocean_Asia_to_EU"
If Target.Value = "Shanghai to Genoa" Then
Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
End If
End Select
ActiveSheet.Protect Password:="dlm"
End If

Application.EnableEvents = True
End Sub

In the code above the RED text should only have options that relate to C3 dropdown, the BLUE text should only have options that relate to C4 dropdown.

[georgiboy]

Sorry, got into a bit of an editing frenzy. I have now completed my edits to Post 19 & Post 25.

Still a few issues in there like on this row:

Range("A23").EntireRow.Hidden = True
You hide row 23, but i don't see anywhere in the code that row 23 is made visible again.

[Paul_Hossler]

1. You need to be a little careful with things like that

If you change more than on cell, Target is the entire range. You may think that it'll never happen, but users are tricky and they might (it's not hard) anbd then nothing will work



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub


31029

2. I'm guessing that C3 and C4 at least are unlocked since you protect the sheet after changing C3 and then C4 is locked and can't change

3. Couple of things to consider. Rearranged code, removed some redundant, etc. Again, a lot of personal style



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

Set r = Target.Cells(1, 1) ' only check first cell

'personal style - I like to exit quick
If r.Address <> "$C$3" And r.Address <> "$C$4" Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub

Application.EnableEvents = False


Unprotect Password:="dlm"

Select Case r.Address
Case "$C$3"
'assume that C4 is Locked=False since this was before Unprotect
Range("C4") = "Please Select Origin..."
Select Case r.Value
Case "Air"
Range("A10:A19").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A56:A58").EntireRow.Hidden = True
Case "Ocean_Asia_to_EU"
Range("A8:A15").EntireRow.Hidden = True
Case "Shanghai to Genoa"
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
'combine? ame for rest
Range("C14:D15").ClearContents
Range("D17:D19").ClearContents
Range("C8:D9").ClearContents
Case "Overland"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C11:D11").ClearContents
Range("C12:D12").ClearContents
Range("C13:D13").ClearContents
Range("C14:D14").ClearContents
Range("C15:D15").ClearContents
Range("C17:D17").ClearContents
Range("C18:D18").ClearContents
Range("C19:D19").ClearContents
End Select

Case "$C$4"
Select Case r.Value
Case "Air"
If r.Value = ("Warsaw to New York") Then
Range("A23").EntireRow.Hidden = True
End If
Case "Ocean_Asia_to_EU"
If r.Value = ("Shanghai to Genoa") Then
Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = True
End If
Case "Shanghai to Genoa"
' more code
Case "Overland"
' more code
End Select
End Select

ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

[June7]

Firstly @ June7, Emojis can and often do occur outside of the "forum". One can be constructing a document in word and using sub paragraphs denoted by (a), (b), © etc and the emojis will appear. it is definitely a MS thing, and one they have not fixed.
Interesting, I can't recall ever experiencing this. I will have to explore.

I have seen forum generate emojis many times in code not between CODE tags and feel that is what happened in this case. Forum will also drop ending \ character from path strings. So again, post code between CODE tags.

[Scuba]

Thank you Paul,

I think your first attachment is incorrect for the subject in hand lol.

I will try out your code and let you know how I get on.

Once again, thank you for you help.

[Scuba]

Hi Paul,

OK, I have cleaned up what rows need to be hidden/unhidden etc and adjusted your code accordingly, see below code.

This unfortunately errors when you first change C3 to air


Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range
Set r = Target.Cells(1, 1)
If r.Address <> "$C$3" And r.Address <> "$C$4" Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub
Application.EnableEvents = False
Unprotect Password:="dlm"
Select Case r.Address
Case "$C$3"
'assume that C4 is Locked=False since this was before Unprotect - CORRECT!
Range("C4") = "Please Select Origin..."
Select Case r.Value
Case "Air"
Range("A10:A19").EntireRow.Hidden = True
Range("A39:A43").EntireRow.Hidden = True
Range("A56:A58").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Range("C9:D16").ClearContents
Case "Ocean_Asia_to_EU"
Range("A8:A15").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = False
Range("C9:D16").ClearContents
Case "Ocean_EU_to_US"
Range("A23:A25").EntireRow.Hidden = False
Range("A17:A19").EntireRow.Hidden = True
Range("A8:A9").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = False
Range("A14").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = False
Range("A11").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Range("A13:A15").EntireRow.Hidden = True
Range("C9:D16").ClearContents
Case "Overland"
Range("A8:A9").EntireRow.Hidden = True
Range("A7:A12").EntireRow.Hidden = True
Range("A15").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A18:A19").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A13:A15").EntireRow.Hidden = True
Range("C9:D16").ClearContents
End Select
Case "$C$4"
Select Case r.Value
Case "Air"
If r.Value = ("Warsaw to JFK") Then
Range("A5:A6").EntireRow.Hidden = True
Range("A8:A21").EntireRow.Hidden = True
Range("A24:A25").EntireRow.Hidden = True
End If
If r.Value = ("Warsaw to LAX") Then
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A8:A11").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A20").EntireRow.Hidden = False
End If
If r.Value = ("Malpensa to JFK") Then
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If
If r.Value = ("Heathrow to JFK") Then
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If
If r.Value = ("Heathrow to LAX") Then
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If
Case "Ocean_EU_to_US"
If r.Value = ("Genoa to New York") Then
Range("A23").EntireRow.Hidden = True
Range("A57").EntireRow.Hidden = True
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
End If
Case "Ocean_EU_to_US"
' more code
Case "Overland"
' more code
End Select
End Select
ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

It errors and points to an 'End IF' statement (see attachment)


Any ideas where I'm going wrong?

ps. I will complete the Ocean rows once I have got the air to work.

Regards

Richard

[Aussiebear]

You are missing a line



if r.value = ("Malpensa to LAX")

[Paul_Hossler]

I'd guess the marked line is missing

I really didn't test it since there was no dummy data to play with




End If

If .......... Then ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If

[Aflatoon]

Your "Ocean_EU_to_US" section doesn't make a lot of sense. You hide rows 23 and 57 then unhide them again since they are part of the next two ranges too.

[Scuba]

This is correct, they are wrong currently, please ignore them, they need to be changed, the actual rows that need to be hidden/unhidden, I'm not concerned with at this stage as I will clean them up once I have the logic working correctly.
:)

[Scuba]

Hi,

The code is showing this line, you can see from the screen shot in post 30?

[georgiboy]

Post 31 & 32 explain what is missing.

[Scuba]

Hi Paul,

When I insert this it fails again 'Compile error'

Can you show me in the code where it should go ad Ive copied what you wrote but it fails.

[georgiboy]

If you indent the code between the If & End If's, it will be easier to see what is missing:

If r.Value = ("Malpensa to JFK") Then
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If


'''''''''''''''''''''''''' Here
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If


If r.Value = ("Heathrow to JFK") Then
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
End If

As Aussie stated in post 31, you are missing something like:

if r.value = ("Malpensa to LAX")
Where is states: '''''''''''''''''''''''''' Here

[Scuba]

Yep I saw that I missed that off


if r.value = ("Malpensa to LAX")

Have added and will test.

[Scuba]

thank you, and yes I saw my school boy error and have correct it :)

[Scuba]

Hi Paul,

OK I'm trying a different approach here now because I cannot get this to work using both cell.

I would prefer to have this working from both cells but it just seems to ignore whatever I select in C4's drop down list.

My original idea was use the values from C3 to hide/unhide the majority of fields required for the mode selected and then when the user selects the actual lane from cell c4, I can further streamline the fields by removing and or adding back in the required fields.

For example I don't need row 8 to be visible when JFK is selected as the end destination but it is required when LAX is selected.

There are other optimisation (rows to hide/unhide) for other lanes in Cell C4 but again, if I can get one to work then I can repeat for the rest


So a different approach is to just work from Cell C4 only, this is not as future proof for me as I would like and I would prefer to get the above working but it just seems to be eluding me currently and I'm nearly 5 day in on this and behind schedule.

So another way to do this and hopefully simplify this, is to use the values from cell C4 only, as they are unique to each other so I can adjust my code to look only at C4 and then do its hidding/unhiding etc.
I have tried to adjust as per below bu t it fails so need your help/guidance again please :)

Ive insert the below under Air as acheck as these fieds are only needed from Ocean and so shouldn't be visible when selecting a Air route.


Range("A23:A25").EntireRow.Hidden = False


Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range
Set r = Target.Cells(1, 1)
If r.Address <> "$C$4" Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub
Application.EnableEvents = False
Unprotect Password:="dlm"
Select Case r.Value
Case "Warsaw to JFK"
Range("A5:A6").EntireRow.Hidden = True
Range("A8:A10").EntireRow.Hidden = True
Range("A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = False
Case "Warsaw to LAX"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A8:A11").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A20").EntireRow.Hidden = False
Case "Malpensa to JFK"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Case "Malpensa to LAX"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Case "Heathrow to JFK"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Case "Heathrow to LAX"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Case "Ocean_EU_to_US"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Overland"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
End Select
ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

[Paul_Hossler]

Be glad to, but could you make a small workbook with the entire macro(s) and attach it?

I'm not sure how the entire macro looks

A more wordy way (not always a good thing) is to test for both C3 and C4 combined




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, rC3 As Range, rC4 As Range

Set r = Target.Cells(1, 1) ' only check first cell
Set rC3 = Range("C3")
Set rC4 = Range("C4")


'personal style - I like to exit quick
If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub

Application.EnableEvents = False




Unprotect Password:="dlm"

Select Case rC3.Value & "#" & rC4.Value
Case "Air" & "#" & "Warsaw to JFK"
Range("A5:A6").EntireRow.Hidden = True
Range("A8:A10").EntireRow.Hidden = True
Range("A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = False


Case "Air" & "#" & "Warsaw to LAX"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A8:A11").EntireRow.Hidden = False
Range("A12").EntireRow.Hidden = True
Range("A17").EntireRow.Hidden = False
Range("A20").EntireRow.Hidden = False

'etc
'etc

Case Else
If Len(rC3.Value) = 0 Then
rC4.Value = "Select Destination"
ElseIf Len(rC4.Value) = 0 Then
rC3.Value = "Select Origin"
Else
MsgBox "Origin and/or Destinaation not programed for"
End If
End Select

ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

[Scuba]

sure but not sure it will work in a cut down version so please find attached workbook

regards

Richard

[georgiboy]

So a different approach is to just work from Cell C4 only, this is not as future proof for me as I would like and I would prefer to get the above working but it just seems to be eluding me currently and I'm nearly 5 day in on this and behind schedule.

I think this was suggested in post 25

[Scuba]

It was yes but as outlined in my last message the preferred method is to combine both, this future proofs me better when I need to make additions to the lanes and or modes.

The only reasons working from one works is because the names are unique in C4 but they may not be in the future hence why 2 is better than one.

[georgiboy]

Sorry I should have said post 25 does both C3 & C4, post 19 works from C4 only.

[Paul_Hossler]

Some things to think about

Not all C4 choices apply to some C3 selections so you might need to make a matrix




Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, rC3 As Range, rC4 As Range

Set r = Target.Cells(1, 1)
Set rC3 = Range("C3")
Set rC4 = Range("C4")

If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub

If Len(rC3.Value) = 0 Or Len(rC4.Value) = 0 Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
Unprotect Password:="dlm"

'show them all to start and then hide the ones based on C3 and/or C4
Rows("1:86").Hidden = False

Select Case rC3.Value
Case "Air"
'if you always need to hide rows for Air


Case "Ocean_EU_to_US"
'if you need to hide rows

Case "Ocean_Asia_to_EU"
'if you need to hide rows


Case "Overland"
'if you need to hide rows

End Select


'depending on C3, not all of these apply
Select Case rC4.Value
Case "Warsaw to JFK"
Range("A5:A6").EntireRow.Hidden = True
Range("A8:A10").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Case "Warsaw to LAX"
Range("A6").EntireRow.Hidden = True
Range("A12").EntireRow.Hidden = True
Case "Malpensa to JFK"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Case "Malpensa to LAX"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Case "Heathrow to JFK"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Case "Heathrow to LAX"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Case "Ocean_EU_to_US"
etc.
Case "Ocean_Asia_to_EU"
etc.
Case "Overland"
etc.
End Select

' ActiveSheet.Protect Password:="dlm" ' <<<<<<<<<<<<<<<<<<< for testing
Application.EnableEvents = True
Application.ScreenUpdating = true



End Sub

[Scuba]

Hi Paul,

This worked a treat, I can't thank you enough :)

I've added an extra line to make the Rate Calc sheet look cleaner until the lane has been selected, it basically stop/hides the 'False' being shown because the user hasn't selected their lane yet from C4 cell..


If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."

My only stumbling block now is I need to be able to hide an 'Active xControl check box' when a certain lane is selected , namely the below, it fails to hide or unhide the check boxes, any ideas?


Case "Ocean_EU_to_US" & "#" & "Genoa to New York" Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = True
ActiveSheet.CheckBox2.Visible = True
ActiveSheet.CheckBox3.Visible = True
ActiveSheet.CheckBox4.Visible = True
ActiveSheet.CheckBox5.Visible = True
ActiveSheet.CheckBox6.Visible = True

Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = False
ActiveSheet.CheckBox2.Visible = False
ActiveSheet.CheckBox3.Visible = False
ActiveSheet.CheckBox4.Visible = False
ActiveSheet.CheckBox5.Visible = False
ActiveSheet.CheckBox6.Visible = False

Full code is below


Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, rC3 As Range, rC4 As Range

Set r = Target.Cells(1, 1) 'only check first cell
Set rC3 = Range("C3")
Set rC4 = Range("C4")

If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If

Application.EnableEvents = False
Unprotect Password:="dlm"

Select Case rC3.Value & "#" & rC4.Value
Case "Air" & "#" & "Warsaw to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Warsaw to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Malpensa to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Malpensa to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Heathrow to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Heathrow to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Ocean_EU_to_US" & "#" & "Genoa to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = True
ActiveSheet.CheckBox2.Visible = True
ActiveSheet.CheckBox3.Visible = True
ActiveSheet.CheckBox4.Visible = True
ActiveSheet.CheckBox5.Visible = True
ActiveSheet.CheckBox6.Visible = True

Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = False
ActiveSheet.CheckBox2.Visible = False
ActiveSheet.CheckBox3.Visible = False
ActiveSheet.CheckBox4.Visible = False
ActiveSheet.CheckBox5.Visible = False
ActiveSheet.CheckBox6.Visible = False

Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_EU_to_US" & "#" & "Gdynia to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_EU_to_US" & "#" & "Hamburg to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to FXT/SOU"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to FXT/SOU"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False

Case "Overland" & "#" & "PL to UK"
Range("A8:A13").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A15:A21").EntireRow.Hidden = True
Range("A38:A74").EntireRow.Hidden = True
Range("A24:A25").EntireRow.Hidden = True

Case "Overland" & "#" & "UK to PL"
Range("A8:A13").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A15:A21").EntireRow.Hidden = True
Range("A38:A74").EntireRow.Hidden = True
Range("A24:A25").EntireRow.Hidden = True
End Select

ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

[Paul_Hossler]

Option Explicit


Sub cboxHide()


ActiveSheet.Shapes("Checkbox1").Visible = False


End Sub


Sub cboxShow()


ActiveSheet.Shapes("CheckBox1").Visible = True


End Sub




But I usually rename them so that the code is more readable and I'm less likely to make errors

[Scuba]

Hi Paul,

That makes sense to rename them, I've renamed them now for ease of reference.

Your code is complete but I need to insert it into my code without breaking the rest/flow of the code, I have inserted as per below but nothing happened when I select 'Genoa to New York' or 'Genoa to Los Angeles'.

Basically the check boxes are only needed for 'Genoa to New York' and then will be hidden for the rest, if I can get the below two to work correctly, I can then replicate for the other lanes.


Case "Ocean_EU_to_US" & "#" & "Genoa to New York" Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.Shapes("RFR20").Visible = True
ActiveSheet.Shapes("RFR40").Visible = True
ActiveSheet.Shapes("RFR40HQ").Visible = True
ActiveSheet.Shapes("Priority20").Visible = True
ActiveSheet.Shapes("Priority40").Visible = True
ActiveSheet.Shapes("Priority40HQ").Visible = True

Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.Shapes("RFR20").Visible = False
ActiveSheet.Shapes("RFR40").Visible = False
ActiveSheet.Shapes("RFR40HQ").Visible = False
ActiveSheet.Shapes("Priority20").Visible = False
ActiveSheet.Shapes("Priority40").Visible = False
ActiveSheet.Shapes("Priority40HQ").Visible = False

[Paul_Hossler]

Without a sample workbook with the latest macros, it's hard to say

Here's a snippet that "looks OK" to me

I think it's easier to 'reset' to a visible configuration each time and then apply the specifics. So I like to unhide all rows and hide the checkboxes and the depending on the choices, hide N/A rows and show the checkboxes

One the checkboxes are made visible, they stay visible until they get hidden and by always starting with a known configuration, I find it easier to manage



Application.EnableEvents = False
Unprotect Password:="dlm"

Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

Rows("1:86").Hidden = False


Select Case rC3.Value & "#" & rC4.Value
Case "Air" & "#" & "Warsaw to New York"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Warsaw to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Malpensa to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

[Scuba]

Hi Paul,

I have uploaded the file for reference if that is easier, let me know please?

Once again, thank you for all your help so far :)

Regards

Richard

[Scuba]

Hi Paul,

I shared the workbook in post 52 as requested, I have reviewed your response in post 51 and I think I understand what you are saying, however the hiding and unhiding of rows and or check boxes is all dependent on what is selected by rC3 & rC4, mainly rC4, hence why I was trying to use each Case under Rc4 as the determining factor, for example

if rC4 = 'Warsaw to New York' then


Shapes("RFR20").Visible = True
Shapes("RFR40").Visible = True
Shapes("RFR40HQ").Visible = True
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

but if rC4='Warsaw to Los Angeles' then


Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True


Basically RFR means Reefer movements & Priority means Priority movements, we only move Reefer containers from 'Warsaw to New York', hence why they are required to be visible when this is selected in rC4 but hidden when rC4 is 'Warsaw to Los Angeles'

This is slightly different to what I posted in 48 but was the true end game as once I got the logic or code to work, I could then change it to what I needed hidden/unhidden etc per rC4 selection.



Regards


Richard

[Paul_Hossler]

Many ways to do it, but I think it's easier to follow to start off by hiding the check boxes and showing all the rows.

Then depending on choices in C3 and C4 to hide the inapplicable rows depending on combanation ofC3+C4and showing the appropriate check boxes depending on C4

That way you always start with a known status



Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, rC3 As Range, rC4 As Range

Set r = Target.Cells(1, 1) 'only check first cell
Set rC3 = Range("C3")
Set rC4 = Range("C4")

If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub


If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If

Application.EnableEvents = False
Unprotect Password:="dlm"


Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

Rows("1:86").Hidden = False

Select Case rC3.Value & "#" & rC4.Value
Case "Air" & "#" & "Warsaw to New York"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Warsaw to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Malpensa to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Malpensa to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Heathrow to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Air" & "#" & "Heathrow to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True

Case "Ocean_EU_to_US" & "#" & "Genoa to New York"
Range("A54:A57").EntireRow.Hidden = True

Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
Range("A54:A57").EntireRow.Hidden = True

Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles"

Case "Ocean_EU_to_US" & "#" & "Gdynia to New York"

Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles"

Case "Ocean_EU_to_US" & "#" & "Hamburg to New York"

Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles"

Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York"

Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles"

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa"

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa"

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk"

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk"

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to FXT/SOU"

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to FXT/SOU"

Case "Overland" & "#" & "PL to UK", "Overland" & "#" & "UK to PL"
Range("A8:A13").EntireRow.Hidden = True
Range("A15:A21").EntireRow.Hidden = True
Range("A38:A74").EntireRow.Hidden = True
Range("A24:A25").EntireRow.Hidden = True
End Select

Select Case rC4.Value
Case "Warsaw to New York"
Shapes("RFR20").Visible = True
Shapes("RFR40").Visible = True
Shapes("RFR40HQ").Visible = True
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True


Case "Warsaw to Los Angeles"
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True
End Select

ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

[Scuba]

Hi Paul,

I will most certainly try this approach yes but not sure how this will work because its not a simple on or off before the rC4 lanes are selected.

Yes, the check boxes are required to be hidden for all 'Air' or 'Overland' lanes but then only 3 of them are required to be visible for all Ocean lanes and then for a specific lane 'Genoa to New York', all 6 are required.

If we are turning them on or off in the first instance before rC4 is selected how will that work?

Sorry for questioning you, I'm just simply trying to get my head around all this and of course learn as I go along :)
regards

Scrap the above Paul, this works a treat, perfect! :)
I can't thank you enough and thank you or being patience with me, this is all new to me but I have learnt a lot all ready and will apply this to other projects in the future, once again many thanks Paul:clap::bow:

Regards

Richard

[Scuba]

Hi Paul,

You may be able to help me with the below code, it fails when run.

Basically I'm hiding certain rows when an active check box is true and the value in C4 = Warsaw to New York

This is additional to what you have helped me with previously.


Sub BreakDownofCHarges()'Declare variables
Dim xCheckbox As Boolean
Dim cellC4 As String


'Get the value of the active x checkbox
xCheckbox = Sheets("Rate Calc").CheckBoxes("RevealBreakDown").Value


'Get the value of cell C4
cellC4 = Sheets("Rate Calc").Cells(4, 2).Value


'If the active x checkbox is checked and cell C4 is equal to "Warsaw to New York", then hide rows 38 to 43 and rows 52 to 58
If xCheckbox And cellC4 = "Warsaw to New York" Then
Sheets("Rate Calc").Rows(38:43).EntireRow.Hidden = True
Sheets("Rate Calc").Rows(52:58).EntireRow.Hidden = True
End If


End Sub

[Paul_Hossler]

Option Explicit


Sub BreakDownofCHarges()


'If the active x checkbox is checked and cell C4 is equal to "Warsaw to New York", then hide rows 38 to 43 and rows 52 to 58
With Sheets("Rate Calc")
If .CheckBoxes("RevealBreakDown") And .Cells(4, 2).Value = "Warsaw to New York" Then
.Rows("38:43").Hidden = True ' .Rows(..) with a more than one requires a string, don't know why ... that's just MS
.Rows("52:58").Hidden = True ' Also .EntireRow is assumed
End If
End With


End Sub





Another way



Sub BreakDownofCharges_1()


'If the active x checkbox is checked and cell C4 is equal to "Warsaw to New York", then hide rows 38 to 43 and rows 52 to 58
With Sheets("Rate Calc")
If .CheckBoxes("RevealBreakDown") And .Cells(4, 2).Value = "Warsaw to New York" Then
.Rows(38).Resize(6).Hidden = True
.Rows(52).Resize(7).Hidden = True
End If
End With


End Sub

[Scuba]

Worked a Dream Paul :) :bow:

Thank you

[Scuba]

Hi Paul,

I realised after getting this to work perfectly with your expert help that I needed to add another clause or conditional row, so depending on what is selected in C5, different rows are hidden or unhidden.

Basically if C5 = Factory Load then line 17 is visible but if its CFS Loading then line 17 is hidden and line 19 is visible, this is because we have different collection locations depending on Factory vs CFS loading, it changes slightly for the other different modes, some have it some don't, I've adjusted the code so the correct rows are showing hidden or unhidden.

So building on what you had shown me already I adjusted the code accordingly but something is missing/not working on the below code as now when I change C4, nothing happens, especially if I am swapping between different modes/lanes (i.e. changing C3 and or C4), I then have to unhide all lines, manually select cell C5 and then it works, sort off, I'm close, just can't fathom how to push it over the line so to speak?

I've attached latest workbook for reference.


Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, rC3 As Range, rC4 As Range, rC5 As Range

Set r = Target.Cells(1, 1) 'only check first cell
Set rC3 = Range("C3")
Set rC4 = Range("C4")
Set rC5 = Range("C5")

If r.Address <> rC3.Address And r.Address <> rC4.Address And r.Address <> rC5.Address Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub


If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If

Application.EnableEvents = False
Unprotect Password:="dlm"

Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

Rows("5:25").Hidden = True
Rows("79:81").Hidden = True

Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value

Case "Air"
Range("$C$5").Value = "Factory Load"

Case "Air" & "#" & "Warsaw to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5").EntireRow.Hidden = False
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:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Warsaw to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5").EntireRow.Hidden = False
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
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Malpensa to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A12").EntireRow.Hidden = False
Range("A13:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Malpensa to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Heathrow to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Heathrow to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff


Case "Ocean_EU_to_US"
Range("$C$5").Value = "Factory Load"

Case "Ocean_EU_to_US" & "#" & "Genoa to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A10").EntireRow.Hidden = True
Range("A11").EntireRow.Hidden = False
Range("A20").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A26:A32").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A10").EntireRow.Hidden = True
Range("A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A26:A32").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "CFS Loading"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A19").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles" & "#" & "CFS Loading"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Hamburg to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff


Case "Ocean_EU_to_US" & "#" & "Hamburg to New York" & "#" & "CFS Loading"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff




Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles" & "#" & "CFS Loading"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff


Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_Asia_to_EU"
Range("$C$5").Value = "Factory Load"

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_Asia_to_EU" & "#" & "Shanghai to SOU/FXT" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A20").EntireRow.Hidden = True
Range("A21:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Ocean_Asia_to_EU" & "#" & "Xiamen to SOU/FXT" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A20").EntireRow.Hidden = True
Range("A21:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Overland"
Range("$C$5").Value = "Factory Load"

Case "Overland" & "#" & "PL to UK" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8:A21").EntireRow.Hidden = True
Range("A22:A23").EntireRow.Hidden = False
Range("A24:A25").EntireRow.Hidden = True
Range("A26").EntireRow.Hidden = False
Range("A27:A32").EntireRow.Hidden = False
Range("A38:A74").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Overland" & "#" & "UK to PL" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A13").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A15:A21").EntireRow.Hidden = True
Range("A22:A23").EntireRow.Hidden = False
Range("A24:A25").EntireRow.Hidden = True
Range("A26").EntireRow.Hidden = False
Range("A27:A32").EntireRow.Hidden = False
Range("A38:A74").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

End Select

Select Case rC4.Value
Case "Genoa to New York"
Shapes("RFR20").Visible = True
Shapes("RFR40").Visible = True
Shapes("RFR40HQ").Visible = True
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True


Case "Genoa/La Spezia to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Gdynia to New York"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Gdynia to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Hamburg to New York"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Hamburg to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "FXT/SOU to New York"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "FXT/SOU to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Shanghai to Genoa"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Xiamen to Genoa"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Shanghai to Gdynia/Gdansk"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Xiamen to Gdynia/Gdansk"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Shanghai to SOU/FXT"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Xiamen to SOU/FXT"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "UK to PL"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

Case "PL to UK"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

End Select

ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub

[Paul_Hossler]

Hard to tell since you've modified what I suggested, but a quick look seems to be that in Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value

the (rC3.Value & "#" & rC4.Value & "#" & rC5.Value) will never equal just "Air" so the Range("$C$5").Value = "Factory Load") will never execute

Try adding the If outside of the Case Select





If rC3.Value = "Air" Then rC5.Value = "Factory Load"

Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value
'
'Case "Air"
' Range("$C$5").Value = "Factory Load"

[Scuba]

Ok I've tried that, it seems to work fine so long as C5 = 'Factory Load', the moment C5 is change to 'CFS Loading', that's when it falls down.

Basically I only need the change C5 to 'CFS Loading' when selecting either Gdynia or Hamburg to New Your or Los Angeles, the rest don't need it, I didn't know how to just have them selectable so was trying to force C5 to 'Factory Load' for the rest, which is why they work but then fails when either Gdynia or Hamburg is selected.

If it helps below is what Im looking at, think on them as conditional drop downs

All the other lanes can be set same as the first one ie C5 is Factory Load




C3 =
Ocean_EU_to_US


C4 =
Genoa to New York


C5 =
Factory Load






C3 =
Ocean_EU_to_US


C4 =
Genoa/La Spezia to Los Angeles


C5 =
Factory Load






C3 =
Ocean_EU_to_US


C4 =
Gdynia to New York


C5 =
Factory Load or CFS Loading






C3 =
Ocean_EU_to_US


C4 =
Gdynia to Los Angeles


C5 =
Factory Load or CFS Loading






C3 =
Ocean_EU_to_US


C4 =
Hamburg to New York


C5 =
Factory Load or CFS Loading






C3 =
Ocean_EU_to_US


C4 =
Hamburg to Los Angeles


C5 =
Factory Load or CFS Loading






C3 =
Ocean_EU_to_US


C4 =
FXT/SOU to New York


C5 =
Factory Load






C3 =
Ocean_EU_to_US


C4 =
FXT/SOU to Los Angeles


C5 =
Factory Load

[Paul_Hossler]

what if c3 = "Air?

[Scuba]

Hi Paul,

If Air, I have forced it to read 'Factory Load"


If rC3.Value = "Air" Then rC5.Value = "Factory Load"

Basically I only need the change C5 to 'CFS Loading' when selecting either Gdynia or Hamburg to New Your or Los Angeles, the rest don't need it.

I don't know how to just have them selectable so was trying to force C5 to 'Factory Load' for the rest, which is why they work but then fails when either Gdynia or Hamburg is selected.



If rC3.Value = "Air" Then rC5.Value = "Factory Load"
Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value

Case "Air" & "#" & "Warsaw to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5").EntireRow.Hidden = False
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:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Warsaw to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5").EntireRow.Hidden = False
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
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Malpensa to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A12").EntireRow.Hidden = False
Range("A13:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Malpensa to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Heathrow to New York" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

Case "Air" & "#" & "Heathrow to Los Angeles" & "#" & "Factory Load"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff

[Paul_Hossler]

Your logic map is a little confusing for me, but I think what you're asking



If rC3.Value = "Air" Then
rC5.Value = "Factory Load"

else if rC4.Value = "Gdynia to New York" then
rC5.Value = "CFS Loading"
else if rC4.Value = "Gdynia to Los Angeles" then
rC5.Value = "CFS Loading"


else if rC4.Value = "Hamburg to New York" then
rC5.Value = "CFS Loading"
else if rC4.Value = "Hamburg to Los Angeles" then
rC5.Value = "CFS Loading" End if



Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value

[Scuba]

HI Paul,

Sorry if my message was little unclear, I was trying to do the opposite lol

OK, let try try to explain another way, hopefully this makes more sense to you.


If the users selects 'Ocean_EU_to_US' in rC3 and any of the below in rC4, then rC5 is visible for them to select either 'Factory Load' or 'CFS Loading'

Genoa to New York
Genoa/La Spezia to Los Angeles
Gdynia to New York
Gdynia to Los Angeles
Hamburg to New York
Hamburg to Los Angeles


If the user selects any of the below in rC3, then rC5 is forced to 'Factory Load' and the row is hidden, as its not a requirement for the below lanes.

Air
Ocean_Asia_to_EU
Overland

The only reason I am trying to force 'Factory Load' to the above is because I didn't know how else to adjust your code to work :dunno, if there is a better way I'm all ears :yes

Regards

Richard

[Paul_Hossler]

OK, I'm thinking that a more brute force approach might be better and easier to follow

Try the attached and the macro is longer but IMHO easier to follow

Be sure to look at the '<<<<<<<<<<<<<<<< comments since I modified some lines temporarily

[Scuba]

Hi Paul,

This looks great but I'm struggling to see where the change is for rC5 cell?

If the users selects 'Ocean_EU_to_US' in rC3 and any of the below in rC4, then rC5 is visible for them to select either 'Factory Load' or 'CFS Loading'

Genoa to New York
Genoa/La Spezia to Los Angeles
Gdynia to New York
Gdynia to Los Angeles
Hamburg to New York
Hamburg to Los Angeles

Below example of one of the lanes where different rows are hidden/unhidden depending on what the user selects in rC4 (see previous post #65) (I didn't show all, the rest are in the attachment on post #65)


Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "Factory Load" Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True

Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "CFS Loading"
Range("C8:C10").Value = ""
Range("C11:C17").Value = ""
Range("C19:C21").Value = ""
Range("D23:D25").Value = ""
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A19").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False

Range("A33:A76").EntireRow.Hidden = True


The below code is perfect for Air, Overland & Ocean_Asia_to_EU


Case "Air" ' -------------------------------------------------------------------------------------------- Mode = Air rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True

However for Ocean_EU_to_US we will have a mixture of


rLoading.Value = "Factory Load"
rLoading.Value = "CFS Loading"

for the lanes :

Genoa to New York
Genoa/La Spezia to Los Angeles
Gdynia to New York
Gdynia to Los Angeles
Hamburg to New York
Hamburg to Los Angeles


Do have to change the code like the below:


rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True

Case "Ocean_EU_to_US" ' ------------------------------------------------------------ Mode = Ocean EU to US
rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True
Select Case rLane.Value

Case "Gdynia To New York"
Range("C8@C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("FBC").Value = xlOff

rLoading.Value = "CFS Loading"
rLoading.EntireRow.Hidden = True
Select Case rLane.Value
Case "Gdynia To New York"
Range("C8@C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A19").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
ActiveSheet.CheckBoxes("FBC").Value = xlOff

Oo do I bunch all '"CFS Loading"' lanes (rLane) under ones the heading "CFS Loading" and then repeat for all "Factory Loads" lanes?


I will review again in case it's me and my lack of understanding, highly possible lol

Once again, your help is highly appreciated sir.

[Scuba]

I tried adding in the CFS Loading lanes but kept getting an error 'Select Case without End Select', which has thrown me as I have end Select at the end of each.

I'm mindful I'm reply to post 67, which you won't have answered/seen yet but I could edit my message to add this bit to it for some reason.


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 Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub


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


If Target.Address = rMode.Address Then rLane.Value = "Please Select Origin..."

Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

Rows("5:25").Hidden = True
Rows("79:81").Hidden = True
Rows("87:" & Rows.Count).Hidden = True

'step 2 - based on Mode, select Lane and configure
Select Case rMode.Value
Case "Air" ' -------------------------------------------------------------------------------------------- Mode = Air
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 = False
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: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 = False
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
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True


Case "Malpensa to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A12").EntireRow.Hidden = False
Range("A13:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True


Case "Malpensa to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True


Case "Heathrow to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True


Case "Heathrow to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
'ActiveSheet.CheckBoxes("FBC").Value = xlOff
End Select

Case "Ocean_EU_to_US" ' ------------------------------------------------------------ Mode = Ocean EU to US

Select Case rLane.Value
Case "Genoa to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A10").EntireRow.Hidden = True
Range("A11").EntireRow.Hidden = False
Range("A20").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A26:A32").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Genoa/La Spezia to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A10").EntireRow.Hidden = True
Range("A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A26:A32").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Gdynia to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A11").EntireRow.Hidden = False
Range("A12:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = False
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Gdynia to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Hamburg to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Hamburg to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "FXT/SOU to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "FXT/SOU to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End Select
Case "Ocean_EU_to_US" ' ------------------------------------------------------------ Mode = Ocean EU to US
rLoading.Value = "CFS Loading" '--------------------------CFS Loading lanes only
rLoading.EntireRow.Hidden = True
Select Case rLane.Value
Case "Gdynia to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5").EntireRow.Hidden = False
Range("A6").EntireRow.Hidden = True
Range("A7:A11").EntireRow.Hidden = False
Range("A12:A18").EntireRow.Hidden = True
Range("A19").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Gdynia to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A19").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Hamburg to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A19").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Hamburg to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A19").EntireRow.Hidden = True
Range("A20").EntireRow.Hidden = False
Range("A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "FXT/SOU to New York"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "FXT/SOU to Los Angeles"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A9:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End Select

Case "Ocean_Asia_to_EU" ' ---------------------------------------------------------------- Mode = Ocean Asia to EU
rLoading.Value = "Factory Load" '-------------------------------------Factory Load Lanes only
rLoading.EntireRow.Hidden = True

Select Case rLane.Value
Case "Shanghai to Genoa"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True


Case "Xiamen to Genoa"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Shanghai to Gdynia/Gdansk"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Xiamen to Gdynia/Gdansk"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Shanghai to SOU/FXT"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "Xiamen to SOU/FXT"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A7:A8").EntireRow.Hidden = False
Range("A9:A21").EntireRow.Hidden = True
Range("A22:A25").EntireRow.Hidden = False
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End Select

Case "Overland" ' ----------------------------------------------------------------------------------- Mode = Overland
rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True

Select Case rLane.Value
Case "PL to UK"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5:A6").EntireRow.Hidden = True
Range("A7").EntireRow.Hidden = False
Range("A8:A21").EntireRow.Hidden = True
Range("A22:A23").EntireRow.Hidden = False
Range("A24:A25").EntireRow.Hidden = True
Range("A26").EntireRow.Hidden = False
Range("A27:A32").EntireRow.Hidden = False
Range("A38:A74").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True

Case "UK to PL"
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A5:A6").EntireRow.Hidden = True
Range("A7:A13").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A15:A21").EntireRow.Hidden = True
Range("A22:A23").EntireRow.Hidden = False
Range("A24:A25").EntireRow.Hidden = True
Range("A26").EntireRow.Hidden = False
Range("A27:A32").EntireRow.Hidden = False
Range("A38:A74").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End Select

'step 3 - based on Lane show check boxes
Select Case rLane.Value
Case "Genoa to New York"
Shapes("RFR20").Visible = True
Shapes("RFR40").Visible = True
Shapes("RFR40HQ").Visible = True
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Genoa/La Spezia to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Gdynia to New York"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Gdynia to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Hamburg to New York"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Hamburg to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "FXT/SOU to New York"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "FXT/SOU to Los Angeles"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Shanghai to Genoa"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Xiamen to Genoa"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Shanghai to Gdynia/Gdansk"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Xiamen to Gdynia/Gdansk"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Shanghai to SOU/FXT"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "Xiamen to SOU/FXT"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = True
Shapes("Priority40").Visible = True
Shapes("Priority40HQ").Visible = True

Case "UK to PL"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False

Case "PL to UK"
Shapes("RFR20").Visible = False
Shapes("RFR40").Visible = False
Shapes("RFR40HQ").Visible = False
Shapes("Priority20").Visible = False
Shapes("Priority40").Visible = False
Shapes("Priority40HQ").Visible = False
End Select
' ActiveSheet.Protect Password:="dlm" <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Application.EnableEvents = True
End Sub

[Paul_Hossler]

1. The XLSM in #66 does not have SELECT error for me


2.


Rows("1:86").Hidden = False
Rows("87:" & Rows.Count).Hidden = True


unhides all rows in 1 to 86 including row 5

3.


Case "Air" ' -------------------------------------------------------------------------------------------- Mode = Air
rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True

and

Case "Ocean_Asia_to_EU" ' -------------------------------------------------------------------------------------------- Mode = Ocean Asia to US
rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True

and

Case "Overland" ' -------------------------------------------------------------------------------------------- Mode = Overland
rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True






sets C5 = "Factory Load" but hides the row (I think that was your logic)


4.

However for Ocean_EU_to_US we will have a mixture of

rLoading.Value = "Factory Load"
rLoading.Value = "CFS Loading"
for the lanes :

Genoa to New York
Genoa/La Spezia to Los Angeles
Gdynia to New York
Gdynia to Los Angeles
Hamburg to New York
Hamburg to Los Angeles


Row 5 is left visible from the code in para 2 above and your data validation list has "Factory Load" and CFS Loading" as choices

[Scuba]

Well that's weird then, I've attached the workbook again so you can see what I'm getting/seeing.

Everything works OK with the exception of the rLoading, the incorrect rows are not being hidden/unhidden when rloading matches 'CFS Loading', nothing happens when changing rLoading between 'CFS Loading' & 'Factory Load'

If I select 'Ocean_EU_to_US for rMode and 'Gdynia to New York' for rLane, then when rLoading = 'CFS Loading then Row C19 should be hidden and row 20 should be visible

Same for Gdynia to Los Angeles
Hamburg to New York
Hamburg to Los Angeles

This is because there are different collection points required depending on CFS or Factory selection in rLoading

[Paul_Hossler]

Try this

I went back to my prefered way to show all rows and then hide the ones I don't want based on Mode, Lane and Loading since otherwise the show/hide/show, ... gets complicated and I can't keep the logic straight

That way therre's no need for lots of "Range("A22:A25").EntireRow.Hidden = False" through out since all rows show to start with and only the N/A ones based on Mode, Lane, and Loading need to be hidden

I think the logic is complicated enough so I'm trying to keep it simple

I marked adds / changes with <<<<<<<<<<<<<<<<<



If I select 'Ocean_EU_to_US for rMode and 'Gdynia to New York' for rLane, then when rLoading = 'CFS Loading then Row C19 should be hidden and row 20 should be visible

There's no logic for Factory Load in the macro. Is there supposed to be?

[Scuba]

Hi Paul,

OK so when I select 'Gdynia to New York' then nothing changes when is select either 'CFS Loading' or 'Factory Loading' in rLoading

Maybe I'm not explaining myself clearly here, which if so, I apologise for and thank you for your patience with me.

I going to try and spell it out for you here.

rloading is defaulted to 'Factory Loading' for all lanes to begin with and the cell is hidden for all rLanes except those shown below

rLoading row/cell is only visible for the below mention rMode lanes as its only needs to be changed for those lanes,


if rLane = 'Gdynia to New York' & 'rLoading' = 'Factory Loading' then row 19 is visible & row 20 is hidden.
if rLane = 'Gdynia to New York' & 'rLoading' = 'CFS Loading' then row 19 is hidden & row 20 is visible

This logic is needed for the below lanes as well:


rLane = Gdynia to New York
rLane = Gdynia to Los Angeles
rLane = Hamburg to New York
rLane = Hamburg to Los Angeles

The user can select between 'CFS Loading' or 'Factory Loading' when the above lanes are selected, everything else can remain as is.

I don't need to change anything else as it all works correct, I just need to get the correct line 19 & 20 to hide/unhide based on rLoading selection for the above lanes only.

Hopefully the above is much clearer direction/vision of what I am trying to attain here :)

UPDATE!

I fixed the code to the below and all appears to be working correctly now :)

I can't thank you enough Paul for your help, I've learnt so much in a short space of time, nothing like learning on the job lol

Once again, big thank you, your a star *:bow::thumb:yay


Case "Gdynia to New York" If rLoading.Value = "CFS Loading" Then ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Range("C19").EntireRow.Hidden = True
Range("C20").EntireRow.Hidden = False
Else
Range("C19").EntireRow.Hidden = False
Range("C20").EntireRow.Hidden = True
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A12:A18").EntireRow.Hidden = True
Range("A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End If

Case "Gdynia to Los Angeles"
If rLoading.Value = "CFS Loading" Then ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Range("C19").EntireRow.Hidden = True
Range("C20").EntireRow.Hidden = False
Else
Range("C19").EntireRow.Hidden = False
Range("C20").EntireRow.Hidden = True
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A12:A18").EntireRow.Hidden = True
Range("A20:A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End If

Case "Hamburg to New York"
If rLoading.Value = "CFS Loading" Then ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Range("C19").EntireRow.Hidden = True
Range("C20").EntireRow.Hidden = False
Else
Range("C19").EntireRow.Hidden = False
Range("C20").EntireRow.Hidden = True
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A12:A18").EntireRow.Hidden = True
Range("A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End If

Case "Hamburg to Los Angeles"
If rLoading.Value = "CFS Loading" Then ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Range("C19").EntireRow.Hidden = True
Range("C20").EntireRow.Hidden = False
Else
Range("C19").EntireRow.Hidden = False
Range("C20").EntireRow.Hidden = True
Range("C8:C10").Value = vbNullString
Range("C11:C17").Value = vbNullString
Range("C19:C21").Value = vbNullString
Range("D23:D25").Value = vbNullString
Range("A6").EntireRow.Hidden = True
Range("A12:A18").EntireRow.Hidden = True
Range("A21").EntireRow.Hidden = True
Range("A33:A76").EntireRow.Hidden = True
If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
End If

Regards

Richard.