PDA

View Full Version : [SOLVED] Help!!! I've screwed up my Excel!



The Tamer
11-05-2004, 07:11 AM
Help!!!

I modified a macro that disabled copy and paste to disable the edit and tools menus.

Now it won't reactivate them... What am I doing wrong?


Option Explicit

Sub DisableCopyCutAndPaste()
EnableControl 30007, False 'tools
EnableControl 30003, False 'edit
Application.OnKey "^c", "Dummy"
Application.OnKey "^v", "Dummy"
Application.OnKey "+{DEL}", "Dummy"
Application.OnKey "+{INSERT}", "Dummy"
Application.CellDragAndDrop = False
Application.OnDoubleClick = "Dummy"
CommandBars("ToolBar List").Enabled = False
End Sub

Sub EnableCopyCutAndPaste()
EnableControl 30007, True 'tools
EnableControl 30003, True 'edit
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "+{DEL}"
Application.OnKey "+{INSERT}"
Application.CellDragAndDrop = True
Application.OnDoubleClick = ""
CommandBars("ToolBar List").Enabled = True
End Sub

Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As Control
Dim C As CommandBarControl
On Error Resume Next
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next
End Sub

Sub Dummy()
'// NoGo
MsgBox "Sorry, you cannot copy this read-only version of the planner!"
End Sub


Thanks

CBrine
11-05-2004, 07:22 AM
Tamer,

Give this code a try. Depending on where you locked up the menu, it might work.



Sub FixMenu()
Application.CommandBars("File").Enabled = True
Application.CommandBars("Edit").Enabled = True
Application.CommandBars("View").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
Application.CommandBars("Format").Enabled = True
Application.CommandBars("Tools").Enabled = True
Application.CommandBars("Data").Enabled = True
Application.CommandBars("Window").Enabled = True
Application.CommandBars("Help").Enabled = True
End Sub

CBrine
11-05-2004, 07:25 AM
To set your sheet to no COPY give this code a try. I will stop all copy and paste. Combined with Workbook protection to prevent a right click on the sheet tab copy, they will not be able to copy at all.

HTH
Cal



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

Zack Barresse
11-05-2004, 09:22 AM
... they will not be able to copy at all.

Unless they disable macros. ;)

The Tamer
11-05-2004, 10:07 AM
I've used the veryHidden solution to the disabling of macros - secure enough for my needs here.

Thanks all again!

CBrine
11-05-2004, 10:11 AM
True enough Zack.
:dunno
I fogot to include, that when I use this, I remove the toolbars, the commandmenus, have a worksheet password, have a workbook password and make all sheets xlveryhidden as well. So they can't really get into my sheets without the macro's enabled.:bug:

Zack Barresse
11-05-2004, 10:16 AM
True enough Zack.
:dunno
I fogot to include, that when I use this, I remove the toolbars, the commandmenus, have a worksheet password, have a workbook password and make all sheets xlveryhidden as well. So they can't really get into my sheets without the macro's enabled.:bug:
:rofl I love it! :D

CBrine
11-05-2004, 10:59 AM
I thought about hiring a big muscular guy to go around to each of my spreadsheet users, and slap them in the side of the head each time they tried to do something they weren't supposed to. That might be going to far though.:)

Ken Puls
11-05-2004, 12:14 PM
I thought about hiring a big muscular guy to go around to each of my spreadsheet users, and slap them in the side of the head each time they tried to do something they weren't supposed to. That might be going to far though.:)
:rofl Same thought has occured to me on occasions! (We don't have the budget though! :no )

mdmackillop
11-05-2004, 12:49 PM
Quis Custodiet Ipsos Custodes?

Zack Barresse
11-05-2004, 06:13 PM
So, Tamer, did you get the original query resolved? We kinda sidetracked your thread here. Sorry!

The Tamer
11-05-2004, 06:49 PM
Well I guess they're all good suggestions in their own ways. :)
Actually, in spite pf my panic, what i'd done was quite simple - i just didn't realise it.
I have code from DRJ and IVAN that disables copy on the read-only version. but i amended it and tested it on the write version. but the code to put the menus back also only worked on the write version! and i was just blind.

The problem on my s/s isn't sensetivity, but deletion and sabotage. important s/shts kept on reverting to older versions, and we'd lose tons of info. So now you can't open without macros (xlVeryHidden) . you can't do any copying on the read-only version. Save and SaveAs are also disabled, and sheets can't be dragged and copied.
Also, two backups are recorded in secret locations with the date and time being recorded as the file name.

Its secure enough for amatures.

anyhows, thanks again guys.
Tamer

Zack Barresse
11-05-2004, 06:53 PM
Cool! So should this be marked Solved then? ;)

Jacob Hilderbrand
11-05-2004, 07:12 PM
I thought about hiring a big muscular guy to go around to each of my spreadsheet users, and slap them in the side of the head each time they tried to do something they weren't supposed to. That might be going to far though.:)

:rofl I would do that, but I would go broke with all the work I would be sending his way.