Excel question?

Question

I am working with excel (2003,2007) files and I was wondering if there is a way in which I can jump quickly to particular worksheet. My workbook has a lot of sheets and it is difficult to scroll through them to find the one I want.

I heard that u can do thi kind of thing with VBA. i am a java programmer but have no experience with VB. Can anyone tell me how this can be done. I am thinking about a combo box that has a list of the sheets in the workbook poping up when I hit a key so that I can select the sheet that I want to go to. It would be good if the combo box has an "Autocomplete" feature.

Answer 1

Input the cell coordinates (e.g. C349) into the name box (the box to the left of the one with the "fx" label).

Answer 2

Yes, there is an easy way. Go to the bottom left hand corner of Excel, just to the left of the sheet tabs. Then right click on the arrows. A list will pop up that you can choose your worksheet from. If it isn't listed there because you have a ton of worksheets click More... and it will give you a second list that you can quickly scroll through.

Another good way is the way dewcoons mentioned. But that is provided you remember the name of the worksheet without having to look it up.

Answer 3

Step 1 - List all of your worksheets in one column (note my example will be in the range A2:A4 of Sheet1)
Step 2 - insert a combo box form (in Excel 2007 -> Developer > Insert > form controls > Combo box)
Step 3 - right click the combo box you just inserted and click format control, in the control tab set the input range to list of worksheets you created in step 1, Set the cell link as A1
Step 4 - right click the combo box and click "assign macro", then click "new"

insert the following code into Visual Basic:

Sub DropDown2_Change()
Dim curSheet As String

Sheets("Sheet1").Select
curSheet = ActiveSheet.Range("A1").Offset(ActiveSheet.Range("A1").Value, 0).Text
Sheets(curSheet).Select
End Sub

Note, you'll need to make sure the names of the sheets exactly match the names in your list, also if you change the location of the list or the cell link, you'll have to adjust the VB code. If you know anything about named ranges, you can name the two ranges and this will solve the problem, but that's a different issue.

If you have any other questions, let me know.

Sorry, the actual post isn't displaying the entire 4th line... here it is in pieces:

curSheet = ActiveSheet.
Range("A1").
Offset(ActiveSheet.
Range("A1").Value, 0).Text

Answer 4

I do not know of a way unless...you go to the find feature and search for a word that will be on that worksheet. if excel finds that word than the location will come up on in a box and you can go to it that way. I would just ask the office assistant on excel, it usually gives good advice. Good luck.

Answer 5

Snoopy (the first poster) is almost right in his answer. Typing in the cell reference will take you to a cell within the same sheet.

To get to a cell on another sheet, add the sheet name followed by a ! in front of the cell name. So to move to cell A1 on Sheet3, you would type in "Sheet3!A1"