PDA

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



BumbleyBee
11-03-2011, 05:30 AM
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
11-03-2011, 06:18 AM
Welcome to the forum!

Protect the workbook, sheet, and formula cells?

BumbleyBee
11-04-2011, 04:30 AM
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
11-06-2011, 04:57 AM
Please can anyone help with this vba problem? : pray2: : pray2: : pray2: 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
11-06-2011, 06:40 AM
Perhaps using the sheets selection change event
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CellDragAndDrop = False
End Sub

'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

BumbleyBee
11-07-2011, 05:05 AM
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
11-07-2011, 09:32 AM
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
11-07-2011, 02:49 PM
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
11-07-2011, 11:29 PM
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:

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.
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
11-08-2011, 03:41 AM
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

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

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/office/forum/office_2007-customize/excel-guru-force-enable-macro-solution-seriously/cdf9885f-f4e3-4424-8fab-7adc541d1c6f

frank_m
11-08-2011, 04:35 AM
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:

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


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

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/office/forum/office_2007-customize/excel-guru-force-enable-macro-solution-seriously/cdf9885f-f4e3-4424-8fab-7adc541d1c6f

BumbleyBee
11-08-2011, 05:24 AM
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
11-08-2011, 05:57 AM
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
11-09-2011, 05:53 AM
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. :content: 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
11-10-2011, 03:44 AM
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
11-10-2011, 04:17 AM
HI BumbleyBee (http://www.vbaexpress.com/forum/member.php?u=42198),

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
11-10-2011, 05:15 AM
Hi again, :hi: 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
11-10-2011, 07:42 AM
Hi again BumbleyBee,

Glad to hear that it's all going well.

Private Sub CommandButton1_Click()
Call UnProtectAllSheets
'or
'Call ProtectAllSheets
End Sub

Sub UnProtectAllSheets()
Dim ws As Worksheet

For Each ws In Worksheets

ws.Unprotect Password:="YourPassword"

Next ws
End Sub


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

frank_m
11-10-2011, 03:23 PM
HI BumbleyBee,

I forgot that you also have the workbook protected.

try this:

Private Sub CommandButton1_Click()
Call UnProtect_everything
'or
'Call Protect_everything
End Sub

Sub UnProtect_everything()
Dim ws As Worksheet

ActiveWorkbook.Unprotect Password:="YourWrkBkPassword"

For Each ws In Worksheets

ws.Unprotect Password:="YourWsPassword"

Next ws
End Sub

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

frank_m
11-10-2011, 08:57 PM
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.
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
Put CommandButton2 on the same sheet as CommandButton1. Using meaningful captions for both, of course

Private Sub CommandButton2_Click()

Call Protect_everything

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

ActiveWorkbook.Unprotect Password:="YourWrkBkPassword"

For Each ws In Worksheets

ws.Unprotect Password:="YourWsPassword"

Next ws
End Sub 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

BumbleyBee
11-11-2011, 05:36 AM
Wow! that's amazing! thanks sooo much ♥ :) . My project is looking great, I'm so happy. :cloud9:

The code is just fine, macros can't be bypassed now and cell drag/drop function etc is disabled when the workbook is open. Workbook protection ensures none of the worksheets can be copied or sent separately out of the workbook and changed.

The only weakness I can see is that it is still possible to select all cells on a (protected) worksheet, and copy and paste the selected worksheet layout and content to a new workbook, and use that copy for [uncontrolled] reporting rather than the ones in the workbook (which would be disastrous). Through this means necessary protection and restriction is avoided, and hidden columns and contents become visible. Unfortunately, there will be users who will attempt this as a shortcut.

Is it possible to disable the 'select all' option - which is available either by clicking the top left cell in a worksheet, or Edit/Select all from the menu? At the same time the column and row headers need to remain visible. :think:

frank_m
11-11-2011, 06:23 AM
Would this help? (This should make it so if the user selects more than one column, or more than one row, even via Cntrl A keyboard shortcut, the selection is reduced to only the active cell, making it only possible to copy a single cell to another workbook)

If this has any shortcuming there are still other posabilities such as disabling menu and keyboard shortcuts for copying, but that can get complex

If you never need the user to be able to select multiple rows or columns, this might be a short, sweet and simple solution.

Put this in the Thisworkbook code module so that it will effect every sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Selection.Columns.Count > 1 _
Or Selection.Rows.Count > 1 Then
ActiveCell.Select
End if
End Sub

BumbleyBee
11-12-2011, 04:55 AM
It works fine but unfortunately would interfere with use of the workbook (in some areas multiple selection is necessary). By the way I discovered holding down the shift key and double-clicking multiple cells will override the code - why is that?

Due to the workbook protection a user can't insert or copy a worksheet into the workbook, therefore may attempt to copy a worksheet from the workbook and paste it to another workbook in order to recreate the usersheet to avoid the workbook restrictions. Therefore a better preventative might be to force-empty the clipboard if the workbook loses active focus (eg if the user switches the focus to a concurrent Excel session (but not Word)) in order to try and paste the usersheet? If so how?

frank_m
11-12-2011, 07:16 AM
I'll have to test it tomorrow when I have more time, but Perhaps as below might work

I think there is a reference that needs to be set to use this. I'll look for it and post back.

Place code in the ThisWorkbook code modukle
Option Explicit

Private Sub Workbook_Deactivate()
Dim oClipClear As CommandBarButton

'On Error Resume Next

Set oClipClear = Application.CommandBars("clipboard") _
.FindControl(ID:=3634)
If Not oClipClear Is Nothing Then
If oClipClear.Enabled Then oClipClear.Execute
End If

If Application.CutCopyMode = xlCopy Then
Application.CutCopyMode = False
End If
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

'On Error Resume Next

Set oClipClear = Application.CommandBars("clipboard") _
.FindControl(ID:=3634)
If Not oClipClear Is Nothing Then
If oClipClear.Enabled Then oClipClear.Execute
End If

If Application.CutCopyMode = xlCopy Then
Application.CutCopyMode = False
End If
End Sub

frank_m
11-12-2011, 08:21 AM
No time to do testing now, but try everything I have come up with so far.

To use, a reference needs to be set to the Microsoft Forms 2.0 Object Library (or browse for the dll file FM20.DLL)

Place code in the ThisWorkbook code modukle
Option Explicit

Private Sub Workbook_Deactivate()
'I believe will fire when moving from one workbook to another
'On Error Resume Next
' this next block of code clears the excel clipboard, not to be confused with the windows clipboard
' Credit I believe goes to the infamous Ivan F Moala, but I'm not really sure where I found it
'------------------------------------------------------
Set oClipClear = Application.CommandBars("clipboard") _
.FindControl(ID:=3634)
If Not oClipClear Is Nothing Then
If oClipClear.Enabled Then oClipClear.Execute
End If
'------------------------------------------------------
' next commands should clear the windows clipboard
If Application.CutCopyMode = xlCopy Then
Application.CutCopyMode = False
End If
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'I believe will fire when moving from excel to Word, or other app
'On Error Resume Next
' this next block of code clears the excel clipboard, not to be confused with the windows clipboard
' Credit I believe goes to the infamous Ivan F Moala, but I'm not really sure where I found it
'------------------------------------------------------
Set oClipClear = Application.CommandBars("clipboard") _
.FindControl(ID:=3634)
If Not oClipClear Is Nothing Then
If oClipClear.Enabled Then oClipClear.Execute
End If
'------------------------------------------------------
' next commands should clear the windows clipboard
If Application.CutCopyMode = xlCopy Then
Application.CutCopyMode = False
End If
End Sub Other Useful code and information about the clipboard can be found at:
http://www.cpearson.com/excel/Clipboard.aspx

One more thing.
You are likely going to need to disable the design mode button. - You go about that the same way you did with disabling the Option Dialog Box from the menu.
Info for that at http://www.rondebruin.nl/menuid.htm, as mentioned in an earlier post on page one in this thread

In fact, I'm going to be doing that myself soon. Follow the thread (linked) below, for how it goes for me;
http://www.vbaexpress.com/forum/showthread.php?t=39781

{]

frank_m
11-12-2011, 09:46 AM
If that does not cover everything, also look at discussion at link below:
http://www.mrexcel.com/forum/showthread.php?t=44001

BumbleyBee
11-13-2011, 10:31 PM
Hello Frank, so grateful for your help.

I have put in the VBA reference to the object library as advised, also saved and reopened the workbook afterwards. Then copied and pasted your code into the thisworkbook module and re-saved.

Unfortunately the second part of the code (Private sub Workbook_WindowDeactivate) triggers an error: "Compile error: procedure declaration does not match description of event or procedure having the same name."

Also getting an error "compile error - variable not defined" at "Set oClipClear = Application"[etc].

sorry to be so inexperienced - unfortunately the chance to learn proper VBA skills bypassed me when I tried to get it. :(

frank_m
11-14-2011, 08:24 AM
Sorry, my fault
Need to add this at the begining of both of those subs.
Dim oClipClear As CommandBarButton

as for
(Private sub Workbook_WindowDeactivate) triggers a compile error
the correct spelling for that line is Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

if you do have the spelling correct and have an error, you may have put it in the wrong module, it is meant to be in the ThisWorkbook code module, or you may have two subs with that same name in the ThisWorkbook module. If there is two subs with that same name, then get rid of one and combine all the code from both, into one.

Aflatoon
11-14-2011, 09:07 AM
As a matter of interest, why the need for a Forms reference there? It doesn't appear to be in use?

Note also that Excel's events only fire within the application, so switching between separate instances of Excel would not be affected, if that is a concern.

Also, in my opinion, trying to create a truly secure application in Excel is a futile exercise.

frank_m
11-14-2011, 09:17 AM
I'll have to test later and get back to you on this
As a matter of interest, why the need for a Forms reference there? It doesn't appear to be in use?

--------------------------------------------------------------
Oh ok, yeah this will be a problem --- I thought even swithching between instance's would fire the deactivate window, but, I have the upmost respect for your wisdom, so I'll take your word for it.

as far as workbook deactivate, I did know that.

Note also that Excel's events only fire within the application, so switching between separate instances of Excel would not be affected, if that is a concern..
--------------------------------------------------------------
thanks for the explaination, another good point: ((Hey Bumbleybee, looks like we can only make it a little more secure, not a lot))

Also, in my opinion, trying to create a truly secure application in Excel is a futile exercise.

BumbleyBee
11-14-2011, 09:53 PM
Looking good! :

clipboard empties when switching between Excel workbooks and also between Excel sessions if more than one open. For this project, this is an important safeguard. Well done!:thumbThe effect is within Excel only: the clipboard retains all the data if switching to Word. We need to be able to do this.

Unfortunately I've discovered a risk: when pasting from Excel into Word, Word offers a range of different paste formats including paste as an Excel object - which looks harmless and copies all the formatting and appearance nicely - however if the source workbook has more than one tab/page, then all those pages are also embedded !!:SHOCKED: !! Imagine a client being able to open and view other clients information.... :eek:

This can be avoided if pasting into Word as either formatted text (RTF) or HTML, which preserve the table structure (eg rows & columns). But that assumes users will always make sure they do and in this case it can't be guaranteed.

I've made a macro that on click captures the data ready to paste into Word but am wondering is it possible to restrict the copy action or on-paste option so that pasting into Word as an Excel object is not available? Is it possible with Excel 2003 to limit the captured data to be only formatted text (RTF) or HTML [so it pastes as a table]?

Mr Aflatoon - thankyou for your input and you are very correct, it is almost impossible to create a totally secure application in Excel. My preference would be to build a straightforward Access database (I'm more familiar with that than Excel vba) but the higher-ups prefer to stick with what they [sort of] know. :(

frank_m
11-14-2011, 10:05 PM
Also, in my opinion, trying to create a truly secure application in Excel is a futile exercise
Hi again Bumbleybee. While I still agree with Aflatoon that you'll never be completly secure. I do think we can improve it.
For one thing you can remove cut and paste from the menus, and dissable the cnrl A and cntrl c and cntrl x keyboard shortcut's

Dissabling the menu items i think you already know how to do. The dissabling of keyboard shortcuts, I'll help you with, but it may take me a few days.

Also bear in mind that when you have such complex dissabling of menu items in the open and close events in Excel 2003, there can some times be resulting headaches to the user. For example if they are using multiple workbooks in the same instance, they can loose features in those other instances, unless it is all brillantly coded, and I'm not a brilliant coder.. Also on the occasions that excel crashes, there can be issues.

Also, I think I should mention again, with the aid of Aflatoon sharing his wisdom, it does seem sure to me now, that there is no way to acheive complete protection from other instances of excel, or msword. -_Perhaps though we can get it to be more secure than it is currently.

BumbleyBee
11-15-2011, 04:15 AM
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...

In his reply on page 1 of this thread [copied here], Mancubus provided some great resources which include disabling shortcuts. I have used some but am not very confident to write it correctly in vba, I get error messages :(

The team are flying in this week and when we have our workshop I will ask them why they paste and embed an Excel workbook when it has so much risk. Perhaps they just don't know.

You guys have helped me soo much, I am so grateful.:)

Incidentally, in the ThisWorkbook code, with the code to hide worksheets and only show the macro sheet if a user has disabled macros or tries to bypass, this works fine except that, if a user triggers the welcome/macro page to appear (by not having macros enabled or trying to bypass startup) the Private Sub DisableOptionsSubMenu does not run whereas it does if macros are enabled and all sheets appear. This means that if the macro page is triggered on workbook open, a user can then change options that should not be changed. Here is my code for the showallsheets, should I put in a 'call Private Sub DisableOptionsSubMenu' in it? If so where? I have also put this call in the workbook_open code but still does not activate if macro page is triggered.

Private Sub ShowAllSheets()
Dim ws As Worksheet
ActiveWorkbook.Unprotect Password:="mypassword"
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
'test insert here
ActiveWorkbook.Protect Password:="mypassword"
ActiveWorkbook.Protect Structure:=True, Windows:=False
Sheets("Index").Select
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, userinterfaceonly:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Next wks

End Sub

BumbleyBee
11-18-2011, 06:05 AM
:reading: ??? still trying but stuck :(

frank_m
11-19-2011, 06:23 AM
If macro page is triggered on workbook open, a user can then change options that should not be changed.
I'm not sure what you mean. The sheet named "macro"(Welcome page) is not shown, or triggered as you put it, unless macros are dissabled. - And I am not clear on how you are describing the user change settings.

Edit: I take back all that I wrote in the next pragraph. - I do not understand how the user is bypassing your settings, as any change they make when macros are turn off, won't be of use because all the data sheets are very hidden. - And when they reopen the workbook and enable the macros, your open event code will modify everything back to how you want it.
Try attached workbook in this post, then add your code that disables the option menu and sets allow drag and drop to false, then test run it and let me know if you are still having dificulties.
Yes, if the user first opens your workbook without enabling macros, there is no way to run the Private Sub DisableOptionsSubMenu during start up. The result being the user then is free to change option settings, then save and close the workbook, reopen it with macros enabled and the options will be as he/ or she prefers. -- In Excel 2003 and below I am nearly sure there is not anyway to protect from that. - If you were using 2007/2010, then you could safeguard that.

I rearranged the show all sheets macro that you posted, but I doubt that will help anything.'

Private Sub ShowAllSheets()
Dim ws As Worksheet
ActiveWorkbook.Unprotect Password:="mypassword"
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

For Each ws In ThisWorkbook.Worksheets
With ws
.EnableSelection = xlNoRestrictions
.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingRows:=True, userinterfaceonly:=True
End With
Next ws

ActiveWorkbook.Protect Password:="mypassword", Structure:=True, Windows:=False
Sheets("Index").Select
End Sub

Are you using Ken Pulls version of Force enable Macros?

The reason I ask is because the routine I'm using has some differences.
I can't say if it's improved over Ken's or not, other than to say one or two users think it is improved. See this page link http://answers.microsoft.com/en-us/office/forum/office_2007-customize/excel-guru-force-enable-macro-solution-seriously/cdf9885f-f4e3-4424-8fab-7adc541d1c6f
I've attached a new workbook where I added the sheet name "Index" and added the modified code as shown above.

frank_m
11-20-2011, 04:26 AM
HI again Bumblybee,

I added a small improvement to the show all sheets macro, where it tests to see if sheet protection is already on, before it turns it on.

Private Sub ShowAllSheets()
Dim ws As Worksheet
ActiveWorkbook.Unprotect Password:="mypassword"
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

For Each ws In ThisWorkbook.Worksheets
With ws
If Not .ProtectContents Then
.EnableSelection = xlNoRestrictions
.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingRows:=True, userinterfaceonly:=True
End If
End With
Next ws

ActiveWorkbook.Protect Password:="mypassword", Structure:=True, Windows:=False
Sheets("Index").Select
End Sub

Also I attached a new sample workbook that disables drag and drop, so that it is ready for you to test to see if you are able to bypass it.
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

frank_m
11-20-2011, 08:23 PM
Hi Bumblybee,

Please post your code that disables and restores Excels Options Sub Menu during open and close events, so that I can more effectively test the issues you are having.

Actually if you could post your workbook with all of the data removed, and non-relevant macros removed, but leave in all the regular sheets and hidden sheets that you want available to user. - Having all that would move us towards improving everything in less time and with less effort.:yes

Thanks

BumbleyBee
11-21-2011, 06:47 AM
Hi Frank_M, thankyou again for your very kind assistance.

I have attached a mock-up workbook - the sample layout and data are not real but provide a platform to work with.

go to the Index page - I have written notes there describing the essentials and where problems still occur. It is not just cell drag/drop that needs to be disabled, there are others and I have inserted the correct code for these (see notes on Index page). This activates fine however if macros are not enabled or user attempts startup bypass, the macro 'welcome' page appears without disabling the options as needed.

Incidentally the workbook needs password protection at all times however the datapage needs to be accessed by Word for mailmerge. Unfortunately Word will not do this if a workbook has protection enforced. The 'userinterfaceonly=true' code only applies to worksheets only (not workbook level). I don't know if there is an equivalent command at workbook level or whether this would permit Word to link for mailmerge while retaining workbook protection? I've searched thru the forum and note others raise this problem but can't see if there is a solution found.

frank_m
11-21-2011, 07:12 AM
Hi Bumblybee,

Thanks for posting the workbook. That should be very helpful.

Circumstance(a)
if macros are not enabled ...... the macro 'welcome' page appears without disabling the options as needed
One question related to Circumstance(a) above
(1a) Why does this matter ?
I ask because you stated that macros are not enabled,
which means all the data sheets are very hidden.(they can change option settings all they want, but no data is visible them to change) Right?
--------------------------------------------------------------------

Circumstance(b)
if user attempts startup bypass.
Three question's related to Circumstance(b) above
(1b) How are they bypassing the startup ?

(2b) Are macros enabled in this circumstance?(Edit: If only the Welcome Page(macro sheet) is visible, then the macros should always be disabled, and all data sheets verhidden. If macros are not disabled, or data sheets are not veryhidden, that means the force enabling of macros routine has a bug, or loop hole) - I need you to clarify what is happening.
-- Again, let me state that If macros are not enabled, and if all the data sheets are very hidden.(There is no data for them to change). Right?)

(3b) Assuming I am correct that all of the data sheets are very hidden at this point, why does it matter if they can access the settings options?
-- Settings will be changed back to the way you want them, and the option menu will be dissabled again, when the workbook is restarted with macros enabled. - Right?

Edit#2 The data sheets will be accessible to changes by word mail merge, or other macro type changes, because The protection code I gave you has user interface protection set to true. (meaning all the data sheets will be protected from user changes, but allows changes via code)

Edit#3 -- Sorry, I stand corrected about the protection issue in Edit#2, I now understand because of your enlightment about workbook protection verses sheet protection.
... Unfortunately Word will not do this if a workbook has protection enforced. The 'userinterfaceonly=true' code only applies to worksheets only (not workbook level)

- I'll have to give that situation some thought., but off hand, it sounds tough if you cannot use the unlocked cells approach. - For the word mail merge part of your dilemma, what if you have that routine unprotect the workbook in the code, then reprotect it when it finishes?

[]

BumbleyBee
11-22-2011, 05:55 AM
Hi, in answer to your questions:

circumstance a) and b): if a user attempts to bypass macros or does not have them enabled, the welcome/macro page event is triggered and operates correctly - all other worksheets are hidden, and the workbook protection is in place.

The disable options event (disable cell drag/drop, tools/options, workbook sharing) is triggered successfully if the wb is opened by the user correctly. However - it does not if the user attempts bypass and the macro page appears. At this point the user could activate the option 'share workbook' or 'protect/share workbook', which permits them to apply a password. If the workbook is then saved and re-opened, the 'this workbook' function and codes are disrupted and error messages appear. Worse, it is impossible to unlock and view or edit the vba editor and even the option to remove protect/share workbook is disabled.:wot The only way to fix it is then to open the workbook and trigger the macro page, the workbook sharing password option can then be removed.

Allowing the above to occur and/or sharing the wb would have very undesirable consequences and if I were not privy to the pw then would not be able to administer the workbook or contents. If the wb is shared I would also be unable to view or edit macros etc.

Whether the user attempts to bypass macros or open the workbook normally, the disable options code needs to activate in either startup situation and prevent this kind of scenario.

Re mailmerge and your comment 'what if you have that routine unprotect the workbook in the code, then reprotect it when it finishes?' - not sure what you mean here. I have seen code that applies wb protection when the wb is opened and unlocks it on close (and can't remember where I saw it) but there seemed to be different opinion on whether this left the wb exposed to unwanted editing through third party links.

frank_m
11-22-2011, 12:09 PM
Hi Bumblybee,

Wow, this has far more possible scenarios to protect against than I ever imagined you were wanting. I felt that with customizing only a few things, you might be able to get by with it, but even with just messing around with just a few user settings and menu items, you're surely going to adversely affect the use of other workbooks. - Even when you try to have everything being restored using deactivation, and closing event's, you will never have everything switching back and forth between users original settings and yours, during the simultaneous use of workbooks, and/or running in different instances of Excel. And worse yet, if excel or windows crashes, (which it will sooner or later), it's nearly impossible to record and restore the original user preferences.

So sorry that I didn't say much about the downside to all this sooner, but I think you have no reasonable way to do what you want in Excel 2003
--- Take a look at using Excel 2007/2010, as the menu customizations are far more adaptable, robust (and straight forward, once you get the hang of it). And all the modifications can be done at the workbook level.

One of the first things to find out before moving in that direction is to make sure there is a stable way to go about making an .xlsm rendered non-functional when opened in 2003, even when the 2003/2007 compatibility tool is installed.

[]

BumbleyBee
11-23-2011, 06:02 AM
Hi, am grateful for your comments. I will summarise as follows:

although the workbook code restricts some settings on open, the restrictions only apply to that Excel session. The users are happy to open a separate Excel session if they wish to open/use other workbooks without any restrictions. We've tested this and all operates well.
the users don't use Excel much and don't customise it much if at all.
our organisation unfortunately has Excel 2003 and an upgrade, while expected, won't happen for quite some time. Thus we must use this version. But I look forward to enhanced functionality when we eventually do upgrade and note your advice.:yes
Happily however, we seem to have a good LAN/operating environment and software crashes are very rare. On the two occasions I can remember where an Excel sheet crashed, the autorecover successfully reactivated the sheets.
If the workbook happened to crash or freeze, re-opening it and then closing it will restore user settings. We also keep backups.getting back to the mailmerge question - is there a way to allow Word to source the workbook for mailmerge, when the workbook is protected? :think:

frank_m
11-23-2011, 07:23 AM
Hi Bumblybee,

I understand that you are stuck with working with 2003, but I don't think you fully understand that using a different instance of Excel for other workbooks, is not going to protect you from having problems. I'm not an expert, so hopefully someone like xld, or aflatoon, or one of the other well qualified members here, will interject some of their wisdom soon.. - Actually, aflatoon already did try to discourage you from it in one of the posts near the beginning of this discussion, but hopefully he will be so kind as to elaborate a little more.

As far as backups are concerned, only restoring the entire hard drive of a users pc will restore his menu modifications that he/she might have, if Excel or Windows crashes while your workbook is open. -- A backed up workbook simply doesn't save that information, most of it is saved at the Excel application level. - For just the settings, not menubar items, you could incorporate some sort of settings backup, and there is software for that, as well as custom built ways to do it, but that doesn't alleviate the possibilities of users becoming irritated and loosing time.. -- You say crashes don't happen often, but when you combine the possibility of that, with all the other possibilities for annoyances, inconvenience and functionality problems; I still have to say that, (at the very least), you need to lower your expectations a lot and instead find ways to instill into the users the urgency that they follow your guide lines for using this workbook.

I'm happy to work on this more with you, if for some reason an expert here disagrees with the pitfalls that I've mentioned.

-----------------------------------------------------------------------------------------------------------------
Edit: Added coments below:


getting back to the mailmerge question - is there a way to allow Word to source the workbook for mailmerge, when the workbook is protected? :think:

I highly doubt it, but I'm the wrong person to answer that. - What I was getting at in an earlier post was mail merge initiated by way of a VBA code could unprotect the workbook, do the task, then re-protect it.

[]

BumbleyBee
11-29-2011, 06:32 PM
Hello again, thankyou for your feedback and I note your concerns. I have been considering different options as suggested but unfortunately I am assisting a very ill family member and have not had a chance to post these. I will respond when I can. Thankyou again.

frank_m
12-07-2011, 07:26 PM
Ref:Page2 (Post#29)

As a matter of interest, why the need for a Forms reference there? It doesn't appear to be in use?.
Hi Aflatoon,

I Finally came across a better answer to what had led me to thinking a Forms reference is needed..

http://word.mvps.org/faqs/macrosvba/ManipulateClipboard.htm


The DataObject object is a part of the Forms library in VBA. In order to make this code work, you must do one of two things.

(1) Have at least one UserForm in your project,
(2) or, In the VBA editor, go to Tools, References, and set a reference to the "Microsoft Forms 2.0 Object Library"

Aflatoon
12-07-2011, 11:40 PM
That is what I would have guessed but your code did not use a Dataobject.
That article also ignores the option of late binding it. ;)

frank_m
12-08-2011, 01:28 PM
Hi Aflatoon,

Of course you are correct. - My memory was confused between the office clipboard and other tasks I have done using the windows clipboard.

As for the late binding when using the Data object, my memory would have said it won't work, based on having tried it and various googling to other forums that I did many months ago, but as I am proving, my memory is not much to go by...:rotlaugh:

I'll play around with that later, so that I might get it to sink in.

Thanks for your response.

Aflatoon
12-08-2011, 02:05 PM
Simply:
Dim objData As Object
Set objData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

:)

BumbleyBee
04-10-2012, 03:23 AM
Hello all, sorry for very long absence... due to a sad death in the family. A hard journey.

I wish to express my very deep gratitude to all the expertise, help and advice provided to me in this thread, so much appreciated.

The work project that triggered this thread has since changed - it transpired that the proposed workbook would have had to be shared across a high number of users, and even with modified code to help protect the data and formulas, it wouldn't have been sufficient to protect it entirely from accidental changes.

So I have transferred the tool into Access and it is testing very well.

Thankyou all again, I learned a lot out of this venture !

:kiss: BumbleyBee