Excel 2003: How to disable cell drag and drop when workbook is opened?

[BumbleyBee]

Hi there, I am hoping someone can help me. I am creating a user tool and to protect my array formulas etc created to gather data entered from each user page in the workbook, I have created an auto_open macro that turns off the cell drag & drop function (found in the Tools/Options/Edit menu). An auto_close macro turns the function back on when the workbook is closed.

But some users might find the loss of that function annoying, and by selecting Tools/Options/Edit, manually turn cell drag and drop function back on, and endanger the formulas.

Is there code that can disable the cell drag/drop function and prevent it from manually being switched back on while the workbook is open?

Grateful for any help.

[Kenneth Hobs]

Welcome to the forum!

Protect the workbook, sheet, and formula cells?

[BumbleyBee]

Hi Kenneth, thanks for replying. I have applied protection to the workbook, sheets and cells that users do not input into. I have also applied a password protect on my vba project. This protects the macros and most operations. But while my auto_open macro turns off cell drag/drop, this does not prevent users from re-enabling this function while they use the workbook.

So what I need to find is code that, when the workbook is opened, disables macro bypassing, turns off cell drag/drop and also disables the cell drag/drop function from being manually switched on while the workbook is in use.

I am not a real programmer , but I use Excel extensively and create my own macros and have designed and built a number of Access databases to make our administration streamlined and up to the task. So I am hoping for a prince charming to rescue me with some code !

[BumbleyBee]

Please can anyone help with this vba problem? My staff are flying over from interstate in 7 days for training with the new tool I am creating and it would be so helpful to have the cell drag/drop function force-disabled whenever the workbook is in use.

[frank_m]

Perhaps using the sheets selection change event
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CellDragAndDrop = False
End Sub[/vba]
[vba]
'In the ThisWorkbook code module
Option Explicit ' At the top of the Module
Dim DragAndDrop As Boolean 'Flag for returning the setting to the way it was

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = DragAndDrop
End Sub
[/vba]

[BumbleyBee]

Thankyou Frank, I have pasted the code into the ThisWorkbook module, saved and tested it however while the cell drag/drop function is disabled on open, it can still be turned back on by opening Tools/Options/Edit. This is what I need to prevent.

The workbook I am creating has approx 30 different sheets, these feed into a datapage in the workbook using array formulas. If cell drag/drop is able to be activated and cells in the sheets moved about, the datapage formulas will not be trustworthy and serious errors will result.

Is it possible to somehow force-disable so that a user cannot turn it back on while the workbook is open?

[frank_m]

Using the sheets Worksheet_SelectionChange event would offer some protection, but admitedly that is not entirely protecting you as the user could select a cell, then change the setting, then drag and drop before making a selection change.

There might be a way to remove the entire setting option choice from the menu, but that can sometimes cause headaches for users if Excel ever happens to crash.(not worth messing with that in my opinion)

Protecting the sheet, as Kenneth suggested, and having a password is likely your best option. - When you turn that on there are options you can see check box's for. - You could uncheck the box next to allow users to select locked cells, then set up all your formula cells to be locked and all other cells to be unlocked.(in Excel 2007/2010 there is likely a checkbox specifically to protect formulas. - I don't think that's available in 2003)

[mancubus]

wellcome to vbax.

first password protect your vba project.

adopt the procedures here to force users enable macros in Office 2003.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

for disabling command bars and controls in Office 2003 visit:
http://www.rondebruin.nl/menuid.htm

for disabling keys and key combinations visit:
http://www.rondebruin.nl/key.htm


all macros to: ThisWorkbook code module.
see the method here http://www.rondebruin.nl/menuid.htm
scroll down to title Other useful examples...

[BumbleyBee]

Thankyou Frank_m and Mancubus - very helpful input.

Re your suggestions - some background info about my project:

• all sheets are protected
• all cells in the sheets are locked, except the cells in which users are to insert data (and it is for these cells that I need the workbook cell drag/drop function disabled as this would cause errors in the data page which gathers all the data from the user input per page).
• the workbook is protected, to prevent changes to sheet names, importing/exporting sheets, and other unwanted tampering.
• my vba project is also protected (with passwords to all above).

Mancubus - I have followed up your links, great coding, thankyou! I now know the coding to force disable the cell drag/drop by disabling the Tools/Options menu and also to prevent its bypass by disabling the Customise Toolbar option.
But:

