Macro to Protect workbook using a password

[PCLIVE]

I know this would be a little unorthodox, but I currently have a macro that
will unprotect a workbook including a password. The line is as follows:

ActiveWorkbook.Unprotect (password)

I also want a macro that will do just the opposite, including the password.
Is this possible? I've tried the following with no luck.

ActiveWorkbook.Protect (password), Structure:=True, Windows:=True

 

[J.E. McGimpsey]

You got two replies when to your identical post on Tuesday. If they
weren't suitable, please explain why not.

If you didn't see them, you can always see your posts and any
replies using the Google archives (it takes 4-8 hours to show up
after posting to the groups):

http://google.com/advanced_group_search?q=group:*excel*

Use your email address in the Author search field.

 

[PCLIVE]

J.E.,

Thank you the Google info. I will check there. I apologize for the
duplicate posting. I never even saw my original message posted. Since I
couldn't find it today when I looked, I just assumed that it make it to the
posting.

You're always a great help.
Thanks again,
Paul Creecy

 

[PCLIVE]

Ok,

ActiveWorkbook.Protect (password), Structure:=True, Windows:=True

When I try to execute the above code, I get a:

Run-time error '5':
Invalid procedure call or argument

Thanks in advance for any insight you can provide.

Paul C.

 

[J.E. McGimpsey]

Your code works for me as long as password is either a string or a
non-empty variable (though in general, it's better not to use VBA
reserved words as variable names). note that there's no need for the
parens around (password), it just causes an unnecessary evaluation.

 

[PCLIVE]

Ok, I found out what I was missing. It was necessary for me to use quotes
around the password. Then it worked.

Thanks for your help.

One more question. Do you know how I can keep a macro from being seen. I
am running the macro from a key-press. I'd like to prevent thie macro from
being seen when clicking on Tools-Macro-Macros. Is that possible?

Thanks again,
Paul C.

 

[J.E. McGimpsey]

If you include an optional argument, it won't show up in the list
(though you can still type in the name of the macro and run it from
the Tools/Macro/Macros dialog), e.g.:

Public Sub foo(Optional bar As Variant)
MsgBox "foo"
End Sub

 

[Dave Peterson]

To keep the inquisitive from viewing your code (and seeing your password):

Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.

To keep it from appearing in the Tools|Macro|macros... list, put this at the
top:

Option Private Module

All the routines in that module will be hidden from that dialog.

You could also do it this way:
Private Sub myMacro1()

But then it's not visible to other routines in different modules, either.

And one more way to hide it is to set it up so that a parameter gets passed to
it (even an optional parm is ok):

Sub myMacro2(Optional dummyParm As Variant)

(dummyParm doesn't have to be used.)

===
All that said, there are password crackers that are available (pretty cheap,
too) that will allow the truly curious to view your code (and passwords).

And there is a "I forgot my password" crackers for workbook level and worksheet
level protection that is freely posted here every week.

 

[J.E. McGimpsey]

And if you use a hex editor, you can get the gist pretty quickly -
the code is tokenized, but it's readable even when the workbook and
project are protected.

 

[PCLIVE]

Thanks for all your help.

Luckily I don't think I'll have to worry to much about my people trying to
hack this particular file. In there position, there is not really any time
or reason for them to do so. But I'm trying to take the steps to make it
less tempting to the curious ones. If don't see the macro in the list, then
they won't go looking for the code (at least that will be true in the
environment that I'm using it in)

I've put the "Option Private Module" to use and it works quite well.
However, when I have this listed as Private, I'm then unable to use a
shortcut key for Macro. Is just something I'll have to live with, or is
there a way around this?

Thanks again,
Paul C.

And if you use a hex editor, you can get the gist pretty quickly -
the code is tokenized, but it's readable even when the workbook and
project are protected.

passwords).

 

[Dave Peterson]

You could use the "passed parm" technique.

But unless the macro is something that I want to use quickly and repetitively,
I'll stay away from the shortcut key (my memory ain't what it used to be).

I use a version of John Walkenbach's MenuMaker program in my personal.xla file:
It looks very nice and is easy to update.
http://j-walk.com/ss/excel/tips/tip53.htm


For those macros that are associated with a single workbook, I'll create a
toolbar when the workbook opens and assign the macro to an icon on that toolbar.

If you're interested, here's a shell that I keep when I want to add a custom
menubar:

In a general module:

Option Explicit
Sub create_menubar()

Dim i As Long

Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call remove_menubar

mac_names = Array("mac1", _
"mac2", _
"mac3")

cap_names = Array("caption 1", _
"caption 2", _
"caption 3")

tip_text = Array("tip 1", _
"tip 2", _
"tip 3")

With Application.CommandBars.Add
.Name = "Test99"
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
End With
Next i
End With
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars("Test99").Delete
On Error GoTo 0
End Sub


Under Thisworkbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub

Private Sub Workbook_Open()
Call create_menubar
End Sub

====
The Mac_names, cap_names, and tip_text are set up for 3 elements. But just
delete/add from each of these and the code will loop through them (even if
there's just one) to add buttons to a temporary toolbar.

(make sure you have the same number of elements for each array.)