Hide command button if cell has a value advice

[ipbr21054]

I have various command buttons on my worksheet.
I wish to hide / show specific command buttons depending on whether a specific cell has a value or not.

Just to start me off i have the following BUT the command button is shown all the time.
Can you advise what ive done wrong please.

Basicallically wish the relevant button to toggle visability whether it cell in question is empty or has a value.
Should be simple enough but cant see this error here ?
Thansk

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B4") = "" Then ' If there is a value in cell then hide button
AprilStartButton.Visible = True
End If

If Range("E4") = "" Then ' If there is a value in cell then hide button
MayButton.Visible = True
End If

End Sub

 

[ipbr21054]

Ok so i have now got it working with the following BUT it needs to be altered.
Reason is when i manually go down the page clicking cells then stop i can see the code still running & selecting cells as you work expect.

So i need it to be written correctly so it will be shorter & run fast / smooth
The below carries on for another few more months

If Range("B4") = "" Then
AprilStartButton.Visible = True
Else
AprilStartButton.Visible = False
End If

If Range("E4") = "" Then
MayButton.Visible = True
Else
MayButton.Visible = False
End If

If Range("B19") = "" Then
JuneButton.Visible = True
Else
JuneButton.Visible = False
End If

If Range("E19") = "" Then
JulyButton.Visible = True
Else
JulyButton.Visible = False
End If

If Range("B34") = "" Then
AugustButton.Visible = True
Else
AugustButton.Visible = False
End If

 

[NoSparks]

Is that the entire _SelectionChange code for this sheet ?

 

[ipbr21054]

No,
This is it supplied.
Just to advise you that i changed it from SELECTION CHANGE to CHANGE which helps a bit but as you can see the code is still far to long.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B4") = "" Then
AprilStartButton.Visible = True
Else
AprilStartButton.Visible = False
End If

If Range("E4") = "" Then
MayButton.Visible = True
Else
MayButton.Visible = False
End If

If Range("B19") = "" Then
JuneButton.Visible = True
Else
JuneButton.Visible = False
End If

If Range("E19") = "" Then
JulyButton.Visible = True
Else
JulyButton.Visible = False
End If

If Range("B34") = "" Then
AugustButton.Visible = True
Else
AugustButton.Visible = False
End If

If Range("E34") = "" Then
septemberButton.Visible = True
Else
septemberButton.Visible = False
End If

If Range("B49") = "" Then
OctoberButton.Visible = True
Else
OctoberButton.Visible = False
End If

If Range("E49") = "" Then
NovemberButton.Visible = True
Else
NovemberButton.Visible = False
End If

If Range("B64") = "" Then
DecemberButton.Visible = True
Else
DecemberButton.Visible = False
End If

If Range("E64") = "" Then
JanuaryButton.Visible = True
Else
JanuaryButton.Visible = False
End If

If Range("B79") = "" Then
FebruaryButton.Visible = True
Else
FebruaryButton.Visible = False
End If

If Range("E79") = "" Then
MarchButton.Visible = True
Else
MarchButton.Visible = False
End If

If Range("B94") = "" Then
AprilEndButton.Visible = True
Else
AprilEndButton.Visible = False
End If

End Sub

 

[ipbr21054]

@NoSparks did you have a suggestion ?

Thanks

 

[NoSparks]

Yes, leave it as is.

 

[dmt32]

as you can see the code is still far to long.

Not tested but see if this update to your code will do what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng         As Range, cell As Range
    Dim i           As Long
    
    Set rng = Me.Range("E64,B79,E79,E4,B19,E19,B34,E34,B49,E49,B64,B4,B94")
    
    i = 1
    If Not Intersect(Target, rng) Is Nothing Then
        Me.AprilStartButton.Visible = rng.Areas(12).Value = ""
        For Each cell In rng.Cells
            Me.OLEObjects(MonthName(i, False) & "Button").Visible = cell.Value = ""
            i = i + 1
            If i > 12 Then Exit For Else If i = 4 Then i = 5
        Next
        Me.AprilEndButton.Visible = rng.Areas(13).Value = ""
    End If
    
End Sub

Dave

 

[ipbr21054]

Works fine thanks very much

 

[dmt32]

Works fine thanks very much

welcome & appreciate your feedback

Dave