Finding and Activating the first blank cell
 

Hi there,
I've never done any Visual Basic programming (only C) and have been
browsing this group for a while. I need to find a way to do two
things:
1. Set the scrollable area for a worksheet when it's reopened (I have
accomplished this by using
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.ScrollArea = "a1:g5000"
End Sub
I found this on another usenet post and edited it fit my application.
I'm not sure why it is being done every time the selection is changed
but it seems to be working which makes me happy.

2. Now the part I'm stuck on. I need to make it so that when the
user opens the worksheet the active cell will be the first blank one in
the scrollable area. I've seen several other posts, but yet to find a
definitive solution for someone on my... newbiness level.

Thanks for your help!
Mike

Finding and Activating the first blank cell
 

Hi Mike,

1. ScrollArea.
There's no need to define the ScrollArea each time user selects a different
cell on the sheet, which is what occurs with your posted code. Once set it's
set, until programmatically changed or manually by changing the property in
the sheet module properties.

2. Not sure what you mean by 'the first blank one' [cell], eg the last cell
with data in Col-A, Row-1, the cell in a row below the last row that
contains data in any column, ditto any of the preceding to include last cell
containing any kind of formatting even if empty. For the latter record a
macro while doing Ctrl-End.

to select the cell below the last cell that contains data in col-A when the
sheet is activated, try this in the sheet module (right-click sheet tab
view code).

Private Sub Worksheet_Activate()

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate

End Sub

Regards,
Peter T



wrote in message
ups.com...
Hi there,
I've never done any Visual Basic programming (only C) and have been
browsing this group for a while. I need to find a way to do two
things:
1. Set the scrollable area for a worksheet when it's reopened (I have
accomplished this by using
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.ScrollArea = "a1:g5000"
End Sub
I found this on another usenet post and edited it fit my application.
I'm not sure why it is being done every time the selection is changed
but it seems to be working which makes me happy.

2. Now the part I'm stuck on. I need to make it so that when the
user opens the worksheet the active cell will be the first blank one in
the scrollable area. I've seen several other posts, but yet to find a
definitive solution for someone on my... newbiness level.

Thanks for your help!
Mike

Finding and Activating the first blank cell
 

Mike,
Just to clarify Peter's response:
You only need to set the Worksheet ScrollArea when the file is opened, as it
is not saved with the file.
So in the ThisWorkbook module:
Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:G1000"
End Sub

NickHK

wrote in message
ups.com...
Hi there,
I've never done any Visual Basic programming (only C) and have been
browsing this group for a while. I need to find a way to do two
things:
1. Set the scrollable area for a worksheet when it's reopened (I have
accomplished this by using
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.ScrollArea = "a1:g5000"
End Sub
I found this on another usenet post and edited it fit my application.
I'm not sure why it is being done every time the selection is changed
but it seems to be working which makes me happy.

2. Now the part I'm stuck on. I need to make it so that when the
user opens the worksheet the active cell will be the first blank one in
the scrollable area. I've seen several other posts, but yet to find a
definitive solution for someone on my... newbiness level.

Thanks for your help!
Mike

Finding and Activating the first blank cell
 

"NickHK" wrote in message

Just to clarify Peter's response:
You only need to set the Worksheet ScrollArea when the file is opened,
as it is not saved with the file.

Good catch !

Regards,
Peter T

Finding and Activating the first blank cell
 

Thanks for your help guys.

However, that code is not working :(

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:G1000"
End Sub

Private Sub Worksheet_Activate()
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
End Sub

Is exactly what I have pasted in there, it seems like the macros never
run. I tried this before with some stuff I found and the only macro's
I've been able to successfully see run were the ones that ran each time
the selection changed. I'm using Excel 2003... and it doesn't seem to
matter what security level I set it at. It just doesnt' seem to run
either macro.

Any ideas?

Thanks,
Mike

Finding and Activating the first blank cell
 

I got it! I had to put the code into a module (apparently always the
case but I've never done this befoX) After that I used the
Auto_Open function like:
Private Sub Auto_Open()

Worksheets(1).ScrollArea = "A1:G1000"
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate

End Sub

And it works!!

Thanks a bunch guys,
Mike

Finding and Activating the first blank cell
 

Are you sure you put these in the correct modules, respectively
'ThisWorkbook' and the relevant worksheet module, as described in earlier
posts.

You may want to change -
Worksheets(1).etc to Worksheets("sheetname").etc unless sure your sheet will
always be the first worksheet.

Actually it wouldn't do any harm to combine both lines of code in the
Worksheet_Activate event (in the worksheet module) and dispense with the
Workbook_Open event, then you won't need to worry about sheet order or
anyone renaming the sheet.

Regards,
Peter T


wrote in message
ps.com...
Thanks for your help guys.

However, that code is not working :(

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:G1000"
End Sub

Private Sub Worksheet_Activate()
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
End Sub

Is exactly what I have pasted in there, it seems like the macros never
run. I tried this before with some stuff I found and the only macro's
I've been able to successfully see run were the ones that ran each time
the selection changed. I'm using Excel 2003... and it doesn't seem to
matter what security level I set it at. It just doesnt' seem to run
either macro.

Any ideas?

Thanks,
Mike

Finding and Activating the first blank cell
 

Ignore the suggestion about combining all in the Worksheet_Activate event,
it will only fire when the sheet is activated, not when the workbook opens.

Putting all in in Auto_Open in a normal module as per your latest post is
also fine, but if you also want the first empty cell in col-A to be
activated when switching back also include the Worksheet_Change event.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
Are you sure you put these in the correct modules, respectively
'ThisWorkbook' and the relevant worksheet module, as described in earlier
posts.

You may want to change -
Worksheets(1).etc to Worksheets("sheetname").etc unless sure your sheet
will
always be the first worksheet.

Actually it wouldn't do any harm to combine both lines of code in the
Worksheet_Activate event (in the worksheet module) and dispense with the
Workbook_Open event, then you won't need to worry about sheet order or
anyone renaming the sheet.

Regards,
Peter T


wrote in message
ps.com...
Thanks for your help guys.

However, that code is not working :(

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:G1000"
End Sub

Private Sub Worksheet_Activate()
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
End Sub

Is exactly what I have pasted in there, it seems like the macros never
run. I tried this before with some stuff I found and the only macro's
I've been able to successfully see run were the ones that ran each time
the selection changed. I'm using Excel 2003... and it doesn't seem to
matter what security level I set it at. It just doesnt' seem to run
either macro.

Any ideas?

Thanks,
Mike