Protecting sheets in excel using macros 1

[springy2003]

Hi, I am sure this can be done. I want to be able to protect a sheet, and at the same time, using a password. The current macro I have is as follows

Sub Protect()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

but the thing is, it only protects the sheets, but it doesn't use a password. Is there anyway to bring up a box so that a user can type in their own password. Thanks so much!

 

[xlbo]

mPass = inputbox("Enter Password:&quot
activesheet.protect password:=mPass

should do the trick Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

Hiya,

If all you want is for your users to type a password, you could either:

- use

Application.Dialogs(xlDialogProtectDocument).Show

to bring up the built-in protection dialog box
- use an inputbox to get the password & use the code above to protect the sheet using the password they gave:

Sub ProtectSheet(p_wksSheet As WorkSheet)
  Dim l_sPassWord as String
  Dim l_sPassWordCheck as String

  l_sPassWord = InputBox("Please Enter Password")
  l_sPassWordCheck = InputBox("Please Enter Password")

  'Get password TWICE to check for correct spelling
  If l_sPassWord<>l_sPassWordCheck Then 
    MsgBox &quot;ERROR - you entered two different passwords! The sheet will not be protected&quot; & vlLF & &quot;Please try again.&quot;, vbOkOnly+vbCritical
    Exit Sub
  End If
  
  'Protect sheet
  p_wksSheet.Protect l_sPassWord, True, True, True
End Sub

HTH

Cheers
Nikki

 

[Nikita6003]

btw - use

Call ProtectSheet(ActiveSheet)

to use the second option ...

sorry - must have had 2 much beer ;-)

Cheers
Nikki

 

[springy2003]

Thanks so much for the help guys. You're the best. One last question though, is there a way, so when you enter the password, you give ***** instead of letters. Thanks so much again!!!

 

[Nikita6003]

Mornin' all (well it's morning over here anyway)

You can't mask input in an inputbox - but you *can* create your own userform, add a textbox & set the PasswordChar poroperty of the textbox to "*"
See this MS link for some more info:

http://msdn.microsoft.com/library/d...eovrcontrollingmacroexecutionwithpassword.asp

If you need more help let us know ;-)

Cheers
Nikki

 

[springy2003]

Thanks again guys!!!
One last help I need. I found the problem last night, nothing to do with the password thing you gave to me, but it is to do with sheet protecting.

When I protect a sheet, I know you can't enter data, unless you format the cell not to be locked. But what I am trying to do, is when I enter data from another sheet into the protected sheet, into the cells that are not locked, I want a macro to select the cells that has just been entered and then re-format the cells so that it is now locked. I can lock the first cell selected, but after that, I don't know how to lock the rest of the cells under that. This is what my marco looks like at the moment.

Sub Automatic()
'Application.ScreenUpdating = False

