Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Select a range
hi
I was kindly given this code by Otto Moehrbach, which works, However If i select a merged cell the sheet code crashes the only way i can make the code active again is to close the workbook and reopen it. Can this code be adapted to deal with merged cells and is it posible to restrict the number of rows Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Column < 4 Or Target.Column 6 Then Exit Sub If Target.Column = 4 Then Application.EnableEvents = False Target.Offset(, 1).Resize(, 2).ClearContents Application.EnableEvents = True End If If Target.Column = 6 Then Application.EnableEvents = False Target.Offset(, -2).Resize(, 2).ClearContents Application.EnableEvents = True End If If Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, -1).ClearContents Target.Offset(, 1).ClearContents Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 4 Or Target.Column 6 Then Exit Sub Target = "X" End Sub thanks kevin |
#2
|
|||
|
|||
hi,
i doubt you can make it work with a merged cell. merged cells, in my opinion, was not a good idea for micro soft to add to excel. works ok in word. but in excel, I don't use them and that is my recomendations to solve your problem. -----Original Message----- hi I was kindly given this code by Otto Moehrbach, which works, However If i select a merged cell the sheet code crashes the only way i can make the code active again is to close the workbook and reopen it. Can this code be adapted to deal with merged cells and is it posible to restrict the number of rows Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Column < 4 Or Target.Column 6 Then Exit Sub If Target.Column = 4 Then Application.EnableEvents = False Target.Offset(, 1).Resize(, 2).ClearContents Application.EnableEvents = True End If If Target.Column = 6 Then Application.EnableEvents = False Target.Offset(, -2).Resize(, 2).ClearContents Application.EnableEvents = True End If If Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, -1).ClearContents Target.Offset(, 1).ClearContents Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 4 Or Target.Column 6 Then Exit Sub Target = "X" End Sub thanks kevin . |
#4
|
|||
|
|||
I'm not sure what part of your code crashes, but .clearcontents doesn't play
nicely with just one cell of a merged range. You could try something like: Target.Offset(, 1).Resize(, 2).mergearea.ClearContents or even: Target.Offset(, 1).Resize(, 2).value = "" Both worked ok for me. Kevin wrote: hi I was kindly given this code by Otto Moehrbach, which works, However If i select a merged cell the sheet code crashes the only way i can make the code active again is to close the workbook and reopen it. Can this code be adapted to deal with merged cells and is it posible to restrict the number of rows Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Column < 4 Or Target.Column 6 Then Exit Sub If Target.Column = 4 Then Application.EnableEvents = False Target.Offset(, 1).Resize(, 2).ClearContents Application.EnableEvents = True End If If Target.Column = 6 Then Application.EnableEvents = False Target.Offset(, -2).Resize(, 2).ClearContents Application.EnableEvents = True End If If Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, -1).ClearContents Target.Offset(, 1).ClearContents Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 4 Or Target.Column 6 Then Exit Sub Target = "X" End Sub thanks kevin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Defined range difficulty | Excel Discussion (Misc queries) | |||
How do I select a range if one cells contents is equal to another | Excel Discussion (Misc queries) | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions |