Worksheet Change Event to Show Command Button

**ker9** · 03-21-2011, 02:46 PM

I can't get this to work, any guidance? If the target is not blank (it starts out blank, then show the button. (I've reworked this in multiple ways and can't come up with the correct language)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Range("Approved"), Target) Is Nothing Then
        If Not Range("Approved") = "" Then
                cbtEmailMgr.Visible = True
    End If
    
End If

End Sub

Thanks!

**Leith Ross** · 03-21-2011, 03:05 PM

Hello ker9,

I would help to know a few more things. What error message are you getting? Is cbtEmailMgr on the same worksheet as the Worksheet_Change event code? Is this a Forms control, ActiveX control (ControlToolbox), or a Shape?

**ker9** · 03-21-2011, 03:22 PM

Hi, Leith,
Thanks for responding. This is an ActiveX command button and it is on the same sheet as the change event.
Generally I'm not getting an error message, it just isn't working right. I've been trying a 1,000 different renditions. Currently, I have the code below. The problem with this version is that when cell C14 (just above) is changed and the user hits return to drop to the C15 cell, the event fires and shows the button. But I don't want to see that button until something is typed into Cell C15 (which is merged C15:E15 and named Approved).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = Range("C15:E15").Address Then
    If IsEmpty(Range("C15:E15")) Then
        Exit Sub
    Else
        cbtEmailTeam.Visible = True
    End If
End If

End Sub

**ker9** · 03-21-2011, 03:30 PM

This code is telling me that the cell is not blank, but there is nothing in it so I guess that is part of my problem

[CODESub IsItBlank()
If IsEmpty(Range("C15:E15")) Then
MsgBox "It's blank"
Else
MsgBox "NOT blank"
End If[/CODE]

**ker9** · 03-21-2011, 04:06 PM

I think I've got it - I unmerge the range and checked each cell - they were all blank.

I re-merged it and now it's working with this code: (I don't understand it)

If IsEmpty(Range("approved")) Then
        Exit Sub
    Else
        cbtEmailTeam.Visible = True
    End If

**Leith Ross** · 03-21-2011, 04:39 PM

Hello ker9,

The IsEmpty function will only check a single cell. To check if a range is empty, you can use the WorksheetFunction.CountA function, like this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If WorksheetFunction.CountA(Range("C15:E15")) =  0 Then
       Exit Sub
    Else
        cbtEmailTeam.Visible = True
    End If

End Sub