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

Escrow Balance	Escrow Balance
Accrued Interest	Accrued Interest
Late Charge Balance and Calculation Variables	Late Chg & Bal Calc
Recurring Fee Balance and Calculation Variables	Recur Fee Bal & Calc
Mortgage Insurance	Mtg Insurance
Mortgage Insurance Payment Information	Mtg Ins Pymt Info
Simple Insurance	Simple Insurance
Financed Single Life Insurance	Finc'd Single Life Ins
Financed Joint Life Insurance	Fin'd Jt Life Ins
Financed Accident & Health Insurance	Finc'd ACC Hth Ins
Financed Interest Balance	Financed Int Balance

<tbody>
</tbody>

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