weird
 

Hello,
I have a table in excel97 in which data is entered
downwards in columns. I am trying to set it so that when
the bottom of one column is reached then the first cell of
the next column is automatically activated.

The code I've tried so far is below, but I get a weird
response in that the code executes when clicking any cell
on the sheet, even when the active cell is clearly not one
of the ones specified.

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Select Case ActiveCell
Case [a4] 'bottom of first column
[b1].Activate 'top of next column
Case [b4]
[c1].Activate
End Select
End Sub

weird
 

The default property for a range object is the value
property. The statement "Activecell" returns the range
refering, of course, to the active cell. The statement
"Select Case ActiveCell", since it does not specify a
property, by default references the value property of the
active cell. The same for the [a4] and [b4] references. So
your code actually reads like:

Select Case ActiveCell.Value
Case [a4].Value 'Is the same as cell A4's value
[B1].Activate
Case [b4].Value 'Is the same as cell B4's value
[C1].Activate
End Select

Since the value in cell A4 is likely blank or non-numeric,
any time you select a blank or non-numeric cell it will
activate cell B1.

Suggested change:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Select Case ActiveCell.Address
Case "$A$4" 'bottom of first column
[B1].Activate 'top of next column
Case "$B$4"
[C1].Activate
End Select
End Sub

Regards,
Greg


-----Original Message-----
Hello,
I have a table in excel97 in which data is entered
downwards in columns. I am trying to set it so that when
the bottom of one column is reached then the first cell
of
the next column is automatically activated.

The code I've tried so far is below, but I get a weird
response in that the code executes when clicking any cell
on the sheet, even when the active cell is clearly not
one
of the ones specified.

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Select Case ActiveCell
Case [a4] 'bottom of first column
[b1].Activate 'top of next column
Case [b4]
[c1].Activate
End Select
End Sub

.

weird
 

Thanks for explaining that. Makes sense and now works.
-----Original Message-----
The default property for a range object is the value
property. The statement "Activecell" returns the range
refering, of course, to the active cell. The statement
"Select Case ActiveCell", since it does not specify a
property, by default references the value property of the
active cell. The same for the [a4] and [b4] references.
So
your code actually reads like:

Select Case ActiveCell.Value
Case [a4].Value 'Is the same as cell A4's value
[B1].Activate
Case [b4].Value 'Is the same as cell B4's value
[C1].Activate
End Select

Since the value in cell A4 is likely blank or non-
numeric,
any time you select a blank or non-numeric cell it will
activate cell B1.

Suggested change:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Select Case ActiveCell.Address
Case "$A$4" 'bottom of first column
[B1].Activate 'top of next column
Case "$B$4"
[C1].Activate
End Select
End Sub

Regards,
Greg


-----Original Message-----
Hello,
I have a table in excel97 in which data is entered
downwards in columns. I am trying to set it so that when
the bottom of one column is reached then the first cell
of
the next column is automatically activated.

The code I've tried so far is below, but I get a weird
response in that the code executes when clicking any
cell
on the sheet, even when the active cell is clearly not
one
of the ones specified.

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Select Case ActiveCell
Case [a4] 'bottom of first column
[b1].Activate 'top of next column
Case [b4]
[c1].Activate
End Select
End Sub

.

.