PDA

View Full Version : Provide option to disable macros



rajagopal
01-20-2009, 12:57 AM
Hi,
I've the following codes in "This workbook" module...


Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox ("The 'Save As' function has been disabled."), vbExclamation, "Warning"
Cancel = True
End If
End Sub
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.Goto Worksheets(2).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub
Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Below code is available in a separate module..
'*** In a standard module ***
Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Cut, Copy and Paste functions have been disabled for security reasons.", vbExclamation, "Warning"

End Sub

I want to have a option wherein if i select "No", the cut/copy/paste macro function will be disabled.

Please help..

Simon Lloyd
01-20-2009, 02:12 AM
Rajagopal, please take the time to read the forum rules and help. Everytime you post code you should enclose it in VBA tags, it makes it easier to read and sets it apart from your question, i have done it for you in this instance but please remember for the future.

Simon Lloyd
01-20-2009, 02:16 AM
If you say NO to what?

If you mean the message box here's how to do it:
If MsgBox("Do you want to go ahead?", vbYesNo, "Yes or No?") = vbNo Then
MsgBox "you clicked no!", vbOKOnly, "NO"
Else
MsgBox "you clicked Yes!", vbOKOnly, "YES"
End If

rajagopal
01-20-2009, 02:18 AM
Thanks for the info.
Can you help about my requirement..?

Simon Lloyd
01-20-2009, 03:56 AM
Thanks for the info.
Can you help about my requirement..?Errmmm!.............how about this The post above your response? (http://vbaexpress.com/forum/showpost.php?p=173850&postcount=3)

rajagopal
01-20-2009, 04:50 AM
My requirement is to disable the macros entirely so that my team can copy, paste required information and then enable it.
With this, the copy, paste function will be disabled for other users.

the msg box confirmation will not meet my requirement.

Simon Lloyd
01-20-2009, 07:50 AM
Either hold down the shift key when opening the workbook or use:
Application.EnableEvents = False

rajagopal
02-06-2009, 10:18 AM
Hi,
Only the SAVE AS code execution is disabled using the Application.EnableEvents=False statement.
Still, the cut.copy, paste code is getting executed.