CODEWITHSUNDEEP
vbaexcel
Alien_Explorer
Alien_Explorer

Reputation: 867

If Else VBA Issue

My problem is that my code does not pick up the "Medium" variable

If Range("B24").Value = "Medium" Then
Sheets("Sheet2").Visible = True

Can you please advise what's wrong with it ("Standard" should uncover rows, "Medium" should keep them hidden but unhide the Sheet2, High again keeps the rows hidden but unhides Sheet2, if (B24) is empty it should keep rows and Sheet2 hidden:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B24").Value = "Standard" Then
    Sheets("Sheet2").Visible = False
End If
    If Range("B24").Value = "Medium" Then
    Sheets("Sheet2").Visible = True
End If
    If Range("B24").Value = "High" Then
    Sheets("Sheet2").Visible = True
End If
    Else
    Sheets("Sheet2").Visible = False
End If
    If Range("B24").Value = "Standard" Then
    Rows("29:47").EntireRow.Hidden = False
    Else
    Rows("29:47").EntireRow.Hidden = True
    End If
End Sub

Upvotes: 2

Views: 87

Answers (1)

Vityata
Vityata

Reputation: 43565

You are obviously missing an If, and your code should not be working. This is how it looks like, when it is formatted:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("B24").Value = "Standard" Then
        Sheets("Sheet2").Visible = False
    End If

    If Range("B24").Value = "Medium" Then
        Sheets("Sheet2").Visible = True
    End If

    If Range("B24").Value = "High" Then
        Sheets("Sheet2").Visible = True
    End If
'Is this Else an IF?
Else
    Sheets("Sheet2").Visible = False
End If

If Range("B24").Value = "Standard" Then
    Rows("29:47").EntireRow.Hidden = False
Else
    Rows("29:47").EntireRow.Hidden = True
End If

End Sub

If you omit the If-Else, the code would look a bit better with a Select-Case statement:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Range("B24")

    Case "Standard"
        Sheets("Sheet2").Visible = False
        Rows("29:47").EntireRow.Hidden = False

    Case "Medium"
        Sheets("Sheet2").Visible = True
        Rows("29:47").EntireRow.Hidden = True

    Case "High"
        Sheets("Sheet2").Visible = True
        Rows("29:47").EntireRow.Hidden = True

    Case Else
        Sheets("Sheet2").Visible = False
        Rows("29:47").EntireRow.Hidden = True

    End Select

End Sub

You can probably go one step further, by writing Rows("29:47").EntireRow.Hidden = True before the select case and remove this line from cases, thus following the Do-Not-Repeat-Yourself principle. But in this case it is probably not needed.

If you want to add a second range, something like this is possible:

Select Case True    
    Case Range("B24") = "Standard"      
    Case Range("B26") = "Medium"        
End Select

However, it would evaluate just once, thus if both are true, B26 is not going to happen.

Upvotes: 4

Related Questions