Selection.Value Error when multiple cells selected
 

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

Selection.Value Error when multiple cells selected
 

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

James wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1

Selection.Value Error when multiple cells selected
 

Also replace the ElseIf with just an else statement the if on it would just
be redundant and might cause problems.

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

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
[quoted text clipped - 17 lines]
End If
End Sub

--
Message posted via http://www.officekb.com

Selection.Value Error when multiple cells selected
 

Hi,


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

Mike

"James" wrote:

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

Selection.Value Error when multiple cells selected
 

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

"James" skrev i meddelelsen
...
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

Selection.Value Error when multiple cells selected
 

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.
--
Gary''s Student - gsnu200800


"James" wrote:

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

Selection.Value Error when multiple cells selected
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200800


"James" wrote:

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

Selection.Value Error when multiple cells selected
 

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

"Per Jessen" wrote:

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

"James" skrev i meddelelsen
...
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

Selection.Value Error when multiple cells selected
 

what do you mean formatted as numbers?

"dustinbrearton via OfficeKB.com" wrote:

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

James wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1

Selection.Value Error when multiple cells selected
 

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

"James" skrev i meddelelsen
...
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

"Per Jessen" wrote:

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

"James" skrev i meddelelsen
...
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