Hide Command Button based on value in range

[redpipe67]

Hi,
I have spent a lot of time trying to find an answer to this but so far have only found How to hide a command button based on a cell value.

I am trying to find out how to Hide a command Button based on a value within a range of cells. For example, changing the below example from ("B6") to ("B:B") does not work.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("B6") = "" Then
CommandButton1.Visible = False
Else
CommandButton1.Visible = True
End If
End Sub

I wonder if anyone could assist me with this please.

Thanks in advance

 

[Chirophobic]

How about: (A range from A1 to A50)

Private Sub example()

If Application.CountIf(A1:A50, "=") = 0 Then
CommandButton1.Visible = TRUE

Else
CommandButton1.Visible = FALSE
End If

End Sub

 

[Chirophobic]

EDIT OF PREVIOUS POST


Private Sub example()
Dim intNumber As Integer
Dim rngRange As Range
Set rngRange = ActiveWorkbook.Worksheets(1).Range("B5:B14")

intNumber = Application.CountIf(rngRange, "=")

If intNumber = 0 then
cmdButton.value = TRUE
else
cmdButton.value = FALSE
End If


End Sub

 

[redpipe67]

Thank you Chirophobic for your assistance. However I was unable to get this to work. I have several CommandButtons on the worksheet in question and despite changing:
CmdButton.Value to CommandButton5.Visible and many variations including what you have posted, there was no change to the button in question as it remained visible.

Ideally the button would be hidden by default and shown only when anything except a blank cell or a particular word "Shipped" would appear anywhere in the range of cells (B5:B50) for example.

So the button is hidden if any of the cells are blank
the button is hidden if any of the cells contain "Shipped"
the button is hidden if a combination of blank cells and "Shipped" is displayed
Anything else in addition to blank cells or "Shipped" in the range of cells and the button becomes visible.

Thanks again for your response.

 

[Chirophobic]

OK, so I had a look around and tried some examples and I came up with this. In my example, I have a Command Button called "cmdButton" that has been set to invisible in the properties. I should also thank this person who gave me the idea: http://www.exceltip.com/st/Determin...a_range_using_VBA_in_Microsoft_Excel/484.html

OK, here goes:

Dim rngMain As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intNumber As Integer
Set rngMain = Application.Workbooks("Example.xlsm").Worksheets("Example").Range("B5:B50")

If InRange(Target, rngMain) Then
intNumber = Application.CountIf(rngMain, "=") + Application.CountIf(rngMain, "=Shipped")
If intNumber > 0 Then
cmdButton.Visible = True
Else
cmdButton.Visible = False
End If
End If
End Sub

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' This bit checks to see it Range1 is in Range2
Dim InterSectRange As Range

Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function

 

[redpipe67]

Chirophobic, I can't thank you enough.

You rock!

 

[mikerickson]

One of my pet peeves is using If..Then..Else..End If to set a value to True or False. This is an alternative.

If InRange(Target, rngMain) Then
    cmdButton.Visible = (0 < (Application.CountIf(rngMain, "=") + Application.CountIf(rngMain, "=Shipped")))
End If