How to set a first visible row in a worksheet in Excel 2007 using VBA Code?

[pvharibabu]

I am trying to set a first visible row in each worksheet in a workbook when I switch between the worksheets. How to do it? Please advice.

 

[NARAYANK991]

Hi Haribabu ,


I do not know whether this is what you are looking for , but in the ThisWorkBook section of your VBA Project , out in the following code :

[pre]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("AW77").Show
End Sub

[/pre]
The above will ensure that AW77 is visible whenever you switch from one worksheet to another ; the cursor will be positioned on AM65 , so that the cell AW77 is in the centre of the screen.


Narayan

 

[pvharibabu]

Thanks Narayan for your reply.


No, this is not I was looking for.


I want to show the part of worksheet starting from a specific row every time I select/click/activate (not sure which is an appropriate word) on that worksheet. For example, in a workbook of 3 sheets, when I click on Sheet2 I would like to see that sheet starting from Row 70 (I can still scroll up & down later if I want where all other content is visible) and when I click Sheet3 I would like to see that sheet starting from Row 3 and Column C and so on for other sheets. This scenarios I want to see for those sheets only at all times I select the sheets. Let me know if you need any more info re my question

 

[pvharibabu]

Something similar to the FIGURE shown in the link below, bottom of the page

http://www.aspose.com/docs/display/cellsnet/Activating+Sheets+and+Making+an+Active+Cell+in+the+Worksheet

 

[NARAYANK991]

Hi Haribabu ,


Try this :

[pre]

Private Sub Worksheet_Activate()
Range("H70").Select

With ActiveWindow
.ScrollColumn = ActiveCell.Column
.ScrollRow = ActiveCell.Row
End With
End Sub

[/pre]
This should set the top left corner cell to H70.


Have similar code for each Worksheet , changing the address from H70 to whatever you want for each worksheet.


If we use the Workbook_SheetActivate section , then whatever code is executed will be the same for all worksheets ; if you want different code for each worksheet , then use the Worksheet_Activate event with different code for each event.


Narayan

 

[pvharibabu]

Excellent, thank you very much Narayan.


I don't know why this code is NOT working for one worksheet (in the same workbook) only which I made a copy from another workbook. It works perfectly for other worksheets though. That's not a big issue as my purpose is served mainly with other spreadsheets. However curious to know what was wrong with that one worksheet.


Hari

 

[NARAYANK991]

Hi Hari ,


When you say not working , what happens when you switch to that worksheet ?


You can try and debug the issue yourself ; go to the code in the Visual Basic Editor , position your cursor on the first statement in the procedure , and press the F9 key. This sets a breakpoint on that statement , which means when the code is executed , the execution will pause on that statement ; thereafter you can press F8 to step through the code , one statement at a time.


Narayan

 

[pvharibabu]

Sorry, my mistake, there were two worksheets with the same name (not really, the other sheet with a little extension). With the windows/frames in VB Editor showing only first half of the sheet names, I had the code in other sheet.


Thanks once again

Hari