IF statment nested within VBA Code

**georgiboy** · 09-06-2023, 06:08 AM

Just updated post 19

**Scuba** · 09-06-2023, 06:14 AM

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** · 09-06-2023, 06:16 AM

thank you

**georgiboy** · 09-06-2023, 06:17 AM

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** · 09-06-2023, 07:03 AM

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** · 09-06-2023, 07:25 AM

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** · 09-06-2023, 07:57 AM

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

Capture.JPG

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** · 09-07-2023, 01:25 AM

Originally Posted by Aussiebear

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** · 09-07-2023, 01:48 AM

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** · 09-07-2023, 06:20 AM

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** · 09-07-2023, 07:52 AM

You are missing a line

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

**Paul_Hossler** · 09-07-2023, 07:54 AM

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** · 09-07-2023, 10:04 AM

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** · 09-08-2023, 03:38 AM

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** · 09-08-2023, 03:39 AM

Hi,

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

**georgiboy** · 09-08-2023, 03:44 AM

Post 31 & 32 explain what is missing.

**Scuba** · 09-08-2023, 03:44 AM

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** · 09-08-2023, 03:54 AM

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** · 09-08-2023, 03:57 AM

Yep I saw that I missed that off

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

Have added and will test.

**Scuba** · 09-08-2023, 04:09 AM

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

Thread: IF statment nested within VBA Code

Thread Tools

Display

Posting Permissions