Finding and Activating the first blank cell

[[email protected]]

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

[Peter T]

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



<[email protected]> wrote in message
news:[email protected]...
> 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
>

[NickHK]

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[Peter T]

"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

[[email protected]]

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

[[email protected]]

I got it! I had to put the code into a module (apparently always the
case but I've never done this before:X) 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

[Peter T]

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


<[email protected]> wrote in message
news:[email protected]...
> 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
>

[Peter T]

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
news:#[email protected]...
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>