setfocus like in access....

[snayjay]

I have a workbook with several worksheets. Well I figured out how to (in code) make the workbook open to a specific worksheet no matter which worksheet the workbook closed on. But for the life of me I can't figure out how to make a worksheet setfocus to the next blank row on open. it's driving me mad. I tried:

Private Sub Worksheet_Activate()
Rows.FindNext
End Sub

But it only open the worksheet to A1. A1 is blank but is where my title for the sheet is. I want it to find it further down. Any help would be appreciated.

v/r Snay

 

[danomaniac]

snayjay,

You could try this. This selects the last used cell in the worksheet, then takes it's row number, adds one, and selects the first cell in that row.


Sub SelectNextBlankRow()

Dim lastrow

ActiveCell.SpecialCells(xlLastCell).Select
lastrow = Selection.Row + 1

ActiveSheet.Cells(lastrow, 1).Select

End Sub


Hope that helps

 

[danomaniac]

Oops, forgot you were using this on activate..

Private Sub Worksheet_Activate()

Dim lastrow

Sheets("Sheet1&quot.Activate
Activesheet.Cells(1,1).Select
ActiveCell.SpecialCells(xlLastCell).Select
lastrow = Selection.Row + 1

ActiveSheet.Cells(lastrow, 1).Select

End Sub

 

[snayjay]

Well I tried it on several pages....and changed the Sheet number as well....and it didn't work. Thanks for trying though.

 

[xlbo]

Try this - must be in the WORKSHEET module
Private Sub Worksheet_Activate()
activesheet.range("A" & range("A65536&quot.end(xlup).row +1).select
End Sub

Tested and works

Rgds
Geoff

 

[snayjay]

You are so awesome....that worked. Only one problem. I created my sheets with a title on top of each page and a navigation bar. Using macros I have a next page and previous page button for each page. The code doesn't work when I use my navigation buttons. Only when I click on the tab at the bottom of the page. Is there something in code I can add that can activate the page thru the macro. I was just assuming that the page (activation) was being called when I used the macro to go to the page. I can email the .xls to you if you want to see what I'm talking about.

 

[xlbo]

Hi Snayjay - just add the line of code I posted to the last line of your sub that changes to next or previous sheet. Presumably, those subs include a
sheets(NextSheet).select
line, or similar
Just put it after that - will work on any sheet 'cos it uses the activesheet object
HTH
Geoff

 

[snayjay]

Ok....let me first say thanks to all who tried to help. I really appreciate any effort...because without your help I'd have many unfinished projects. On to this problem. Ok here is what I did. Since I was doing the next page and previous page things thru hyperlinks. I wasn't able to add the:

ActiveSheet.Range("A" & Range("A65536&quot.End(xlUp).Row + 1).Select

code to it. So I tried putting it under the worksheet.followhyperlink method and what it did is took the correct position from the page the code is on and applied it to the next page not giving me the right affect. Well my frustration got to me and I decided to use a command button. I'm used to doing vba in Access. This is the first project I've done in excel. Anyway, I made a commandbutton and tried this code:
Private Sub PrevPage_Click()
Sheets(Sheet3).Select
ActiveSheet.Range("A" & Range("A65536&quot.End(xlUp).Row + 1).Select
End Sub

Well it didn't work either. I get the error. Type mismatch. In Access, I can usually figure out the problem with that error. I'm usually putting a value on a label or caption on a textbox or something backassward like that. Anyway, I can't figure out the mismatch here. So can anyone figure this one out. I'm sure it's a simple one...so I'll just wait on an answer. But if I can't get this program to work the way I want I'll just make a darn .mdb. I like Access better anyway. I was just trying to fix the current spreadsheet the office was using. Thanks in advance,

Snayjay

 

[snayjay]

Ok...nvm all that crap above this. I got it working. What I did is made the commandbutton and simply put the code:

Private Sub PrevPage_Click()
Sheet3.Select
End Sub

on all the pages for next page and previous page...changing the sheet number each time of course. And then on all the pages I added the other code:

Private Sub Worksheet_Activate()
activesheet.range("A" & range("A65536&quot.end(xlup).row +1).select
End Sub

and it works great. Thanks again for all your help.