Help!!! I've screwed up my Excel!

[The Tamer]

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]

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]

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]

... they will not be able to copy at all.

Unless they disable macros.

[The Tamer]

I've used the veryHidden solution to the disabling of macros - secure enough for my needs here.

Thanks all again!

[CBrine]

True enough Zack.

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.

[Zack Barresse]

True enough Zack.

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.

:rofl I love it!

[CBrine]

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]

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]

Quis Custodiet Ipsos Custodes?

[Zack Barresse]

So, Tamer, did you get the original query resolved? We kinda sidetracked your thread here. Sorry!

[The Tamer]

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]

Cool! So should this be marked Solved then?

[Jacob Hilderbrand]

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.