password protect ASAP

  • Thread starter Thread starter faza
  • Start date Start date
F

faza

I have suddenly been assigned a job which involves entering some data
in to this excel document.. i am very new to excel and i have gon
throught the help and everything...
i am trying to password protect certain sheets..in the main document.
i know the code to input :

Sheets("Sheet3").Protect Password:="aaa"

but the thing is,,i dont know where to input this code.. and how..
please somebody help me out...
thank
 
No code needed, click "Tools", "Protection", "Protect Sheet". Here you can
supply a password to unprotect. Keep in mind that all cells are defaulted
to be locked so if you have certain cells that need access to you will need
to unlock each cell first, then protect the sheet. To unlock a cell, click
the cell(s), click "Format", "Cells", click the "Protection" TAB and uncheck
"Locked".

Bill Foley
www.pttinc.com
 
thanks for the reply ptt...
we are actually looking for a way to hide certain sheets so that its
not even visible... until u enter a password...
we have the accounts of each month.. but we dont want everyone to see
the accounts of all the months..
any idea?

thanks
 
Floating ideas around, I don't know EXACTLY the code that would be
needed to do this, but how about some pseudocode that could then be
interpreted, or I can lookup how to do this properly when I have more
time / get home.

To Hide.

Push a button.

Pops up a box asking for a worksheet name.

The code in VBA would look something like

Worksheets(BoxValue).Visible = False

To Unhide.

Push another button.

Pops up a box asking for a worksheet name.
Pops up a box asking for a password.

Code would be something like...

DIM Passwordset= "aaa"

IF (Passwordbox name = Passwordset)
{Worksheets(BoxValue).Visible = True}
Else
Popup a box saying "Enter Correct Password"


Maybe that'll work? That's sorta how I'd do it in Access, which is
almost the same. Been a while since I've thought in VBA, so I'd have to
lookup the correct code.

Hope this helps somebody to help you. -_-'

-Bob

Edit: Oh yeah. And then password protect the VBA code.
 
Hi faza!

Hide the sheets and then use:

Tools > Protection > Protect Workbook
Give and confirm password
OK

Then hope that those who aren't supposed to see those hidden sheets
don't use the freely available subroutines and workbooks that will
break through any internal passwords that you impose in a few minutes.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
thanks for all your help..i really appreciate it.. one of my frien
actually said that we can use this code..

Sheets("Sheet3").Protect Password:="aaa"

i tried using it.. but i dont know where and how to.. enter it..???
is this worth a try.. or is this also a way to do it??
 
Hi faza!

Here are the very basics:

Sub testprotection()
Worksheets("sheet2").Visible = False
ActiveWorkbook.Protect Structure:=True, Windows:=False,
Password:="not4u2see"
End Sub

This can go as a self-contained subroutine in an ordinary module or
you could use the code in a Before_Close event handling module that
goes in ThisWorkbook.

Don't forget the password or you'll be visiting JE McGimpsey site to
pick up a copy of a workbook or code to unprotect your workbook:

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Ok, I worked out how my psuedocode would be written and have tested it
out on a basic sheet, and it does work. Here's what happens with my
code.

There is a button on a menu page called "Hide" and one called "Show"

You click Hide, and a box pops up asking you for a sheet name, and a
password. You type in the sheet name, and the password, and click ok.
The sheet then becomes hidden, and protected by the password you
typed.

Click "Unhide" and it shows what looks like the same box, bar it says
"Show". You type in a sheet name, and a password. This time, it unlocks
the workbook with the password you provided, and then makes the sheet
visible.

So, here's the code, for those interested.

UserForm1

Global Definitions

Dim formhidename As String
Dim passwordvalue As String

Private Sub CommandButton1_Click() 'ok button
formhidename = TextBox2.Text
passwordvalue = TextBox1.Text
Sheets(formhidename).Visible = False
ActiveWorkbook.Protect (passwordvalue)
End
End Sub

Private Sub CommandButton2_Click() ' cancel button
End
End Sub

UserForm2

Global Definitions

Dim formunhidename As String
Dim newpasswordvalue As String

Private Sub CommandButton1_Click() 'ok button
formunhidename = TextBox2.Text
newpasswordvalue = TextBox1.Text
ActiveWorkbook.Unprotect (newpasswordvalue)
Sheets(formunhidename).Visible = True
End
End Sub

Private Sub CommandButton2_Click() ' cancel button
End
End Sub




Limitations. It will only allow one sheet to be hidden before locking
the workbook.

*thinks*

Ooh, I know. Make it so that at the beginning of the code it checks if
the workbook is protected, then unlocks if nessecary, hides the sheet,
then relocks it. Will post solution later.

-Bob
 
Changes to UserForm1

Private Sub CommandButton1_Click() 'ok button
formhidename = TextBox2.Text
passwordvalue = TextBox1.Text
If (ActiveWorkbook.ProtectStructure = True) Then
ActiveWorkbook.Unprotect (passwordvalue)
Sheets(formhidename).Visible = False
If (ActiveWorkbook.ProtectStructure = False) Then
ActiveWorkbook.Protect (passwordvalue)
End
End Sub


Changes to UserForm2

Private Sub CommandButton1_Click() 'ok button
formunhidename = TextBox2.Text
newpasswordvalue = TextBox1.Text
If (ActiveWorkbook.ProtectStructure = True) Then
ActiveWorkbook.Unprotect (newpasswordvalue)
Sheets(formunhidename).Visible = True
If (ActiveWorkbook.ProtectStructure = False) Then
ActiveWorkbook.Protect (newpasswordvalue)
End
End Sub

Workbook structure is always protected with this method, can be
manually unlocked if needed.

Hope that helps, it was interesting to start thinking in VBA terms
again. Was a great help to me.

-Bob
 
Back
Top