Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

cannibalize some code

  Asked By: Maria    Date: Sep 10    Category: MS Office    Views: 1220
  

I have an issue that I am not sure how to address. I am not a
developer but tinker around with code that I find on the WEB. I have
a worksheet that I need to disable the copy-past function. I have
found some code which does it very nicely if macros are enabled so I
need to force the user to enable macros in order for the disable
macro to run. I have also found code to force macros as well. The
problem is I am not sure how to combine the two. The macro fails at
the
Private Sub Workbook_BeforeClose(Cancel As Boolean)
The errror that I am getting "Ambiguous name detected:
Workbook_BeforeClose"

Here is all of the code


'*** 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 "Sorry! Cutting, copying and pasting have been disabled
in this workbook!"
End Sub

'*** In the ThisWorkbook Module ***
Option Explicit

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

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

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

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you
made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel
close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property
to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible =
xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible =
xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Geneva Morris     Answered On: Sep 10

You actually seem to have two problems here.
#1.. the error you're getting: "Ambiguous name detected"
means that somehow, you have TWO macros  with the same name.
Do a "find" (ctrl-f) and search for "beforeclose" and see what you find.

#2.. this one is more of a problem.
The EVIL people of this world have long ago discovered that one method
of introducing a virus on an unsuspecting world is to write a installation
macro cleverly hiding in an Excel file. By setting it to execute during
the Auto_Open macro  or a Workbook_Open macro, then hiding it or even
removing it when it is complete. Some of these macros aren't caught
by virus scanners because they THEMSELVES aren't a virus, but what they
can DO is connect and download the virus.
Because of this, the Microsoft Empire has expended considerable resources
to enable  users to receive warning when they are about to open a file that
contains macros. If it were as simple as including one line of code  to
FORCE the macros to run, then the EVIL people of this world would be sure
to find  this secret method of bypassing the protection.

Here's how I have handled it:

I "lock down" the spreadsheet with password protection.
Then, in the Auto_Open or Workbook_Open macro, include the password command
that "unprotects" the workbook. Then, password protect the VBA modules.
In this way, if the user  has selected to NOT run  the macros, then the workbook
would not be of any use to them. The only way to use it would be to open it
and elect to Enable Macros.

If you need help with this, I might be able to locate my code,
and put together instructions for password protecting the file and VBA modules.

 
Answer #2    Answered By: Fabia Ferrrari     Answered On: Sep 10

Well worth giving a go IMHO........................

 
Answer #3    Answered By: Anuja Shah     Answered On: Sep 10

Oops.. forgot my item #3...
Check into the documentation for:
Application.CutCopyMode = False

this may save you a BUNCH of coding !!!

 
Answer #4    Answered By: Emma Campbell     Answered On: Sep 10

found  some more code  which works a bit better. Since my initial
post, I have too have figured out what my problem was and have
correct it. (I am proud of my novice self).
I guess when I really grow up I will be a competent script writer.
In the mean time I will continue to find  solutions and try to figure
them out- change to suit my needs. So, I thank you bunches for your
help – as in previous times I am most appreciative.

 
Didn't find what you were looking for? Find more on cannibalize some code Or get search suggestion and latest updates.




Tagged: