Locking cells after the user has entered values in protected works

[greencecil]

Hi,

I am trying to write a macro that asks a user if they are sure they have
entered the right data, when they answer yes, I want the macro to lock cells
in another spreadsheet. This spreadsheet is password protected, so the user
can only enter data into the cells I now wish to lock. The password example I
give here is ABC. I have written the following code, but get errors.

Sub LockValues()

' Lock the values entered in cells D6 to D23 in Worksheet 1
If MsgBox("Are you sure your entries are " _
& "correct and complete?", vbYesNo) = vbYes _
Then Sheets("Worksheet 1").Select
ActiveSheet.Unprotect Password:="ABC"
Range("D623").Select
Selection.Locked = True
ActiveSheet.Protect "ABC"
End Sub

Can someone advise me what changes I should make?
Thanks

 

[Gord Dibben]

Works for me.

What are the errors you get?

Type and line if possible.

Do you have a Worksheet 1.........note the <space>


Gord Dibben MS Excel MVP

 

[greencecil]

I get a Microsoft Visual Basic window, that has a red cross and the number
400 next to it, with the buttons 'ok' and 'Help' under. When I click on help
a window comes up, but with no contents.
Does this give you any clues about the problem?

 

[Gord Dibben]

Where do you have the macro running from?

It should be in a general module.

If in a sheet module other than Worksheet 1 module it will error out because

Range("D623").Select will be referring to the sheet from which the macro was
run, not Worksheet 1


Gord

 

[greencecil]

Yep, there you have it. I wrote it in the sheet module. Thanks!