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

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

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

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

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