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

[frank_m]

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]

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.
• 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?

[frank_m]

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?

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]

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]

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/...eClipboard.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]

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]

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

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

Thanks for your response.

[Aflatoon]

Simply:
[vba] Dim objData As Object
Set objData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")[/vba]

[BumbleyBee]

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 !

BumbleyBee