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

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
.

you might like this

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrange = Range("d3:f11")
myrange.UnMerge
If Not Intersect(Target, myrange) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Column
Case 4: Target.Offset(, 1).Clear: Target.Offset(, 2).Clear
Case 5: Target.Offset(, -1).Clear: Target.Offset(, 1).Clear
Case 6: Target.Offset(, -1).Clear: Target.Offset(, -2).Clear
Case Else
End Select
Application.EnableEvents = True
End If
End Sub

--
Don Guillett
SalesAid Software

"Kevin" wrote in message
om...
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

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