Locking cells after the user has entered values in protected works
 

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("D6:D23").Select
Selection.Locked = True
ActiveSheet.Protect "ABC"
End Sub

Can someone advise me what changes I should make?
Thanks

Locking cells after the user has entered values in protected works
 

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


On Thu, 13 Dec 2007 13:20:01 -0800, greencecil
wrote:

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("D6:D23").Select
Selection.Locked = True
ActiveSheet.Protect "ABC"
End Sub

Can someone advise me what changes I should make?
Thanks

Locking cells after the user has entered values in protected w
 

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" wrote:

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


On Thu, 13 Dec 2007 13:20:01 -0800, greencecil
wrote:

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("D6:D23").Select
Selection.Locked = True
ActiveSheet.Protect "ABC"
End Sub

Can someone advise me what changes I should make?
Thanks

Locking cells after the user has entered values in protected w
 

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("D6:D23").Select will be referring to the sheet from which the macro was
run, not Worksheet 1


Gord

On Tue, 18 Dec 2007 14:18:01 -0800, greencecil
wrote:

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" wrote:

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


On Thu, 13 Dec 2007 13:20:01 -0800, greencecil
wrote:

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("D6:D23").Select
Selection.Locked = True
ActiveSheet.Protect "ABC"
End Sub

Can someone advise me what changes I should make?
Thanks

Locking cells after the user has entered values in protected w
 

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

"Gord Dibben" wrote:

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("D6:D23").Select will be referring to the sheet from which the macro was
run, not Worksheet 1


Gord

On Tue, 18 Dec 2007 14:18:01 -0800, greencecil
wrote:

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" wrote:

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


On Thu, 13 Dec 2007 13:20:01 -0800, greencecil
wrote:

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("D6:D23").Select
Selection.Locked = True
ActiveSheet.Protect "ABC"
End Sub

Can someone advise me what changes I should make?
Thanks