Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am ideally looking to only turn off the drag fill series functionality.

I only want the user of my sheet to be able to drag COPY down.

I turned the feature off in settings, which disable ALL drag downs. I would be happy with that, but of course this only works in my own Excel settings, other users of the sheet will still be able too in the context of their settings.

I am wondering if there is a way in VBA to turnoff the fill series features or disable the drag feature entirely?

share|improve this question

1 Answer 1

The biggest challenge you will face is Macro Security on the individual machines. If they are not set to run Macros this will keep this from running.

You can use the following in VBA to disable Cell Drag and Drop when the workbook opens.

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

In order to avoid making people angry, you should turn it back on when the workbook closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

You can use the IF statement to check if the feature is turned on off/on to ensure you return it to the original state. The biggest challenge you will face is Macro Security on the individual machines. If they are not set to run Macros this will keep this from running.

share|improve this answer
    
This is helpful thanks –  Koto Nov 19 '12 at 16:23
    
When closing the workbook down, I am getting an error box emerging: Compile Error, Ambiguous name detected: BeforeClose . I have saved both your macro's in "This Workbook" macro sheet as opposed to an individual sheet macro. –  Koto Nov 20 '12 at 11:35
    
Private Sub Workbook_BeforeClose() Application.CellDragAndDrop = True End Sub how does this macro know that the workbook is closing? –  Koto Nov 20 '12 at 11:45
    
Excel knows and executes the event handler/subroutine automatically when the event occurs. Also, the declaration should be Private Sub Workbook_BeforeClose(Cancel As Boolean) ... End Sub –  Karan Nov 20 '12 at 16:40
    
Thank you @Karan. Post corrected. –  CharlieRB Nov 21 '12 at 12:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.