Prevent filldown

[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é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...

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.

 

[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é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.

I tested from version xl-97

regards,
hector.

 

[Dave]

Ok, thanks Héctor. I'll use it Monday.
Regards - Dave.