Sheet Selection

[Alice21]

Hi

I have 89 sheets in my workbook, each with a unique ID number.
I want to have a Master front sheet that has a bit of information abou
the system and then a drop down box that has all ID numbers in. When
person selects a certain ID from the drop down it will then take them t
the relevant sheet.

Is this possbible please

 

[GS]

Hi

I have 89 sheets in my workbook, each with a unique ID number.
I want to have a Master front sheet that has a bit of information
about the system and then a drop down box that has all ID numbers in.
When a person selects a certain ID from the drop down it will then
take them to the relevant sheet.

Is this possbible please?

Yes, it's easily doable! Do you want to use a DV dropdown or a ComboBox
control?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Alice21]

Hi

I have used combo boxes before when creating a user from so I'd stick t
that if i could.

Thank

 

[GS]

Hi

I have used combo boxes before when creating a user from so I'd stick
to that if i could.

Thanks

That's fine so long as you don't mind adding the extra baggage to your
already large file. Personally, I don't like using controls in
scrollable areas of a worksheet because their positioning can be
altered unexpected for various reasons. Thus, I put toolbox controls in
frozen pane areas.

I'll need to know how the unique ID relates to its sheet; is it the
sheetname OR stored in a specific cell on each sheet?

Do you want the combobox list loaded when the file first opens? Or
whenever the sheet it's on is activated? (The latter would update the
list for any sheets added/deleted)

If the ID is not the sheetname, I'd go with a 2 col list and make the
sheetnames col the BoundColumn and hide it so only the IDs display. Do
you want to always go to the same position on sheets?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Bob Flanagan]

Alice, the approach I like to use is a master index worksheet at the front of the workbook. On it is a list of every sheet with the name of sheet being a hyperlink to cell A1. And, on every sheet in A1 is the word "index", and it is a hyperlink back to the master index. This avoids any code in theworkbook which would not run if the user has macro disabled.

The Spreadsheet Assistant at http://www.add-ins.com/spreadsheet-assistant.htm has as one of its features a way to build such a master sheet and hyperlinks to and from all sheets.

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[GS]

Robert,

I downloaded this add-in for evaluation, out of curiosity. Very nicely
done except for one very important thing! *It doesn't clean up after
itself on shutdown*! It leaves its toolbars and the customizations it
makes to built-in menus to persist in Excel's TLB file.

Very messy! Not professional behavior for one who appears to be a
member with integrity in the Excel development community! I submitted
my sentiments on your contact form, and anticipate receiving a revised
version to continue my evaluation. As it stands right now.., *I
wouldn't recommend this product to anyone*! I suspect your other
add-ins have the same behavior, but at this point I have no interest in
finding out if they do or not!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

To be more specific.., your add-in persists in automated instances of
Excel. This is typical of people who register their addins under HKLM
instead of the preferred HKCU key in the Registry. Very annoying to
automation developers...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

To be more specific.., your add-in persists in automated instances of

Excel. This is typical of people who register their addins under HKLM
instead of the preferred HKCU key in the Registry. Very annoying to
automation developers...

Note that in the case of Spreadsheet Assistant, I found it under HKU,
which I haven't notice any of the other add-ins installed there.

Meanwhile, I have programmed my automation frontloaders to set the
Installed property of all add-ins to 'False'. This, unfortunately, will
uninstall your product for all my users who happen to use Spreadsheet
Assistant (or any other Excel add-ins)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion