PDA

View Full Version : Solved: Populate Combo box with shortened list



MRichmond
01-26-2012, 01:47 AM
Yesterday p45cal was good enough to provide a way of listing all the worksheets in my workbook, minus some excluded worksheets (see earlier post)

http://www.vbaexpress.com/forum/showthread.php?t=40651

What I am looking for now is a way to load this list into a combobox.

The user will click anywhere on the summary tab, and a user form with a combobox appears asking them to select which audit they wish to view.
As you can see from sample attached I have found code to pre-populate the combo box but it lists all worksheets, not the shortened list as shown on the summary tab.

Can anyone tell me how to amend the code so only the tabs listed on the summary are shown in the combo box?

p45cal
01-26-2012, 03:58 AM
Instead of a combobox to navigate, would you be open to having hyperlinks added to the relevant cells so that clicking say the cell in column A (or B) would take you to that sheet? This adds hyperlinks to column A and clicking one of them takes you to cell A1 (or any other cell if you so wanted) on that sheet:
Private Sub Worksheet_Activate()
Intersect(Range("A:B"), UsedRange.Offset(1)).ClearContents
For Each sht In ThisWorkbook.Sheets
If Sheets("Lists").Range("Exclude").Find(sht.Name, lookat:=xlWhole, LookIn:=xlFormulas) Is Nothing Then
Set lr = Cells(Rows.Count, 1).End(xlUp).Offset(1)
lr.Value = sht.Name
lr.Offset(, 1).Value = sht.Range("I2").Value
Hyperlinks.Add Anchor:=lr, Address:="", SubAddress:="'" & sht.Name & "'!A1"
End If
Next sht
End Sub
(I haven't looked at the sheet you attached, only added one line to the code in the other thread.)

MRichmond
01-26-2012, 04:18 AM
Thanks p45cal,
Must admit hadn't thought about that, but it works for me.

One problem though, the prevous audits are hidden sheets, and the hyperlink only works if the sheet is visible.

Can you extra line be amended to unhide the sheet and then take you there?

p45cal
01-26-2012, 05:31 AM
Ahh. Snag.
There is a work round but not a one liner. Try the following in the Summary sheet's code module:
Private Sub Worksheet_Activate()
Intersect(Range("A:B"), UsedRange.Offset(1)).ClearContents
For Each sht In ThisWorkbook.Sheets
If Sheets("Lists").Range("Exclude").Find(sht.Name, lookat:=xlWhole, LookIn:=xlFormulas) Is Nothing Then
Set lr = Cells(Rows.Count, 1).End(xlUp).Offset(1)
lr.Value = sht.Name
lr.Offset(, 1).Value = sht.Range("I2").Value
Hyperlinks.Add Anchor:=lr, Address:="", SubAddress:="'" & sht.Name & "'!A1" ', TextToDisplay:=lr.Value
sht.Visible = xlSheetHidden 'this hides that sheet if it isn't already hidden.
End If
Next sht
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Static Blocked As Boolean
If Blocked Then Exit Sub
Blocked = True 'prevents the followhyperlink event calling itself.
Sheets(Target.Range.Value).Visible = xlSheetVisible
Target.Follow
Blocked = False
End Sub

MRichmond
01-26-2012, 05:49 AM
Thanks very much p45cal, that works exactly how I pictured it.

:thumb

Now all I have to do is get all my guys to use it (my problem not yours:banghead: )

Thanks again for the help