Unauthorized Movers!

[Dean]

I made a protected template for my dad and, despite my warnings,
occasionally he moves (cuts and pastes) one of the input cells which screws
up the equations. Is there a way to set it up so he can edit the input
cells but not move them?

Thanks!
Dean

 

[Don Guillett]

Have you considered spanking?

 

[Dean]

He's about 2500 miles away!

Have you considered spanking?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Harlan Grove]

I made a protected template for my dad and, despite my warnings,
occasionally he moves (cuts and pastes) one of the input cells which
screws up the equations. Is there a way to set it up so he can edit the
input cells but not move them?

Try putting the following code into the ThisWorkbook class module of the
workbook.


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Application.CutCopyMode = xlCut Then Application.CutCopyMode = False
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Application.CutCopyMode = xlCut Then Application.CutCopyMode = False
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If Application.CutCopyMode = xlCut Then Application.CutCopyMode = False
End Sub


This will turn off Cut mode when he moves around within the same worksheet,
moves to a different worksheet, or moves to a different workbook.

 

[Dean]

Perhaps I needed to be more explicit. Actually, my dad doesn't even know
how to cut and paste. But what he does know is how to move by dragging the
contents of a cell with his mouse. I always assumed that such was exactly
the same as cut and paste, but it seems to not be true since your procedure
seems to hush the paste icon, but does not impede clicking and dragging.
Can you help prevent that?

Dean

 

[Harlan Grove]

Perhaps I needed to be more explicit. Actually, my dad doesn't
even know how to cut and paste. But what he does know is how to
move by dragging the contents of a cell with his mouse. . . . ....
Can you help prevent that?

Tools > Options, Edit tab, uncheck 'Allow cell drag and drop'.

 

[Dean]

Silly me - I knew that once!

I assume that then becomes a global EXCEL setting, but can I have this file
uncheck it when he opens the file and recheck it when he exits (or when he
resaves it)?

Dean

 

[Harlan Grove]

I assume that then becomes a global EXCEL setting, but can I have this file
uncheck it when he opens the file and recheck it when he exits (or when he
resaves it)?

....

Record the method calls involved while performing these operations manually,
then add the unchecking code to the workbook's Open and Activate event
handlers and the checking code to the workbook's BeforeClose and Deactivate
event handlers.

 

[Dean]

Sounds good - thanks!

Dean

...

Record the method calls involved while performing these operations
manually, then add the unchecking code to the workbook's Open and Activate
event handlers and the checking code to the workbook's BeforeClose and
Deactivate event handlers.

 

[Dave Peterson]

Open your workbook
Hit alt-F11 to get to the VBE
Hit ctrl-r to see the project explorer (like windows explorer)
Select your project
Hit the asterisk on the numeric keypad (not above the 8 on the QWERTY keys)

Doubleclick on ThisWorkbook (for your workbook's project)

On the right hand side, you'll see the code window.

Use the dropdown on the top left of that window to choose: Workbook

You can use the righthand side dropdown to choose from the available events
(Workbook_Open and Workbook_Activate).

 

[Dave Peterson]

The original thread has disappeared off my newsreader.

But I bet Harlan was suggesting that you need two events--one when you close the
workbook and one when you move to a different workbook.

I don't really know what you're trying to accomplish.

 

[Dave Peterson]

Open excel
Click on the Office button (top left corner)
Click on Excel Options botton (on the bottom)
Click on the Advanced category (left hand side)
Near the top:
Editing Options Group
Look for (and check)
Enable fill handle and cell drag-and-drop
and probably(???)
Alert before overwriting cells

======
If you're both using winXP, you may want to try establishing a NetMeeting. The
other user may be able to share his desktop and you could see everything that he
does (right or wrong!).

Windows|Start button|Run
type:
conf
(for conference?)
and hit enter

He'll have to know your IP address to connect to you, though.
I use: http://www.whatismyip.com

=========
NetMeeting doesn't exist in Vista. But there is an option you can use:

Windows start button|Help and Support
Under the Ask for Assistance:
Invite a friend to connect to your computer with Remote Assistance

(This is available in WindowsXP, too. But I like NetMeeting better if I can use
it.)

 

[Dave Peterson]

You'd want to tell your dad to uncheck it.

I didn't remember what you asked when I was typing the instructions.