Restricting A Command To Certain Ranges

[Guest_imported]

Hello,

Can anyone tell me how to restrict the following command to columns D, G, J, M, P, S from Row 8 onwards with a warning message should someone try otherwise.

I,m unable to protect the worksheet so anyone's help would be welcome:

Private Sub Image1_Click()
ActiveCell.FormulaR1C1 = "P1"
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveCell.Offset(1, 0).Select
End Sub

Cheers,

Andrew

 

[dwilson01]

This should do the trick, 4,7,10,13,16 and 19 represent D,G,J,M,P and S respectively.

Private Sub Image1_Click()

If ActiveCell.Row >= 8 Then
  Col = ActiveCell.Column
  Select Case Col
  
  Case 4, 7, 10, 13, 16, 19
    'Column D, G, J, M, P, or S is selected
    ActiveCell.FormulaR1C1 = "P1"
    With Selection.Interior
      .ColorIndex = 36
      .Pattern = xlSolid
    End With
    ActiveCell.Offset(1, 0).Select
  Case Else
    'Column selected is not D, G, J, M, P or S
    MsgBox "You are trying to change unauthorized cells"
  End Select
'Row selected is less than 8
Else: MsgBox "You are trying to change unathorized cells"
End If

End Sub

Would be great to hear if this works or not.

Dave

 

[Guest_imported]

Cheers Dave

It works a treat - i don,t know how you guys do it!

Thanks

Andrew