Security on an attachment

J

JLatham

Cathy,
I think 'Bob I' has offered what many would consider the best solution since
it involves 'normal' features of Windows and Excel.

There is also a VBA code solution that I can offer. The code disables the
"Send To" entry under the File menu, and disables the e-Mail icon in the
standard toolbar when you have the workbook open and chosen. If you close
the workbook, or switch to another while the sensitive one is open, those
options are re-enabled so they can be used again/with the other workbook(s).
There are 2 steps to getting this code into your workbook.

STEP 1: The following code goes into a standard code module. To put it
there, open your sensitive workbook and press [Alt]+[F11] to open the VB
Editor. Choose 'Insert' and 'Module' from the VBE menu bar. Copy the
following code and paste it into the module presented to you:

Private Sub DisableSendTo()
Const eMailButtonID = 3738
Dim anyButton As Object

For Each anyButton In CommandBars("Standard").Controls
If anyButton.ID = eMailButtonID Then
anyButton.Enabled = False
End If
Next
CommandBars("Worksheet Menu Bar"). _
Controls("File").Controls("Send To").Enabled = False
End Sub

Private Sub EnableSendTo()
Const eMailButtonID = 3738
Dim anyButton As Object

For Each anyButton In CommandBars("Standard").Controls
If anyButton.ID = eMailButtonID Then
anyButton.Enabled = True
End If
Next
CommandBars("Worksheet Menu Bar"). _
Controls("File").Controls("Send To").Enabled = True
End Sub

close the VB Editor.

STEP 2: This code goes into the same workbook's Workbook code segment. To
get to that, right-click on the Excel icon that's to the immediate left of
the word "File" in the Excel menu bar and choose [View Code] from the pop-up
list that appears. Copy and paste the code below into that module and again
close the VB Editor.

Private Sub Workbook_Open()
Run "DisableSendTo"
End Sub

Private Sub Workbook_Activate()
Run "DisableSendTo"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "EnableSendTo"
End Sub

Private Sub Workbook_Deactivate()
Run "EnableSendTo"
End Sub


If it doesn't seem to disable the eMail icon in the Standard tool bar, it
may be that the ID for the email Icon is different than the 3738 that it is
in my Excel 2003 copy. If you need to find the number for your version, the
code below can be copied into a regular module and run to tell you what the
ID numbers for the icons in your version's toolbar are:
Sub FindIDs()
Dim anyButton As Object

For Each anyButton In CommandBars("Standard").Controls
ActiveCell = anyButton.ID
ActiveCell.Offset(0, 1) = anyButton.TooltipText
ActiveCell.Offset(1, 0).Activate
Next

End Sub
 
Top