Hi guys,

Can I lock or unlock a column, row or cell(s) dynamically depending on the
content of another cell? I have a drop down list where a user chooses
country, if it is the US then i want them to be able to imput address,
otherwise I want the adress columns to be locked.
--

Thanks in advance,
Matt

Hi Matt

this code will unlock the cell in column E of the same row that "US" is
input into in column A ... then if another change is made anywhere on the
sheet the whole of column E will be locked again ...

--------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Value = "US" Then
ActiveSheet.Unprotect ("pwd")
Target.Offset(0, 4).Locked = False
ElseIf Target.Column < 1 Then
ActiveSheet.Unprotect ("pwd")
Columns("E:E").Locked = True
ActiveSheet.Protect ("pwd")
End If

End Sub
--

to use the code, right mouse click on the sheet tab against which you want
to run the code and choose view code - the VBE window will be displayed.
Copy & paste the code above into the right hand side of the screen ...
change the 1 to the appropriate number of the column (A = 1, B= 2 etc) where
the countries are listed
change "E:E" to the column where the addresses are entered
also change Offset(0,4) to match the above to settings ... Offset(0,4) means
that the address cell is on the same row but 4 columns to the right of the
country cell.
change
pwd
to the actual password you want to protect your sheet with

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"MaBell" wrote in message
...
Hi guys,

Can I lock or unlock a column, row or cell(s) dynamically depending on the
content of another cell? I have a drop down list where a user chooses
country, if it is the US then i want them to be able to imput address,
otherwise I want the adress columns to be locked.
--

Thanks in advance,
Matt

Great ! Thanks!

"JulieD" wrote:

Hi Matt

this code will unlock the cell in column E of the same row that "US" is
input into in column A ... then if another change is made anywhere on the
sheet the whole of column E will be locked again ...

--------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Value = "US" Then
ActiveSheet.Unprotect ("pwd")
Target.Offset(0, 4).Locked = False
ElseIf Target.Column < 1 Then
ActiveSheet.Unprotect ("pwd")
Columns("E:E").Locked = True
ActiveSheet.Protect ("pwd")
End If

End Sub
--

to use the code, right mouse click on the sheet tab against which you want
to run the code and choose view code - the VBE window will be displayed.
Copy & paste the code above into the right hand side of the screen ...
change the 1 to the appropriate number of the column (A = 1, B= 2 etc) where
the countries are listed
change "E:E" to the column where the addresses are entered
also change Offset(0,4) to match the above to settings ... Offset(0,4) means
that the address cell is on the same row but 4 columns to the right of the
country cell.
change
pwd
to the actual password you want to protect your sheet with

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"MaBell" wrote in message
...
Hi guys,

Can I lock or unlock a column, row or cell(s) dynamically depending on the
content of another cell? I have a drop down list where a user chooses
country, if it is the US then i want them to be able to imput address,
otherwise I want the adress columns to be locked.
--

Thanks in advance,
Matt

you're welcome
--
Cheers
JulieD

"MaBell" wrote in message
...
Great ! Thanks!

"JulieD" wrote:

Hi Matt

this code will unlock the cell in column E of the same row that "US" is
input into in column A ... then if another change is made anywhere on the
sheet the whole of column E will be locked again ...

--------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Value = "US" Then
ActiveSheet.Unprotect ("pwd")
Target.Offset(0, 4).Locked = False
ElseIf Target.Column < 1 Then
ActiveSheet.Unprotect ("pwd")
Columns("E:E").Locked = True
ActiveSheet.Protect ("pwd")
End If

End Sub
--

to use the code, right mouse click on the sheet tab against which you
want
to run the code and choose view code - the VBE window will be displayed.
Copy & paste the code above into the right hand side of the screen ...
change the 1 to the appropriate number of the column (A = 1, B= 2 etc)
where
the countries are listed
change "E:E" to the column where the addresses are entered
also change Offset(0,4) to match the above to settings ... Offset(0,4)
means
that the address cell is on the same row but 4 columns to the right of
the
country cell.
change
pwd
to the actual password you want to protect your sheet with

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"MaBell" wrote in message
...
Hi guys,

Can I lock or unlock a column, row or cell(s) dynamically depending on
the
content of another cell? I have a drop down list where a user chooses
country, if it is the US then i want them to be able to imput address,
otherwise I want the adress columns to be locked.
--

Thanks in advance,
Matt

OK, what happens if the user wrongly enters "US" in the Country column, then
enters the address, then realizes that they meant to enter "UK" for the
country? The address that they entered is now locked into the Address column
on that row, and can only be changed by temporarily changing the Country
back to "US", then back to "UK" again!
--
Regards,
Bill


"MaBell" wrote in message
...
Hi guys,

Can I lock or unlock a column, row or cell(s) dynamically depending on the
content of another cell? I have a drop down list where a user chooses
country, if it is the US then i want them to be able to imput address,
otherwise I want the adress columns to be locked.
--

Thanks in advance,
Matt