Locking of cells dependent on which option bottoms pushed...

[nco]

Hi Super Excel users

I have a pretty straight forward question.

Is it possible to lock cells dependent of which option bottoms being pushed by the user?

Example:
I have three option bottoms:
optionBottom_1, optionBottom_2, optionBottom_3

And I have three input cells where two should be open for inputs depending on the choice of the option bottoms.

Example:
If optionBottom_1 is pushed then a1 and b1 should be open for inputs and c1 should be locked for inputs.

Likewise if optionBottom_2 is pushed by the user, a1 and c1 should be open for inputs and b1 locked for inputs.


Hope someone can help me out here.

Regards
NCO
Denmark

 

[acw]

Hi

How about
Private Sub OptionButton1_Click()
Range("a1").Select
ActiveSheet.Unprotect
Range("A1:A2").Locked = False
Range("a3").Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Private Sub OptionButton2_Click()
Range("a1").Select
ActiveSheet.Unprotect

Range("A1").Locked = False
Range("A3").Locked = False
Range("a2").Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Private Sub OptionButton3_Click()
Range("a2").Select
ActiveSheet.Unprotect

Range("A2:A3").Locked = False
Range("a1").Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


Tony

 

[Nimrod]

Sub OptionButton1_Click()
ProtectCell ("c1")
End Sub

Private Sub OptionButton2_Click()
ProtectCell ("B1")
End Sub

Private Sub OptionButton3_Click()
ProtectCell ("A1")
End Sub

Private Sub ProtectCell(CellToLock)
ActiveSheet.Unprotect
Range("A1:C1").Locked = False
Range(CellToLock).Locked = True
ActiveSheet.Protect
End Sub

 

[nco]

Hi Nimrod

Thanks for your fine lines of coding almost art....
I prefer your lines in favour of acw's lines.

But unfortunately as I tried to implement your code, my other lines of coding stoped to working and now I can't get them to work again no matter if I out-comment your lines.

I have attached the lines below, the code execution stops at every line

".Range("a1").Interior.ColorIndex = 4" in all three Private Sub OptionButton(s), 1,2,3.

Do you or anyone else have any idea why this happens? I have closed the Excel sheet and started it up again but the same thing happens every time.



Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
'********************************
'Shape & scale parameter as input
'********************************
'
With Worksheets("Power_production").Range("input_cells")
.Range("a1").Interior.ColorIndex = 4
.Range("b1").Interior.ColorIndex = 4
.Range("c1").Interior.ColorIndex = xlNone
'.Border.ColorIndex = 5
End With
'
'ProtectCell ("c1")
'
Application.ScreenUpdating = True
End Sub
'
'
Private Sub OptionButton2_Click()
Application.ScreenUpdating = False
'******************************************
'Shape parameter & mean wind speed as input
'******************************************
'
With Worksheets("Power_production").Range("input_cells")
.Range("a1").Interior.ColorIndex = 4
.Range("b1").Interior.ColorIndex = xlNone
.Range("c1").Interior.ColorIndex = 4
'.Border.ColorIndex = 5
End With
'
'ProtectCell ("b1")
'
Application.ScreenUpdating = True
End Sub
'
'
Private Sub OptionButton3_Click()
Application.ScreenUpdating = False
'******************************************
'Scale parameter & mean wind speed as input
'******************************************
'
With Worksheets("Power_production").Range("input_cells")
.Range("a1").Interior.ColorIndex = xlNone
.Range("b1").Interior.ColorIndex = 4
.Range("c1").Interior.ColorIndex = 4
'.Border.ColorIndex = 5
End With
'
'ProtectCell ("a1")
'
Application.ScreenUpdating = True
End Sub
'
'
'Private Sub ProtectCell(CellToLock)
' ActiveSheet.Unprotect
' Range("input_cells").Locked = False
' Range("input_cells").Range(CellToLock).Locked = True
' ActiveSheet.Protect
'End Sub


NCO

 

[Nimrod]

The problem was that you were attempting to change a colors cell while the sheet was protected.
.... try something along these lines ... :wink:

Private Sub OptionButton1_Click()
'********************************
'Shape & scale parameter as input
'********************************
ProtectCell ("c1")
End Sub

Private Sub OptionButton2_Click()
'******************************************
'Shape parameter & mean wind speed as input
'******************************************
ProtectCell ("b1")
End Sub

Private Sub OptionButton3_Click()
'******************************************
'Scale parameter & mean wind speed as input
'******************************************
ProtectCell ("a1")
End Sub

Private Sub ProtectCell(CellToLock)
Application.ScreenUpdating = False
With Worksheets("Power_production")
.Activate
.Unprotect
.Range("input_cells").Interior.ColorIndex = 4
.Range(CellToLock).Interior.ColorIndex = xlNone
.Range("input_cells").Locked = False
.Range(CellToLock).Locked = True
.Protect
End With
Application.ScreenUpdating = True
End Sub

 

[nco]

Protect cells - VBA - code......

Hi Nimrod

Thanks for your smart proposal.
I think the code is really smart and it works fine.
I only had to make two minor changes, (maked with bold) in the Sub ProtectCell.
How long time have you been coding in Excel VBA? Because I would like to know how long time it take to achive your level.

Private Sub OptionButton1_Click()
'********************************
'Shape & scale parameter as input
'********************************
ProtectCell ("c1")
End Sub
'
Private Sub OptionButton2_Click()
'******************************************
'Shape parameter & mean wind speed as input
'******************************************
ProtectCell ("b1")
End Sub
'
Private Sub OptionButton3_Click()
'******************************************
'Scale parameter & mean wind speed as input
'******************************************
ProtectCell ("a1")
End Sub
'
Private Sub ProtectCell(CellToLock)
Application.ScreenUpdating = False
With Worksheets("Power_production")
.Activate
.Unprotect
.Range("input_cells").Interior.ColorIndex = 4
.Range("input_cells").Range(CellToLock).Interior.ColorIndex = xlNone
.Range("input_cells").Locked = False
.Range("input_cells").Range(CellToLock).Locked = True
.Protect
End With
Application.ScreenUpdating = True
End Sub

Thanks a lot
NCO

 

[nco]

Protect cells - VBA - code......

Hi Nimrod

Thanks for your smart proposal.
I think the code is really smart and it works fine.
I only had to make two minor changes, (maked with bold) in the Sub ProtectCell.
How long time have you been coding in Excel VBA? Because I would like to know how long time it take to achive your level.

Private Sub OptionButton1_Click()
'********************************
'Shape & scale parameter as input
'********************************
ProtectCell ("c1")
End Sub
'
Private Sub OptionButton2_Click()
'******************************************
'Shape parameter & mean wind speed as input
'******************************************
ProtectCell ("b1")
End Sub
'
Private Sub OptionButton3_Click()
'******************************************
'Scale parameter & mean wind speed as input
'******************************************
ProtectCell ("a1")
End Sub
'
Private Sub ProtectCell(CellToLock)
Application.ScreenUpdating = False
With Worksheets("Power_production")
.Activate
.Unprotect
.Range("input_cells").Interior.ColorIndex = 4
.Range("input_cells").Range(CellToLock).Interior.ColorIndex = xlNone
.Range("input_cells").Locked = False
.Range("input_cells").Range(CellToLock).Locked = True
.Protect
End With
Application.ScreenUpdating = True
End Sub

Thanks a lot
NCO