Can we disable the AutoFill functionality on certain cells only ?

[Jaafar Tribak]

Hi.

I want to prevent copying onto some specific cells when holding and dragging adjacent cells . In other words not to allow AutoFilling should those specific cells come within range of the autofill area.

Disabling the AutoFill functionality via ( Tools>Options>Edit>Allow Cell Drag and Drop ) is not an option because this applies to the entire application. I only need this on specific cells and want to keep the AutoFill feature for the rest of the worksheet.

I have been playing around with some code but it is proving more difficult than I initially thought unless I am missing something.

Any ideas ?

 

[Jaafar Tribak]

Ok nevermind . I found a stable workaround by dinamically Protecting and UnProtecting the worksheet in the Worksheet_SelectionChange event handler.

 

[Jaafar Tribak]

Oops ! I think I spoke too soon.

Toggling the sheet protection as mentioned in the previous post is proving problematic because it has the side effect of reseting the Clipboard and I can no longer copy and paste .

So Problem not solved.

Is there a solution to this without having to protect the sheet ?

 

[GlennUK]

It's not the toggling that generates the side effect of reseting the Clipboard ... it's just running the event macro ( with or without the toggling ) that has that side effect. I don't know of a way around it either.

 

[Jaafar Tribak]

It's not the toggling that generates the side effect of reseting the Clipboard ... it's just running the event macro ( with or without the toggling ) that has that side effect. I don't know of a way around it either.

Thanks Glenn.

I didn't experience that.

I don't get the clipboard reset just by running the event macro. Even editing cells and making other changes to other excel objects within the event doesn't in fact reset the Clipboard.

 

[Andrew Poulsom]

Why don't you just permanently protect the worksheet after unlocking the cells you want the user to be able to change?

 

[Jaafar Tribak]

Why don't you just permanently protect the worksheet after unlocking the cells you want the user to be able to change?

Having the cells unlocked won't prevent the autofill from overriding their contents.

Thanks.

 

[Andrew Poulsom]

Why do you want to prevent autofilling on unlocked cells? You mentioned that you were toggling protection. Why not just keep protection on?

 

[Jaafar Tribak]

Why don't you just permanently protect the worksheet after unlocking the cells you want the user to be able to change?

Ok maybe I didn't explain myself clearly enough.

I dont want to allow the contents of the cells to be erased by the AutoFill but still allow the user to edit the cells.

The reason is that those cells contain Data validation and/or Conditional Formatting.

Normal Copy and paste erases the contents of the cells as well but I have that scenario already handled.

 

[Andrew Poulsom]

Maybe you can use this:

http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/