Dropdown selection to unhide worksheets

[ysplace]

Hi everyone,

I need some help and hopefully I can explain it clearly. I have an excel workbook that consists of 57 worksheets. I want to hide all worksheets but use a listbox to selected worksheets visible. HOWEVER....The listbox names are not exactly the same names as the worksheets. Due to size limitation for the name of each worksheet, I had to abbreviate the name. I want the listbox names to be what users are used to seeing. Here is an example:

In List Box Actual Worksheet Name
[TABLE="width: 597"]
<tbody>[TR]
[TD]Escrow Balance[/TD]
[TD]Escrow Balance[/TD]
[/TR]
[TR]
[TD]Accrued Interest[/TD]
[TD]Accrued Interest[/TD]
[/TR]
[TR]
[TD]Late Charge Balance and Calculation Variables[/TD]
[TD]Late Chg & Bal Calc[/TD]
[/TR]
[TR]
[TD]Recurring Fee Balance and Calculation Variables[/TD]
[TD]Recur Fee Bal & Calc[/TD]
[/TR]
[TR]
[TD]Mortgage Insurance[/TD]
[TD]Mtg Insurance[/TD]
[/TR]
[TR]
[TD]Mortgage Insurance Payment Information[/TD]
[TD]Mtg Ins Pymt Info[/TD]
[/TR]
[TR]
[TD]Simple Insurance[/TD]
[TD]Simple Insurance[/TD]
[/TR]
[TR]
[TD]Financed Single Life Insurance[/TD]
[TD]Finc'd Single Life Ins[/TD]
[/TR]
[TR]
[TD]Financed Joint Life Insurance[/TD]
[TD]Fin'd Jt Life Ins[/TD]
[/TR]
[TR]
[TD]Financed Accident & Health Insurance[/TD]
[TD]Finc'd ACC Hth Ins[/TD]
[/TR]
[TR]
[TD]Financed Interest Balance[/TD]
[TD]Financed Int Balance[/TD]
[/TR]
</tbody>[/TABLE]

If user select "Late Charge Balance and Calculation Variables than make visible worksheet "Late Chg & Bal Calc". Any suggestions?

Thank you!

 

[igold]

How about using a two column listbox with the index number of the sheet as the second column. Make column 2 the bound column of the listbox and use it to refer to your sheet.

 

[ysplace]

Thought about it but concern about confusion.

 

[igold]

If the appearance of a number is confusing, how does the end user make sense of an abbreviated worksheet name.

 

[Fluff]

If you want the actual sheet names in the listbox, how about

Private Sub UserForm_Initialize()
   Dim Ws As Worksheet
   For Each Ws In Worksheets
      Me.ListBox1.AddItem Ws.Name
   Next Ws
End Sub

 

[Jmoz092]

@Fluff, where does that code get posted?

 

[Fluff]

In the userform module