Just updated post 19
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"
thank you
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.
Here is another way of looking at it, seperating the two parts:
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.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
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:
You hide row 23, but i don't see anywhere in the code that row 23 is made visible again.Range("A23").EntireRow.Hidden = True
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
Capture.JPGOption Explicit Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address End Sub
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
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
It errors and points to an 'End IF' statement (see attachment)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
Any ideas where I'm going wrong?
ps. I will complete the Ocean rows once I have got the air to work.
Regards
Richard
You are missing a line
if r.value = ("Malpensa to LAX")
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
Be as you wish to seem
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.
Hi,
The code is showing this line, you can see from the screen shot in post 30?
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.
If you indent the code between the If & End If's, it will be easier to see what is missing:
As Aussie stated in post 31, you are missing something like: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
Where is states: '''''''''''''''''''''''''' Hereif r.value = ("Malpensa to LAX")
Yep I saw that I missed that off
Have added and will test.if r.value = ("Malpensa to LAX")
thank you, and yes I saw my school boy error and have correct it