1. With the code which forces users to enable macros (when the workbook is opened) - I have followed the instructions and it all works fine until I protect the workbook. The protection seems to disrupt the code when it attempts to change the visible setting of the relevant worksheets. After that, even if I remove workbook protection, or change my macro setting, the code no longer works.
2. A second problem is that even with the above codes, if the SHIFT key is pressed when the workbook is opened, all coding is ignored and the user is able to bypass the coding and open the workbook and use cell drag/drop if they wish to.

Is there coding to fix all the above?

[frank_m]

I believe there is a Sub in there named something like what I have below .
I have tweaked it to leave some sheets hidden and to unprotect and re-protect during the process
[vba]
Private Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ActiveSheet.Unprotect
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
If ws.Name = "HiddenSheetOne" _
Or ws.Name = "HiddenSheetTwo" _
Or ws.Name = "HiddenSheetThree" Then
ws.Visible = xlSheetHidden
End If
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
ActiveSheet.Protect
End Sub
[/vba]
Also I attached a sample workbook where I incorporated those changes.
All the rest of the routine I found at the link given below(the last post given there). I can't say whether or not it's as good or better than what you have, but it seems to work well for me and you can decide for yourself by reading the discussion in that thread:
http://answers.microsoft.com/en-us/o...b-7adc541d1c6f

[frank_m]

After checking that sample workbook more I realized that I accidently was turning the sheet protection on and off, when it should be the workbook protection.
I've attached a revised sample workbook.
.. and below are the revised modifications:
[vba]
Private Sub HideAllSheets()
Dim ws As Worksheet
ActiveWorkbook.Unprotect
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
ActiveWorkbook.Protect Structure:=True, Windows:=False
Worksheets(WelcomePage).Activate
End Sub
[/vba]
[vba]
Private Sub ShowAllSheets()
Dim ws As Worksheet
ActiveWorkbook.Unprotect
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
If ws.Name = "HiddenSheetOne" _
Or ws.Name = "HiddenSheetTwo" _
Or ws.Name = "HiddenSheetThree" Then
ws.Visible = xlSheetHidden
End If
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
[/vba]
Other than the couple of modifications shown above, the Routine in the sample workbook I got from the last post at the link below:
http://answers.microsoft.com/en-us/o...b-7adc541d1c6f

[BumbleyBee]

thankyou Frank, I will try this. Been working on this problem for 8 hours today, trawling through the forum hoping to find different solutions. It is midnight now, will report back tomorrow. many thanks.

[frank_m]

A second problem is that even with the above codes, if the SHIFT key is pressed when the workbook is opened, all coding is ignored and the user is able to bypass the coding and open the workbook and use cell drag/drop if they wish to.

In that case, yes the code not running would cause the option dialog not to be deleted from the menu, and the user could change to allow drag and drop, but that shouldn't matter, because if the force macros routine is working correctly, all the data sheets would be veryhidden. And when the workbook is closed and reopened with macros enabled, your code in the OpenEvent to dissable drag and drop and delete the Options menu item should work.

[BumbleyBee]

Thankyou Frank_M, I have been working with the sample you provided (thankyou!) and have added a password at the relevant lock/unlock areas, this is working well. I am now testing the code in my project and will try adding the cell drag/drop and disable options code. This will be the fun part as I am not skilled in writing VBA, but I'll have a go and report back tomorrow if poss.

[BumbleyBee]

Hi Frank_M, the code testing seems to be going very well so far

A small snag is that my workbook will contain approx 30 sheets and your coding specifies each sheet (in your sample) by indvidual name. Is it possible to have code that locks or unlocks all sheets (except the macro page depending on startup condition) without having to name each one in the code?

Scratch that question - managed to answer it!

Next question - is there a code that will let me unlock / or lock all worksheets if I need to? 30 pages is a lot of pages.. !

[frank_m]

HI BumbleyBee,

If macros are enabled all normal sheets will be made visible in the code, regardsless of their names. - The only sheets I specified by name in the code were a few sheets that would be hidden to the user even with macros enabled, but available with the menu > format > unhide sheets.
(as I thought you wanted a few hidden sheets during normal use of the workbook.)

To give you a better understanding of what the enitire routine is doing, If macros are not enabled, absolutely none of the sheets will be accessable to the user by way of the uhide sheets menu choice, because they will be more than just hidden, they will be veryhidden, which requires code to unhide them. - But if macros are enabled, the few sample hidden sheets that are in my sample workbook will be always hidden at start up if macros are on, but available for unhiding by the user. (as you already realize the welcome page is only present if macros are turned off.)

