Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Next Blank Cell in Blank Row | Excel Worksheet Functions | |||
Finding next non-blank cell | Excel Discussion (Misc queries) | |||
Finding Worksheet and Activating it | Excel Discussion (Misc queries) | |||
Finding blank cell | Excel Programming | |||
Finding first non-blank cell | Excel Programming |