Dim Name, Name1, Name2, Name3, Name4
Sheets("Sheet1&quot.Select

Range("D4&quot.Select
Name = ActiveCell.Offset(1, 0).Value
Name1 = ActiveCell.Offset(2, 0).Value
Name2 = ActiveCell.Offset(3, 0).Value
Name3 = ActiveCell.Offset(4, 0).Value
Name4 = ActiveCell.Offset(5, 0).Value

For x = 1 To 5
ActiveCell.Offset(0, x).Value = ""
Next x

Sheets("Sheet2&quot.Select
myrow = ActiveCell.Row
mycol = ActiveCell.Column
ActiveCell.Offset(-myrow + 5, -mycol + 6).Select


Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop
'locking cells
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Value = Name
ActiveCell.Offset(1, 0).Value = Name1
ActiveCell.Offset(2, 0).Value = Name2
ActiveCell.Offset(3, 0).Value = Name3
ActiveCell.Offset(4, 0).Value = Name4


Sheets("Sheet1&quot.Select
'Application.ScreenUpdating = False

End Sub
-------------

Is there a way of doing this, or can this be done???
Thanks again for all the help I am recieving. You have been just a big help to me.

Rich.

 

[Nikita6003]

Hiya,

you've got several options, seeing you're actually updating the sheet in code & you therefore *know* which cells need Locked set to true. The fastest way (i.e. one line of code ;-)) is probably if you add this line after assigning the values to the ActiveCell & its offsets:

Range(ActiveCell, ActiveCell.Offset(4, 0)).Locked = True

Just a question: is there a reason for copying the cells this way? I mean storing the values in vaiables then assigning the variables to the cells on Sheet2?

Cheers
Nikki

 

[springy2003]

I want it to do this because I want to be able to store all the data in one sheet, where it can't be touch, and I want one sheet where I can transfer the data. It is making the system very user friendly, so that it won't take long to scroll to where I want to input the data and so on.

Anyway, that code you gave me doesn't work. It came up with the same error I found yesterday night.
------------
Run-time error '1004':

Method 'Range' of object '_worksheet' failed

-----------------------

Thanks again for taking your time to help me.

 

[xlbo]

You have to unprotect the spreadsheet 1st to be able to change the "Locked" property of ANY of the cells Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

Hiya,

The code works fine for me (always test before I post ;-)) ... and it's a bit hard to find what's going wrong when I can't see the file

Having said that, the error points to Excel picking up an impossible range - so could you let me know which line of code the error is on?

Alternatively, if the sheets don't contain restricted data, send me the file on Nikita6003@hotmail.com & I'll take a look

Cheers
Nikki

 

[springy2003]

This is what the code looks like when I have put the
Range(ActiveCell, ActiveCell.Offset(4, 0)).Locked = True
in

Sub Automatic()
'Application.ScreenUpdating = False

Dim Name, Name1, Name2, Name3, Name4
Sheets("Sheet1&quot.Select

Range("D4&quot.Select
Name = ActiveCell.Offset(1, 0).Value
Name1 = ActiveCell.Offset(2, 0).Value
Name2 = ActiveCell.Offset(3, 0).Value
Name3 = ActiveCell.Offset(4, 0).Value
Name4 = ActiveCell.Offset(5, 0).Value

For x = 1 To 5
ActiveCell.Offset(0, x).Value = ""
Next x

Sheets("Sheet2&quot.Select
myrow = ActiveCell.Row
mycol = ActiveCell.Column
ActiveCell.Offset(-myrow + 5, -mycol + 6).Select


Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop
'locking cells
Selection.Locked = True
Selection.FormulaHidden = False
ActiveCell.Value = Name
ActiveCell.Offset(1, 0).Value = Name1
ActiveCell.Offset(2, 0).Value = Name2
ActiveCell.Offset(3, 0).Value = Name3
ActiveCell.Offset(4, 0).Value = Name4

Range(ActiveCell, ActiveCell.Offset(4, 0)).Locked = True

Sheets("Sheet1&quot.Select
'Application.ScreenUpdating = False

End Sub

-----------------------

The bit in bold is where the error is occuring with the

------------
Run-time error '1004':

Method 'Range' of object '_worksheet' failed
------------

which again, last night when I try lots of codes was the exact same error.

 

[Nikita6003]

Hi again

as a fudge, try this instead:

    ActiveCell.Value = Name
    ActiveCell.Offset(1, 0).Locked = True
    ActiveCell.Value = Name1
    ActiveCell.Offset(1, 0).Locked = True
    ActiveCell.Offset(2, 0).Value = Name2
    ActiveCell.Offset(2, 0).Locked = True
    ActiveCell.Offset(3, 0).Value = Name3
    ActiveCell.Offset(3, 0).Locked = True
    ActiveCell.Offset(4, 0).Value = Name4
    ActiveCell.Offset(4, 0).Locked = True

But the code works for me in a test sheet where I've fudged som data in column D on Sheet1. Copied over to sheet2 without errors.
And if your ActiveCell.Offset(4, 0) is working OK, I haven't the faintest why mine isn't ...

Anyway, give the fudge a try & if you still have problems let me know; alternatively, send me the file OR try this:

ActiveCell.Offset(4, 0).Value = Name4
    MsgBox "Active cell address = " & ActiveCell.Address
    MsgBox "4 Rows down of Active cell address = " & ActiveCell.Offset(4, 0).Address
    Range(ActiveCell, ActiveCell.Offset(4, 0)).Locked = True

This'll give you 2 messageboxes with the Cell addresses of the range we're trying to lock

Cheers
Nikki

 

[xlbo]

I still say that you need to unprotect the sheet BEFORE you can lock or unlock cells Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[springy2003]

xlbo, it need to be unprotected to select which cells need to be locked or not locked, but when the sheet is protected, the cells at are not locked can be changed, and when it is still locked you can actually tell the computer to lock it, since I try it by highlighting it and using a macro, but it isn't user friendly.

Anyway, Nikita6003, thanks so much. It now works, but only the first once where you select the cell one by one and then lock it. The second one, where you use the msg box, I try that for the first set of code you gave me, but it didn't work. But you have been a real help. Also, in the

Selection.Locked = True
ActiveCell.Value = Name
ActiveCell.Offset(1, 0).Locked = True
ActiveCell.Value = Name1
ActiveCell.Offset(1, 0).Locked = True
ActiveCell.Offset(2, 0).Value = Name2
ActiveCell.Offset(2, 0).Locked = True
ActiveCell.Offset(3, 0).Value = Name3
ActiveCell.Offset(3, 0).Locked = True
ActiveCell.Offset(4, 0).Value = Name4
ActiveCell.Offset(4, 0).Locked = True

you have to put that bit of code in to lock the first cell as well, but thanks for everything else. Thanks!!

Rich!

 

[Nikita6003]

xlbo - look at the error message Rich posted:
Run-time error '1004':

Method 'Range' of object '_worksheet' failed

It's a range problem rather than a protection problem - or he'd get tbe Unable to set the Locked property of the Range class error. My guess is there's no protection for this sheet (might just be protection on sheet1)

Rich - which xl version are you using? (& I'm only asking coz' it's an annoying little problem & I hate annoying little problems coz they tend to pop up at annoying times)

Cheers
Nikki

 

[springy2003]

Excel 97.
I try it and yes, you have to unprotect the sheet first and then re-protect it back. I just worked it out and now it is working fine again.

 

[xlbo]

tum te tum te tum.........

Nikki - u are correct about the error msg but my point is still valid in that this code is meant to be run on a protected spreadsheet and therefore will need to be unprotected 1st

Springy - r u sure that the error message you posted is correct as if unprotecting the spreadsheet worked, you should've got the:
Unable to set the Locked property of the Range class
error as Nikki stated ....... Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[springy2003]

Nope, I didn't get any error like that with some Locked property. The error that I got is diffinately the one I wrote here, since I spent the whole night yesterday looking at it. HEHE.

But I think I know where you are coming from, Geoff. You are saying that you can't format a cell when the sheet is protected, yup thats right, (ignore what I said in previous post that you can do it, I must be stupid or something to say that). So I try to do lock protecting cells thing when the sheet is unprotected. Then in the same macro, when the macro has finish formating the cell, I protect it after that. Now it is working fine!

Thanks again for everyone who helped me, Nikki and Geoff

 

[xlbo]

Nikki - any ideas - right solution....wrong error message..... Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur