Prevent filldown

D

Dave

Can I use an event macro to prevent the user from using the fill handle on
unprotected cells?
The issue is data validation. Column B has a list validation.
(For example: Tom1,Dick1,Harry1)
If the user chooses "Tom1" from the drop-down list in cell B1, then uses the
fill handle to fill down, B2 will read "Tom2", B3 will read "Tom3" etc, which
is not prevented by data validation.
Excel 2000.
Thanks in advance
Dave,
 
H

Héctor Miguel

hi, Dave !
Can I use an event macro to prevent the user from using the fill handle on unprotected cells?
The issue is data validation. Column B has a list validation. (For example: Tom1,Dick1,Harry1)
If the user chooses "Tom1" from the drop-down list in cell B1
then uses the fill handle to fill down, B2 will read "Tom2", B3 will read "Tom3" etc,
which is not prevented by data validation...
Click to expand...

one (possible) option is the following in "that" worksheet code-module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CellDragAndDrop = Intersect(Target, Range("b:b")) Is Nothing
End Sub

hth,
hector.
 
D

Dave

Hi Héctor,
Works a treat. Thanks a lot!
I'm at home now, using XL2007, but I use XL2000 at work.
Will it run in XL2000?
Regards - Dave.
 
H

Héctor Miguel

hi, Dave !
Works a treat. Thanks a lot!
I'm at home now, using XL2007, but I use XL2000 at work.
Will it run in XL2000?
Regards - Dave.
Click to expand...

I tested from version xl-97 :D

regards,
hector.
 
Top