W
Woodi2
I have a userform that opens in Sheet1 if any cell between B4 and B1000 is
selected. ComboBox1 has a selection of Mech, Elect or Mech & Elect to select
from. If Mech is chosen I would like to clear the contents of the selected
cell with an offset by 10 to clear and lock.
I have found this code that I am trying to manipulate to suit but struggling
with the activeCell part of the code.
In the code below I would like to change the reference to b1 to
ActiveCell.Offset(0, 4) and reference to B10 to ActiveCell.Offset(0, 14).
Thus if the ActiveCell offset by 4 = Mech, clear the contents of offset cell
14 and lock it.
Private Sub Worksheet_Change(ByVal Target As Range)
If [B1] = "Mech" Then
ActiveSheet.Unprotect
[D10].Locked = False
[D10].Interior.ColorIndex = 34
[D10].ClearContents
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
[D10].Locked = True
[D10].Interior.ColorIndex = 0
ActiveSheet.Protect
End If
End Sub
Any Help would be appreciated.
selected. ComboBox1 has a selection of Mech, Elect or Mech & Elect to select
from. If Mech is chosen I would like to clear the contents of the selected
cell with an offset by 10 to clear and lock.
I have found this code that I am trying to manipulate to suit but struggling
with the activeCell part of the code.
In the code below I would like to change the reference to b1 to
ActiveCell.Offset(0, 4) and reference to B10 to ActiveCell.Offset(0, 14).
Thus if the ActiveCell offset by 4 = Mech, clear the contents of offset cell
14 and lock it.
Private Sub Worksheet_Change(ByVal Target As Range)
If [B1] = "Mech" Then
ActiveSheet.Unprotect
[D10].Locked = False
[D10].Interior.ColorIndex = 34
[D10].ClearContents
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
[D10].Locked = True
[D10].Interior.ColorIndex = 0
ActiveSheet.Protect
End If
End Sub
Any Help would be appreciated.