Results 1 to 20 of 72

Thread: IF statment nested within VBA Code

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #10
    Administrator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,308
    Location
    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
    Last edited by georgiboy; 09-06-2023 at 07:22 AM.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2408, Build 17928.20080

Posting Permissions

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