Dynamically Activating Cells

[Guest]

Hi,

I have this code in the "ThisWorkbook" section...

Private Sub Workbook_Open()
Worksheets("Main").ScrollArea = "H8:H172"
End Sub

Now what I want to be able to do is to not let Range("H9") be usable until
Range("H8") is filled in. Then Subsiquently each other following cell
depending on the previous cell.

Is this possible or is it a lost cause? I've asked some friends but so far
nobody has been able to help with this.

Thanks Much In Advance.
Rob

 

[Guest]

I think I'd do this with a worksheet change event. Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myColumn As Range

Set myColumn = Columns(1)
If Not Intersect(Target, myColumn) Is Nothing Then
Target.Parent.Unprotect
lrow = Cells(Rows.Count, Target.Column).End(xlUp).Row
With Cells(lrow + 1, Target.Column)
.Locked = False
.Select
End With
Target.Parent.Protect
End If
End Sub

 

[Guest]

Thanks so much for the reply. I tried the code and it certainly locks the
cells but when I input information in the home cell (H8) it doesn't unlock
cell H9 so that I can put information into it next.

Any Ideas or did I do something wrong?

Thanks Sooo Very Much!
Rob

 

[Guest]

Change

Set myColumn = Columns(8)

Let me know how that works.

 

[Guest]

AWESOME It Works!!!

Thanks sooo much!!!

Change

Set myColumn = Columns(8)

Let me know how that works.

 

[Gord Dibben]

Rob

Change Set myColumn = Columns(1) to Columns(8)


Gord Dibben MS Excel MVP