Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
Happy new year everyone.
I have an issue with the code below and wondered if there was a way to get round the problem. The issue is that R11:S11 and R12:S12 are merged cells. With single cells, the code toggles between a tick (Wingdings character 252) and blank when each cell is selected. With merged cells nothing happens. Is there a way to enable the code when the cells are merged? Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit With Worksheets("Sheet1") Set rTick = Worksheets("Sheet1").Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End With sub_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
Working with Merged cells sucks sometimes.
1.) When your selection is your merged cells R11:S11 the Target address is R11:S11. You can't use Target.Value, because this refers to a single cell. You will have to use Target.Value2(1,1) or Target.Value2(1,2). 2.) Plus you don't have to use the Worksheets("Sheet1") reference when setting your rTick range variable because it is in the With Sheets("Sheet1") statement. 3.) Also, why do you have Application.EnableEvents set to False at the beginning of your code? 4.) I would also declare your rTick variable as a Range. Try this below. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Dim rTick As Range With Sheets("Sheet1") Set rTick = .Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value2(1, 1) = Chr(252) Then .Value2(1, 1) = "" Else .Value2(1, 1) = Chr(252) .Font.Name = "Wingdings" End If End With End If End With End Sub -- Cheers, Ryan "IanC" wrote: Happy new year everyone. I have an issue with the code below and wondered if there was a way to get round the problem. The issue is that R11:S11 and R12:S12 are merged cells. With single cells, the code toggles between a tick (Wingdings character 252) and blank when each cell is selected. With merged cells nothing happens. Is there a way to enable the code when the cells are merged? Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit With Worksheets("Sheet1") Set rTick = Worksheets("Sheet1").Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End With sub_exit: Application.EnableEvents = True End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
Hi Ryan
Thanks for the suggestion, but it doesn't work. I've tried it in my original workbook and in a completely new workbook and the results are the same. If I put a breakpoint in and step though the code, there are no errors at any point, even though the tick is not created and Value2(1,1) remains "Empty". A couple of seconds after I step through "End Sub" I get a message saying that Excel has encountered a problem and needs to close. It doesn't close properly, though as Excel still appears in Task Manager Processes (though not Applications).. "Ryan H" wrote in message ... Working with Merged cells sucks sometimes. 1.) When your selection is your merged cells R11:S11 the Target address is R11:S11. You can't use Target.Value, because this refers to a single cell. You will have to use Target.Value2(1,1) or Target.Value2(1,2). 2.) Plus you don't have to use the Worksheets("Sheet1") reference when setting your rTick range variable because it is in the With Sheets("Sheet1") statement. 3.) Also, why do you have Application.EnableEvents set to False at the beginning of your code? 4.) I would also declare your rTick variable as a Range. Try this below. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Dim rTick As Range With Sheets("Sheet1") Set rTick = .Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value2(1, 1) = Chr(252) Then .Value2(1, 1) = "" Else .Value2(1, 1) = Chr(252) .Font.Name = "Wingdings" End If End With End If End With End Sub -- Cheers, Ryan "IanC" wrote: Happy new year everyone. I have an issue with the code below and wondered if there was a way to get round the problem. The issue is that R11:S11 and R12:S12 are merged cells. With single cells, the code toggles between a tick (Wingdings character 252) and blank when each cell is selected. With merged cells nothing happens. Is there a way to enable the code when the cells are merged? Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit With Worksheets("Sheet1") Set rTick = Worksheets("Sheet1").Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End With sub_exit: Application.EnableEvents = True End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
Ian,
Try it this way: Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit Set rTick = Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target.Cells(1, 1) If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If sub_exit: Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "IanC" wrote in message ... Hi Ryan Thanks for the suggestion, but it doesn't work. I've tried it in my original workbook and in a completely new workbook and the results are the same. If I put a breakpoint in and step though the code, there are no errors at any point, even though the tick is not created and Value2(1,1) remains "Empty". A couple of seconds after I step through "End Sub" I get a message saying that Excel has encountered a problem and needs to close. It doesn't close properly, though as Excel still appears in Task Manager Processes (though not Applications).. "Ryan H" wrote in message ... Working with Merged cells sucks sometimes. 1.) When your selection is your merged cells R11:S11 the Target address is R11:S11. You can't use Target.Value, because this refers to a single cell. You will have to use Target.Value2(1,1) or Target.Value2(1,2). 2.) Plus you don't have to use the Worksheets("Sheet1") reference when setting your rTick range variable because it is in the With Sheets("Sheet1") statement. 3.) Also, why do you have Application.EnableEvents set to False at the beginning of your code? 4.) I would also declare your rTick variable as a Range. Try this below. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Dim rTick As Range With Sheets("Sheet1") Set rTick = .Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value2(1, 1) = Chr(252) Then .Value2(1, 1) = "" Else .Value2(1, 1) = Chr(252) .Font.Name = "Wingdings" End If End With End If End With End Sub -- Cheers, Ryan "IanC" wrote: Happy new year everyone. I have an issue with the code below and wondered if there was a way to get round the problem. The issue is that R11:S11 and R12:S12 are merged cells. With single cells, the code toggles between a tick (Wingdings character 252) and blank when each cell is selected. With merged cells nothing happens. Is there a way to enable the code when the cells are merged? Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit With Worksheets("Sheet1") Set rTick = Worksheets("Sheet1").Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End With sub_exit: Application.EnableEvents = True End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
Yes, I got the same issue. Excel automatically closes when the code is
executed when using .Value2(1,1), WEIRD, must be a bug. Is Sheet1 the sheet you have this code in? I did this and didn't have any issues. I also shorten your code by not using a variable, but if you need to use the rTick variable, then I would suggest you declare you variables. For example rTick needs to be declared as a Range. I would recommend putting Option Explicit at the top of each of your modules to ensure you don't have any undeclared variables which can make your code very difficult to debug. Hope this helps! If so, let me know, click "YES" below. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- If Not Intersect(Target, Range("R11:S12")) Is Nothing Then With Target.Cells(1, 1) If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End Sub -- Cheers, Ryan "IanC" wrote: Hi Ryan Thanks for the suggestion, but it doesn't work. I've tried it in my original workbook and in a completely new workbook and the results are the same. If I put a breakpoint in and step though the code, there are no errors at any point, even though the tick is not created and Value2(1,1) remains "Empty". A couple of seconds after I step through "End Sub" I get a message saying that Excel has encountered a problem and needs to close. It doesn't close properly, though as Excel still appears in Task Manager Processes (though not Applications).. "Ryan H" wrote in message ... Working with Merged cells sucks sometimes. 1.) When your selection is your merged cells R11:S11 the Target address is R11:S11. You can't use Target.Value, because this refers to a single cell. You will have to use Target.Value2(1,1) or Target.Value2(1,2). 2.) Plus you don't have to use the Worksheets("Sheet1") reference when setting your rTick range variable because it is in the With Sheets("Sheet1") statement. 3.) Also, why do you have Application.EnableEvents set to False at the beginning of your code? 4.) I would also declare your rTick variable as a Range. Try this below. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Dim rTick As Range With Sheets("Sheet1") Set rTick = .Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value2(1, 1) = Chr(252) Then .Value2(1, 1) = "" Else .Value2(1, 1) = Chr(252) .Font.Name = "Wingdings" End If End With End If End With End Sub -- Cheers, Ryan "IanC" wrote: Happy new year everyone. I have an issue with the code below and wondered if there was a way to get round the problem. The issue is that R11:S11 and R12:S12 are merged cells. With single cells, the code toggles between a tick (Wingdings character 252) and blank when each cell is selected. With merged cells nothing happens. Is there a way to enable the code when the cells are merged? Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit With Worksheets("Sheet1") Set rTick = Worksheets("Sheet1").Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End With sub_exit: Application.EnableEvents = True End Sub . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
... Ian, Try it this way: Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit Set rTick = Range("R11:S12") If Not Intersect(Target, rTick) Is Nothing Then With Target.Cells(1, 1) If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If sub_exit: Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP Thanks Bernie. That works a treat in a blank workbook. I'll try it in my original workbook tomorrowe, but it's looking promising. -- Ian -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection change code with merged cells
"Ryan H" wrote in message ... Yes, I got the same issue. Excel automatically closes when the code is executed when using .Value2(1,1), WEIRD, must be a bug. Is Sheet1 the sheet you have this code in? I did this and didn't have any issues. I also shorten your code by not using a variable, but if you need to use the rTick variable, then I would suggest you declare you variables. For example rTick needs to be declared as a Range. I would recommend putting Option Explicit at the top of each of your modules to ensure you don't have any undeclared variables which can make your code very difficult to debug. Hope this helps! If so, let me know, click "YES" below. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- If Not Intersect(Target, Range("R11:S12")) Is Nothing Then With Target.Cells(1, 1) If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Name = "Wingdings" End If End With End If End Sub -- Cheers, Ryan Thanks Ryan. That works, though I do need the variable in there as the range varies depending on the content of a specific cell. Bernie's version of the code is perfect. I just need to incorporate it into my original workbook, but that won't be until tomorrow. -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Probklem with code, when merged cells | Excel Programming | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
Problem with code for merged cells | Excel Programming | |||
changing merged cells from code | Excel Programming | |||
Fine-tuning selection change event for merged cells & wrap text | Excel Programming |