Macro to Protect OK, Unprotect now messed up!

[Guest]

I thought I was being clever in recording a macro to protect workbooks with a
password, and then another to unprotect, using ctrl+ a diff letter everytime.

OK, it works, but now to unprotect I'm not even being asked for password!!
This happens whether I use the macro or the "unprotect" feature from the
menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!

Obviously, it sort of defeats the purpose of protecting in the first place,
if anyone can unprotect.

Is this happening, because I'm doing it on the same PC where my macro is
stored?

Help..

Thanks

 

[JE McGimpsey]

Did you protect the sheets with a password? Like

Sheets("Sheet1").Protect Password:="drowssap"

If not, then the sheet is protected without a password, and you won't be
prompted when you unprotect.

Protecting without a password is still useful - after all, the only
thing protecting actually does is prevent the casual user from screwing
up the worksheet. Anyone with the ability to find these newsgroups can
easily discover a method to bypass worksheet protection:

http://www.mcgimpsey.com/excel/removepwords.html

 

[Rowan Drummond]

That is because when you record a macro to protect the workbook it does
not automatically record the password. If you go into the vbe your code
will look something like:

Sub Protct()
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

change it to:

Sub Protct()
ActiveWorkbook.Protect Structure:=True _
, Windows:=False, password:="mypassword"
End Sub

Similarly the macro to unprotect the book should look something like:

Sub Unprtct()
Dim pwd As String
pwd = InputBox("Enter Password...", "Unprotect Book")
If pwd = "mypassword" Then
ActiveWorkbook.Unprotect Password:=pwd
Else
MsgBox "Incorrect Password"
End If
End Sub

And now that you have the password in the VBA code you will probably
want to protect your VBE project so that this can't be viewed. In the
VBE use the menus to goto Tools>VBAProject Properties>Protection. Check
lock projet for viewing and supply a password.

Hope this helps
Rowan

 

[Guest]

AH HA! I did what you told me and it works! Yeah, I know protection is not
robust.. but it's a whole lot better than nothing!

I am totally new to macros, so let me ask another question...

When I "record" a macro - I am storing it in my Personal Macro worksheet -
(one of the choices). I am doing this instead of attaching the macro to the
file I'm working on, so that when I send this file on email, and someone else
opens it, a) they don't get those pesky security alerts, and 2) they won't be
able to open vba and read my password. IS THIS LOGIC CORRECT?

Thanks for sharing your knowledge!

 

[wmjenner]

Yes your logic is correct. Unless you store the macro in the actua
workbook, it doesn't get sent when you e-mail the file to someone s
they cannot open it. Conversely, I have saved macros that hide an
unhide certain worksheets within a workbook (confidential stuff, fo
example) in my personal workbook. If you want to give access t
someone to unhide and rehide, you have to then physically copy th
macro to their own personal workbook (or, if they're not down the hall
send them the code)

 

[Guest]

Hi ...THANKS... I beginning to understand the macro logic now. I just opened
the file I was working on yesterday, and my "personal macro file" also opened
in the background.

Is this Excel just being nice, or did I do something wrong?

 

[Gord Dibben]

Stilla

Personal.xls will open when Excel opens.

If you have it set to "hidden" it will open in the background(won't show up on
Window) and your macros will be available for all open workbooks.


Gord Dibben Excel MVP

Hi ...THANKS... I beginning to understand the macro logic now. I just opened
the file I was working on yesterday, and my "personal macro file" also opened
in the background.

Is this Excel just being nice, or did I do something wrong?

 

[Guest]

THANKS Gord! EXCEL IS SOOOOOOCOOOOL

Stilla

Personal.xls will open when Excel opens.

If you have it set to "hidden" it will open in the background(won't show up on
Window) and your macros will be available for all open workbooks.


Gord Dibben Excel MVP

Hi ...THANKS... I beginning to understand the macro logic now. I just opened
the file I was working on yesterday, and my "personal macro file" also opened
in the background.

Is this Excel just being nice, or did I do something wrong?

 

[Jarhead19901993]

Is it possible, and if so how, to keep the characters from showing in the
input box when entered if someone is near and may see the password?

 

[Gord Dibben]

Not when using an Input Box

You would need a TextBox using PasswordChar probably on a UserForm

See VBA help on passwordchar property.


Gord Dibben MS Excel MVP

 

[Jarhead19901993]

Are there examples of the code necessary? I have seen links in other posts,
but all of the sites I've uncovered links to linking to Daniel Klann who
wrote code for this purpose appear to no longer function.

 

[Gord Dibben]

A sample from Dave Peterson using a textbox on a UserForm.

Private Sub CommandButton1_Click()
Dim myPwd As String
myPwd = "ok"
UserForm1.MultiPage1.Pages("admin").Visible _
= CBool(Me.TextBox1.Value = myPwd)
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.PasswordChar = "*"
End Sub


Gord

 

[Jarhead19901993]

Thank you for your help.

Rick

A sample from Dave Peterson using a textbox on a UserForm.

Private Sub CommandButton1_Click()
Dim myPwd As String
myPwd = "ok"
UserForm1.MultiPage1.Pages("admin").Visible _
= CBool(Me.TextBox1.Value = myPwd)
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.PasswordChar = "*"
End Sub


Gord