Destination box in Excel 1

[gizmo1973]

I know what you are all going to say "use access" but in this instance I can't as it's for a friend who works for a charity and they cannot afford Access.
Okay we have an XP excel workbook with over 300 sheets and I need a navigation aid from the Master sheet, ideally a

text field
go button
cancel button,

you enter the sheet name in the text field and press go and hey presto you arrive at the sheet or of course you cancel.

i tried the hyperlink but it won't fill ranges and also tried named range and din but my friends has the above idea fixed as a winner.
now I'm not much good at VB which I suspect this may be (should I be in the VB forum?) so any ideas would be most graciously accepted

Regards, Phil.

I'm here because I LOOK busy

The New Red Army Marches On

http://www.fcunited.co.uk

P.S. Beer anyone?

 

[bkpchs237]

gizmo1973,
Using hyperlinks is clean and simple. List each sheet as a link within your master sheet and voila! If you don't want to use them then you are in the wrong forum as vba will be necessary thereafter. Further trying to use a text box may be problematic instead of a cell on the worksheet and just change its value and use it similar to:

Sub GotoMySheet()
Dim strSheetName As String
strSheetName = Range("A2").Value
Sheets(strSheetName).Select
End Sub

Of course you'd need to program in some error handling too.
At any rate I don't understand what you mean by: "...but it won't fill ranges..." What do you want this link to do exactly?

Hope this helps.

 

[gizmo1973]

bkpchs237, many thanks for your reply.
when I said "...but it won't fill ranges..." i men this.

In cell A1 of master I set a hyperlink to sheet1 but if I try to drag it down to B1 C1 D1 etc it won't fill the range it will stay as sheet1, if I could get it to fill by increments then i would be happy to recommend the hyperlinks but it would mean setting a hyperlink for every sheet in the workbook


Regards, Phil.

I'm here because I LOOK busy

The New Red Army Marches On

http://www.fcunited.co.uk

P.S. Beer anyone?

 

[bkpchs237]

gizmo1973
Ahhhh. Why didn't you say so? I didn't quite understand what you meant earlier. If this assumption is correct - that you have 300 sheets named "Sheet1", "Sheet2", "Sheet3" etc... or some other similar pattern that increases numbers by 1 that you wish to link to quickly. Also, that you are not actually opposed to hyperlinks, but want a quick method to create them instead of one by one.
If this assumption is correct then this should help you. It will put a hyperlink in every cell from cell A1 thru cell A300 in the current worksheet to each of the other worksheets. I assumed named "Sheet#" as the names of the files, but you can substitute your text where the word Sheet is listed:

Option Explicit

Sub GotoMySheet()
Dim i As Integer
For i = 1 To 300
Range("A" & i).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet" & i & "!A1"
Next i
End Sub

Sub ReturnToMasterSheet()
ActiveWorkbook.Sheets("Master").Activate
Range("B1").Select

End Sub

The second procedure is for you to return to the master sheet which can be assigned to a toolbar button if desired.

Hope this helps.

 

[byundt]

The HYPERLINK function may be of some use to you. It may take some tweaking to get the formula dialed in (you need to use in a previously saved workbook for example), but try copying down a formula in cell A1 like either of these:
=HYPERLINK("[Book1.xls]Sheet" & ROW() & "!A1")
=HYPERLINK("[Name with space.xls]Sheet" & ROW()-3 & "!A1","Sheet" & ROW()-3)

As you copy this formula down, the ROW() part of the formula increments to produce Sheet1, Sheet2, Sheet3, etc. The second formula was placed in row 4, so it needed to subtract 3 to get the series Sheet1, Sheet2, Sheet3, etc. And the use of the second parameter simplifies the appearance of the link--just the sheet name instead of [Workbook name.xls]Sheet1!A1

Here is a sample workbook showing how it might work:

http://home.mchsi.com/~byundt/Book_1.xls

Brad

 

[GlennUK]

Hi Phil,

you can get cheap second-hand copies of old versions of Office ( which includes Access ) off the usual auction sites. Last year I got Office 97 for less than £20.

Just a thought, if you end up wanting to try the Access route.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Hey Giz. This can be done quite easily with a bit of VBA.

1: Create a new sheet - call it "Lookup"
2: Create a dynamic named range in the lookup sheet - formula would be: =OFFSET(A1,,,counta($A:$A),1) - call it dr_Sheets
3: Following code goes in workbook_OPEN event

i=1
For each sht in thisworkbook.worksheets
  if sht.name <> "Lookup" then
    Sheets("Lookup").cells(i,1).value = sht.name
  i = i + 1
  end if
next

4: Create a Data>Validation dropdown box which uses the 'List' option - list should be set to dr_Sheets

5: If the dropdown box is in A1 then in the worksheet_CHANGE event for that sheet, use:

if target.address <> "$A$1" then exit sub
sheets(Range("A1").text).select

Let me know how it goes

 

[gizmo1973]

XLBO to the rescue!!!!!

Cheers Geoff!!

He's just tried it and is chuffed to bits with it!!
P.S. Enjoy your beer???

Glenn,

Good point well made and that advice has gone to him too as if he went down access he could do so much more

All others,

Thanks for you answers and efforts!!

Regards, Phil.

I'm here because I LOOK busy

The New Red Army Marches On

http://www.fcunited.co.uk

P.S. Beer anyone?

 

[xlbo]

Giz - no worries - I always enjoy my beer !!!