Unhide Worksheet with Same Label Name

ltzhao

New Member
Joined
Dec 10, 2004
Messages
32
Hello All,

I have a workbook with quite a few worksheets, along with a summary page. The summary page has a list of names of all the worksheets in the workbook. I am still learning to use Macro. So far, I have created buttons to hide the individual worksheets (each component is on a worksheet by itself). But I can't figure out how to unhide a specific worksheet from a group of worksheets without creating just as many macros calling specific worksheet names. I don't really want to create 100s of little macros. I am sure there is a better way out there.

Here is a concept of the workbook:

On the summary page, the user supposed to click on a label ("T1000" for example), and this will unhide a worksheet with the same name (T1000). There are hundreds of these worksheets, how does it find


This is my idea of how the function supposed to work.

First determine the label text as a string.
Then locate the worksheet with the name equals to the string
then set visibility = true.

Can anyone please help me.

Thanks,

L.T.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hello, ltzhao
This is (usually) very easily done.
What I'm not clear on is what you mean by 'label'.
What exactly is the user clicking on when you say that?
(If it's a control from a toolbar, which toolbar did it come from?)
 
Upvote 0
Hi HalfAce,

Thanks for your reply. The "Label" I am using is from Forms tool bar. I have to create these label names so that the user can look at the part numbers, then click on the part numbers if they want to drill down further.

L.T.


Hello, ltzhao
This is (usually) very easily done.
What I'm not clear on is what you mean by 'label'.
What exactly is the user clicking on when you say that?
(If it's a control from a toolbar, which toolbar did it come from?)
 
Upvote 0
Well, if you're open to a suggestion or two, I wouldn't use the label (or for this purpose,
anything from the Forms toolbar).
You would end up with as many labels as you have sheets for them to work on,
resulting in a macro for each one - which I understand you're wanting to avoid. (yes?)
I would consider using a listbox from the Controls Toolbox toolbar.
List the sheet names in some out of the way cells somewhere.
In the ListBox properties, enter that range of cells in the ListFillRange property.
Then the code for the listbox would look something like this:
Code:
Private Sub ListBox1_Click()
  With Sheets(ListBox1.Value)
    .Visible = True
    .Select
  End With
End Sub

You could use a ComboBox instead of the ListBox too.

Of course you could also slap some code in the sheet module of your summary sheet
that hides all your normally hidden sheets when it gets activated. . .

Does any of this sound acceptable(?), or do you really have your heart set on the labels?
 
Upvote 0
Hello HalfAce,

Thanks for your reply. To answer your question, No, I Don't have my heart set on Lable. and Yes, I am trying to avoid creating 100s of little macros.

I will try your suggestion with the list box first, and will let you know the out come.

Thanks for your help.

L.T.
 
Upvote 0
Hello HalfAce,

I realized why I didn't want to use ListBox or ComboBox. It is because each Part number is listed on a seperate Row. Each Row is has specific dates and what not indicating the project progress, whether it is on time or behind or ahead of schedule. When a user sees that a part number is behind schedule, he then can click on the part number on that row to pull up a hidden tab, input any necessary information in the corresponding tab.

If I use a combo box, for instance, then the user will have to go to a different location, find the part number from a huge list of PNs listed in the combobox. Or I will have to create just as many combo boxes, having one combo box per row. I am trying to avoid this as well. So, that being said, do you think it is possible to come up with something that doesn't require me writing 100s little macros?

Thanks in advance for your help.

L.T.


Hello HalfAce,

Thanks for your reply. To answer your question, No, I Don't have my heart set on Lable. and Yes, I am trying to avoid creating 100s of little macros.

I will try your suggestion with the list box first, and will let you know the out come.

Thanks for your help.

L.T.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top