I have a sheet with three columns filled with validation lists, but whe
the user merge cells to input something, for the cells still contai
validation lists, they don’t allow the users input anything excep
choosing from the lists. Therefore, I tried to use SelectionChang
event to delete the validation lists when the users need entr
something in merged cells.

Followed code does delete the validation list in the merged cells
but when the users select an unmerged cell twice the validation lis
also was deleted. How can I improve my code? I use Excel 2002 SP-1.



Thank you a lots in advance.





Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If abortproc Then

aboutproc = True

Exit Sub

End If

Dim cRange As Range

Set cRange = ActiveCell.CurrentRegion

If cRange.MergeCells = True Then cRange.Select

With Selection.Validation.Delete

End With

End Su

--
Message posted from http://www.ExcelForum.com

Micher,

I couldn't reproduce your problem, but try this code instead, it seems to do
what you want

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cRange As Range

If Target.MergeCells = True Then
Target.Validation.Delete
End If
End Sub


I took these 4 lines out
If abortproc Then
aboutproc = True
Exit Sub
End If
because a) it uses 2 different (b ut very similar) variables, and b) it
never gets used anywhere


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"micher " wrote in message
...
I have a sheet with three columns filled with validation lists, but when
the user merge cells to input something, for the cells still contain
validation lists, they don't allow the users input anything except
choosing from the lists. Therefore, I tried to use SelectionChange
event to delete the validation lists when the users need entry
something in merged cells.

Followed code does delete the validation list in the merged cells,
but when the users select an unmerged cell twice the validation list
also was deleted. How can I improve my code? I use Excel 2002 SP-1.



Thank you a lots in advance.





Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If abortproc Then

aboutproc = True

Exit Sub

End If

Dim cRange As Range

Set cRange = ActiveCell.CurrentRegion

If cRange.MergeCells = True Then cRange.Select

With Selection.Validation.Delete

End With

End Sub


---
Message posted from http://www.ExcelForum.com/

It works fine now, Thank you so much!


Have a very good day

--
Message posted from http://www.ExcelForum.com