| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| why are multiple cells selected when i only want one? | Excel Discussion (Misc queries) | |||
| Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
| why do multiple cells get selected when i click only one cell? | Excel Discussion (Misc queries) | |||
| Macro for using multiple selected cells | Excel Programming | |||
| Macro to select cells and then new selection from selected cells | Excel Programming | |||
Hybrid Mode
