Results 41 to 60 of 72

Thread: IF statment nested within VBA Code

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location

    IF statment nested within VBA Code

    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?
    Last edited by Aussiebear; 09-05-2023 at 03:22 AM. Reason: Added code tags to supplied code.

Posting Permissions

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