• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a code to open a worksheet at a set cell

Martin Lloyd

New Member
I would like to be able to press on the bottom tabs in a workbook so each worksheet opens at a predetermined cell each time and doesn't go back to where it was left on a previous viewing.
Also, when opening a workbook, I would like it to open at the first worksheet at a set point and then by pressing the tabs for each worksheet thereafter they all open in the same set position.
To make it clearer, I have made up six quotation forms in six worksheets where the first page on all tabs is a photograph so I would prefer that the documents open at the cell where I type the name and address which is on the second page of each worksheet.
Thank you if you can help me with this.
 
Hi Martin Lloyd

Please open the VB editor for that particular file and enter the code on workbook_open

worksheet("Sheetname").range("cell reference#").select
 
I would like to be able to press on the bottom tabs in a workbook so each worksheet opens at a predetermined cell each time and doesn't go back to where it was left on a previous viewing.
Also, when opening a workbook, I would like it to open at the first worksheet at a set point and then by pressing the tabs for each worksheet thereafter they all open in the same set position.
To make it clearer, I have made up six quotation forms in six worksheets where the first page on all tabs is a photograph so I would prefer that the documents open at the cell where I type the name and address which is on the second page of each worksheet.
Thank you if you can help me with this.

Hi Martin,

If I understood your question correctly, on the first tab you will have sheet name along with the cell that needs to be selected when navigated to the particular worksheet.

For example, on the first sheet, I have a list as follows :

Sheet2B10
Sheet3C1

So from the above table it is mentioned that, when the user navigates to Sheet2, cell B10 to be activated or selected and the same with Sheet3.

In order to activate the given cell, on VB Editor, you can right click on the sheet name and write Activate event to nagivate to the specified cell.

Here is how the code looks like :

Code:
'The following code is written in the activate event of Sheet3
Private Sub Worksheet_Activate()
'Cell C4 of Sheet1 contains the cell which needs to be activated upon activation. Hence reading that value form sheet1
    Range(Sheets("Sheet1").Range("C4").Value).Select
End Sub

Hope that helps!!
 
Back
Top