Please help! VBA Checkbox to Lock & Gray Out Cells

[Bailey0926]

I can't see the forest through the trees on this one. My spreadsheet is designed to calculate waiting periods between each step of a multi-step process, i.e. Person A makes an appointment with GP, date of appointment, waiting time? Referral to specialist, date of appointment, waiting time? Diagnosis, date of procedure, waiting time. I am sure you get the drift.


My waiting time calculations utilise the NETWORKDAYS function - 1 and eliminate outliers created by empty cells with =IF(AND(F16<366,0<F16),F16,0)< p>
I have a scenario for which the person receives the proverbial "golden ticket" and gets to skip a step in the progression. I have created a column of ActiveX checkboxes (Checkbox 1-48) in Column H with the True/False outcomes offset to Column I.

What I am trying to accomplish is if data is being entered in Row 42 for a person with this proverbial "golden ticket," the user will tick the check box locking the corresponding cells in Cells in J42 through O42 will lock so they the user cannot input data and gray themselves out to visually denote this cell is empty and locked for a reason.

I did take a stab at it myself, but when I ran it absolutely nothing happened. After 8 hours of googling, tutorials, books, etc. I am still no further than a bit of VBA I half understand and does sweet f*** all.

Private Sub CheckBox1_Click()
Dim StartCell As Range
Set StartCell = ActiveCell
Range("J42:O42").Select
If CheckBox1.Value = True Then
Selection.Locked = True
Else
Selection.Locked = False
End If
StartCell.Activate
Set StartCell = Nothing
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = 808080
End With
Selection.FormatConditions(1).StopIf CheckBox1.Value = False
End Sub

I'm far from fluent in VBA, so if you could explain it for someone in kindy that would be helpful. I'm working in Excel 2007 Compatability Mode and the system this will run on still has 2003.

I deeply appreciate any assistance offered! Cheers, B

 

[RolfJ]

I have two comments that might be helpful:

1. Locking cells has no effect unless the worksheet is protected (via Tools | Protection)
2. The two statements before the statement that is intended to gray out the selection undoes the selection.
3. The code I would use for the graying out is a bit different from yours:
   
   

       With Selection.Interior
           .ColorIndex = 15
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
       End With

Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com