Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

Use a Worksheet event:

Private Sub Worksheet_Activate()
Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub

--
CFS


"SangelNet" wrote:

Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

That only works if there are no blank cells above the last cell with
something in it.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"CFS" wrote in message
...
Use a Worksheet event:

Private Sub Worksheet_Activate()
Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub

--
CFS


"SangelNet" wrote:

Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

On Jan 22, 3:07*pm, CFS wrote:
Use a Worksheet event:

Private Sub Worksheet_Activate()
* * Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub

--
CFS

"SangelNet" wrote:
Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

thank you it worked. had to change the xlup for xldown.

thnx again.

You can setup within the Activate event of the worksheet itself to use the
following code:

If ActiveSheet.Range("A1").Formula = "" Then
ActiveSheet.Range("A1").Select
Elseif ActiveSheet.Range("A2").Formula = "" Then
ActiveSheet.Range("A2").Select
Else
ActiveSheet.Range("A1").End(xlDown).Select
If Selection.Row < ActiveSheet.Rows.Count Then
Selection.Offset(1,0).Select
End If
End If

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"SangelNet" wrote in message
...
Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

Try this worksheet event code...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").End(xlDown).Offset(1).Activate
End Sub

To implement it, right click the icon immediately to the left of the File
menu item, select View Code from the popup menu that appears and then
copy/paste the above code into the code window that opened up. Now, go back
to your worksheet and select different sheets.

--
Rick (MVP - Excel)


"SangelNet" wrote in message
...
Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

Would work, but what if the user doesn't want to have it for all worksheets,
or either Cell A1 or Cell A2 is blank?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Rick Rothstein" wrote in message
...
Try this worksheet event code...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").End(xlDown).Offset(1).Activate
End Sub

To implement it, right click the icon immediately to the left of the File
menu item, select View Code from the popup menu that appears and then
copy/paste the above code into the code window that opened up. Now, go
back to your worksheet and select different sheets.

--
Rick (MVP - Excel)


"SangelNet" wrote in message
...
Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

Would work, but what if the user doesn't want to have it for all
worksheets, or either Cell A1 or Cell A2 is blank?

I answered the question the OP asked. His first sentence was...

"I'm trying to put together something that will set the focus
on the first blank cell of a range ("A") for each sheet.

I assumed "first blank cell of a range ("A") meant the first blank cell in
Column A no matter where it occurred. His the last 3 words covers your other
question.

--
Rick (MVP - Excel)

Yes, that's my point. If A1 is blank, and you do the End(xlDown) on A1, it
still won't be selecting A1 with A1 being the first blank cell.

If A1 is not blank, but A2 is blank, once again, when you use the
End(xlDown) on A1, it will still be below A2, so once again, the first blank
cell is not selected.

Also, if every single cell in A1 has something in it, it will error out as
it can't go to a cell of A65537.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Rick Rothstein" wrote in message
...
Would work, but what if the user doesn't want to have it for all
worksheets, or either Cell A1 or Cell A2 is blank?

I answered the question the OP asked. His first sentence was...

"I'm trying to put together something that will set the focus
on the first blank cell of a range ("A") for each sheet.

I assumed "first blank cell of a range ("A") meant the first blank cell in
Column A no matter where it occurred. His the last 3 words covers your
other question.

--
Rick (MVP - Excel)

Good point! Thanks for catching my momentary "lapse".

Here is new code (still for the Workbook SheetActivate event so it will
apply to all sheets) which should work as the OP wanted...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim LastCell As Range
Set LastCell = Sh.Cells(Sh.Rows.Count, "A").End(xlUp)
On Error GoTo Whoops
Sh.Range("A1:A" & LastCell.Row).Find("", After:=LastCell, _
SearchOrder:=xlByRows).Activate
Exit Sub
Whoops:
If LastCell.Row = 1 Then
Range("A1").Activate
Else
LastCell.Offset(1).Activate
End If
End Sub

--
Rick (MVP - Excel)


"Ronald R. Dodge, Jr." wrote in message
...
Yes, that's my point. If A1 is blank, and you do the End(xlDown) on A1,
it still won't be selecting A1 with A1 being the first blank cell.

If A1 is not blank, but A2 is blank, once again, when you use the
End(xlDown) on A1, it will still be below A2, so once again, the first
blank cell is not selected.

Also, if every single cell in A1 has something in it, it will error out as
it can't go to a cell of A65537.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Rick Rothstein" wrote in message
...
Would work, but what if the user doesn't want to have it for all
worksheets, or either Cell A1 or Cell A2 is blank?

I answered the question the OP asked. His first sentence was...

"I'm trying to put together something that will set the focus
on the first blank cell of a range ("A") for each sheet.

I assumed "first blank cell of a range ("A") meant the first blank cell
in Column A no matter where it occurred. His the last 3 words covers your
other question.

--
Rick (MVP - Excel)