Solved: Automatically Populate dropdown for each new worksheet created

[ahmed_k]

Hi,
I have this workbook where the users can create a worksheet for each location. The new location worksheet is a copy of a hidden worksheet called Location(0). The user should be able to create as many new location worksheets as possible. The new worksheets will have Location(1), Location(2), Location(3) ...... as sheet names. On each new sheet the user enters a location description.

On the title page there is a dropdown which I would like to be populated by the location descriptions as each new location is created. When the user selects the location from the dropdown he should be taken to that sheet.

Alternatively if this is can't be done by a dropdown, a list with hyperlinks should work as well. I tried the hyperlinks myself but was unable to write a macro so that it could create a hypeprlinks for new sheets that are created.

Any help would be greatly appreciated. I have attached an Excel file with the necessary information as an example.

Thanks in advance :friends:

[lucas]

It works on sheet activation and seems to do what you asked. It's also a listbox but you could probably change that:

see attached. Code in the module for the title page.

Option Explicit
Private Sub ListBox1_Click()
Sheets(ListBox1.Text).Activate
End Sub
Private Sub Worksheet_Activate()
Dim sh As Worksheet
Sheets("Title Page").ListBox1.Clear
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Location") > 0 And sh.Visible = True Then
Sheets("Title Page").ListBox1.AddItem sh.Name
End If
Next
End Sub

[ahmed_k]

Thanks Lucas. That works great.
But I would like the text in the listbox to be the location description on each sheet so I changed the second macro as below. How do I change the first macro since the text in the listbox is no longer the sheet name?

Private Sub ListBox1_Click()
Sheets(ListBox1.Text).Activate
End Sub

Private Sub Worksheet_Activate()
Dim sh As Worksheet
Sheets("Title Page").ListBox1.Clear
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Location") > 0 And sh.Visible = True Then
Sheets("Title Page").ListBox1.AddItem sh.Range("C3")
End If
Next
End Sub

[lucas]

Best way would be to change the names of your sheets to match the range C3 on each sheet.

[ahmed_k]

The sheet name can only be limited to 31 characters and cannot have characters such as * and /.
In that case how do I enable a msgbox if the user enters more than 31 characters or incompatible characters in C3.

Thanks

[lucas]

Have them name the sheet first and use that in C3?

[lucas]

Put this in c3 of your template and it will reproduce the sheet name in that cell:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

[ahmed_k]

Thanks Lucas, that works great.

Is there any way that whenever a user creates a new location a text entry box pops-up so that the text they enter changes the sheet name? This way The users don't have to manually change the sheet name.

[lucas]

See if this will work for you.

see attached.

sheet Location 0 is now hidden

[ahmed_k]

Awesome... works great. Cheers mate