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.

Hi,

I'm not entirely sure I understand the question or where a userform comes in
it but how about this

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = "Mech" Then
Application.EnableEvents = False
ActiveSheet.Unprotect
With ActiveCell
.Offset(, 10).Locked = False
.Offset(, 10).Interior.ColorIndex = 34
.Offset(, 10).ClearContents
End With
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
With ActiveCell
.Offset(, 10).Locked = True
.Offset(, 10).Interior.ColorIndex = xlNone
ActiveSheet.Protect
End With
Application.EnableEvents = True
End If
End Sub

Mike

"Woodi2" wrote:

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.

Thanks Mike.
The userfrom is under seperate code, but basically opens if any cell in B4
to B1000 is selected. A nujber of textbox's are populated and then entered
into the worksheet. If say cell B6 is selected, the uerform enters the data
offset from cell B6, if it was cell B10 it would offset the data from cell
B10.
Thus I need some code where there is no reference to a particular cell,
hence removing B1 and replacing with ActiveCell (somehow).

"Mike H" wrote:

Hi,

I'm not entirely sure I understand the question or where a userform comes in
it but how about this

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = "Mech" Then
Application.EnableEvents = False
ActiveSheet.Unprotect
With ActiveCell
.Offset(, 10).Locked = False
.Offset(, 10).Interior.ColorIndex = 34
.Offset(, 10).ClearContents
End With
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
With ActiveCell
.Offset(, 10).Locked = True
.Offset(, 10).Interior.ColorIndex = xlNone
ActiveSheet.Protect
End With
Application.EnableEvents = True
End If
End Sub

Mike

"Woodi2" wrote:

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.