- Just so I'm clear
(1) was I correct that you want a few hidden sheets during normal use of the workbook?--> or is it more than a few hidden sheets you want ?
(2) after you get everything working, you are going to password protect the vba project, to safeguard the code from user tampering, correct ?

[BumbleyBee]

Hi again, your code is working really nicely (and yes I will lock my vba project), what I need now is, having opened my workbook with macros, to be able to unlock all worksheets simultaneously (with password) if I need to make administrator changes to them, then lock them all up again (with password) when I'm done. I remember seeing this somewhere else in the forum but can't find it .

[frank_m]

Hi again BumbleyBee,

Glad to hear that it's all going well.
[vba]
Private Sub CommandButton1_Click()
Call UnProtectAllSheets
'or
'Call ProtectAllSheets
End Sub
[/vba][vba]
Sub UnProtectAllSheets()
Dim ws As Worksheet

For Each ws In Worksheets

ws.Unprotect Password:="YourPassword"

Next ws
End Sub
[/vba]
[vba]
Sub ProtectAllSheets()
Dim ws As Worksheet

For Each ws In Worksheets

ws.Protect Password:="YourPassword"
'or
'ws.Protect Password:="YourPassword", DrawingObjects:=True, Contents:=True, _
'Scenarios:=True, userinterfaceonly:=True
'userinterfaceonly set to true, allows code to change cells, while protecting from user changes

Next ws
End Sub
[/vba]

[frank_m]

HI BumbleyBee,

I forgot that you also have the workbook protected.

try this:
[vba]
Private Sub CommandButton1_Click()
Call UnProtect_everything
'or
'Call Protect_everything
End Sub
[/vba]
[vba]Sub UnProtect_everything()
Dim ws As Worksheet

ActiveWorkbook.Unprotect Password:="YourWrkBkPassword"

For Each ws In Worksheets

ws.Unprotect Password:="YourWsPassword"

Next ws
End Sub
[/vba]
[vba]Sub Protect_everything()
Dim ws As Worksheet

For Each ws In Worksheets

ws.Protect Password:="YourWsPassword"
'or
'ws.Protect Password:="YourWsPassword", DrawingObjects:=True, Contents:=True, _
'Scenarios:=True, userinterfaceonly:=True
'userinterfaceonly set to true, allows code to change cells, while protecting from user changes

Next ws

ActiveWorkbook.Protect Password:="YourWrkBkPassword", Structure:=True, Windows:=False
End Sub [/vba]

[frank_m]

Forgetful me, you're also going to need an InputBox with an administrative password

This first part I used an activex type sheet command button. Place that on any sheet you wish.
[vba]Private Sub CommandButton1_Click()

Dim MyPassword As String

MyPassword = InputBox("Please Enter The Password.")

If MyPassword = vbNullString Then
MsgBox "Canceled"

Exit Sub
End If

If Not LCase(MyPassword) = "adminpassword" Then ' replace with whatever password you want of course
'LCase allows the password be non-case sensitive,
'but whatever password you use you should enter here in the code using all lower case

MsgBox "The Password you entered is not correct"

Exit Sub

Else

Call UnProtect_everything

MsgBox "Workbook and all sheets are now unprotected"

End If

End Sub [/vba]
Put CommandButton2 on the same sheet as CommandButton1. Using meaningful captions for both, of course
[vba]
Private Sub CommandButton2_Click()

Call Protect_everything

End Sub [/vba] These next two I put in a public module, although you could make private subs within the command buttons module.
[vba]Sub UnProtect_everything()
Dim ws As Worksheet

ActiveWorkbook.Unprotect Password:="YourWrkBkPassword"

For Each ws In Worksheets

ws.Unprotect Password:="YourWsPassword"

Next ws
End Sub [/vba] [vba]Sub Protect_everything()
Dim ws As Worksheet

For Each ws In Worksheets

ws.Protect Password:="YourWsPassword"
'or
'ws.Protect Password:="YourWsPassword", DrawingObjects:=True, Contents:=True, _
'Scenarios:=True, userinterfaceonly:=True
'userinterfaceonly set to true, allows code to change cells, while protecting from user changes

Next ws

ActiveWorkbook.Protect Password:="YourWrkBkPassword", Structure:=True, Windows:=False
End Sub [/vba]