VBA code tweak needed

[londa_vba]

Hello
I have the following code that pairs to a drop down on the sheet. The code works when "plate" is selected and when the dropdown is blank but not when "tubes".

Private Sub Worksheet_Change(ByVal Target As Range)

If [Amp_performed] = "Plate" Then
Sheets("GF Amp Plate Layout").Visible = True
Range("a8:a33,a39:a64,a70:a95,a101:a126").Font.Color = vbBlack
Else
Sheets("GF Amp Plate Layout").Visible = False
Range("a8:a33,a39:a64,a70:a95,a101:a126").Font.Color = vbWhite
End If

If [Amp_performed] = "Tubes" Then
Sheets("GF Amp Plate Layout").Visible = False
Range("a8:a33,a39:a64,a70:a95,a101:a126").Font.Color = vbWhite
Else
Sheets("GF Amp Plate Layout").Visible = True
Range("a8:a33,a39:a64,a70:a95,a101:a126").Font.Color = vbBlack
End If

If [Amp_performed] = "" Then
Sheets("GF Amp Plate Layout").Visible = False
Range("a8:a33,a39:a64,a70:a95,a101:a126").Font.Color = vbWhite
Else
Sheets("GF Amp Plate Layout").Visible = True
Range("a8:a33,a39:a64,a70:a95,a101:a126").Font.Color = vbBlack
End If
End Sub

 

[Joe4]

I think the issue is that you have multiple IF statements with ELSE clauses.
So in all three cases of your IF...THEN...ELSE, something is running (either the main check or the ELSE).
You do not want that, you only want one thing to run.

I think you would be better off here using a CASE statement. See: Using Select Case statements (VBA)
It would be structured something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case [Amp_performed]
        Case "Plate"
            'Code here that runs when value is "Plate"
        Case "Tubes"
            'Code here that runs when value is "Tubes"
        Case ""
            'Code here that runs when value is empty
        Case Else
            'Code here that runs when value is any other string
    End Select
    
End Sub

Just filling in the appropriate code to run for each criteria.
That ensures only exactly one code section will be run.

 

[londa_vba]

I think the issue is that you have multiple IF statements with ELSE clauses.
So in all three cases of your IF...THEN...ELSE, something is running (either the main check or the ELSE).
You do not want that, you only want one thing to run.

I think you would be better off here using a CASE statement. See: Using Select Case statements (VBA)
It would be structured something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case [Amp_performed]
        Case "Plate"
            'Code here that runs when value is "Plate"
        Case "Tubes"
            'Code here that runs when value is "Tubes"
        Case ""
            'Code here that runs when value is empty
        Case Else
            'Code here that runs when value is any other string
    End Select
   
End Sub

Just filling in the appropriate code to run for each criteria.
That ensures only exactly one code section will be run.

Works perfectly. Thank you for the explanation and the solution!

 

[Joe4]

You are welcome! Glad I was able to help!

The "Case" statement is really a nice feature when you are checking a single item for multiple different options/values.
It sure beats writing a bunch of If...Then...Else statements, and is much shorter!