Selection.Value Error when multiple cells selected

[James]

This is a followup to a previous question I had.
ok, this works fine if i have a single cell colored, but not if i have a few
merged cells or if i select more than one cell at a time. I get a Runtime
error type 13, "Type mismatch" at the Selection.Value part. Does anyone know
why im getting this. I know merged cells may be a problem, but i get the same
error when i select multiple cells. Is there a fix? This is the structure of
my code. If anyone has any suggestions im wide open to them. Thanks alot

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If Target.Interior.ColorIndex = 36 Then
If Selection.Value = "" Then 'IF BLANK YELLOW CELL CLICKED
UserForm1.Show
ElseIf Selection.Value <> "" Then 'IF <> "" YELLOW
CELLCLICKED
UserForm2.Show
End If

End If
End Sub

 

[Mike H]

Hi,


You could include this as the first line
If Target.Cells.Count > 1 Then Exit Sub

Mike

 

[Per Jessen]

Hi

You can only check the value of one cell at a time. In the code below if
more than one cell is selected, then it will skip rest of the macro.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Interior.ColorIndex = 36 Then
If Selection.Value = "" Then 'IF BLANK YELLOW CELL
CLICKED
UserForm1.Show
ElseIf Selection.Value <> "" Then 'IF <> "" YELLOW
CELLCLICKED
UserForm2.Show
End If

End If
End Sub

Hopes it helps

Regards,
Per

 

[Gary''s Student]

Mike H is correct. Selection.Value is risky. You can always set
selection.value to something:

Selection.Value = "something"

because ALL the cells will be given the text. But you are TESTING
Selection.Value and Excel does not know whether to perform the IF statement
if ALL the cell values in Selection are empty or if ANY of the cell values
are empty.

You need to loop over the individual cells in Selection and tell Excel what
to do.

 

[James]

Thanks everyone for your input, I have the tools I need now. Much appreciated

 

[James]

how do i create a check to see if the selection has text in it or is
blank....when selecting multiple cells? thanks, this has helped a lot

 

[James]

what do you mean formatted as numbers?

Are you selecting cells that are formatted as numbers? Try using is null
instead of = ""

 

[Per Jessen]

Try this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
For Each cell In Target.Cells
If cell.Value <> "" Then HasText = True
Next

If cell.Interior.ColorIndex = 36 Then
If HasText = False Then 'IF BLANK YELLOW CELL CLICKED
UserForm1.Show
Else
UserForm2.Show
End If
End If
End Sub

Regards,
Per