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

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

"Rob" wrote:

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

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

"Barb Reinhardt" wrote:

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

"Rob" wrote:

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

Change

Set myColumn = Columns(8)

Let me know how that works.

"Rob" wrote:

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

"Barb Reinhardt" wrote:

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

"Rob" wrote:

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

AWESOME It Works!!!

Thanks sooo much!!!

"Barb Reinhardt" wrote:

Change

Set myColumn = Columns(8)

Let me know how that works.

"Rob" wrote:

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

"Barb Reinhardt" wrote:

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

"Rob" wrote:

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

Rob

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


Gord Dibben MS Excel MVP

On Tue, 5 Jun 2007 07:24:01 -0700, Rob wrote:

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

"Barb Reinhardt" wrote:

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

"Rob" wrote:

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