Solved multi sheet listing in dropbox

June 11, 2012 at 06:14:27
Specs: Windows 7
Good day,

I have a workbook 130 sheets in Excel 2010.
I would like to create a dropbox with all the cell value of C1 from each sheets.
then when selecting from the dropbox on value, go to the corresponding sheet.

Thanks by advance for your help


See More: multi sheet listing in dropbox

Report •

June 11, 2012 at 10:55:15
✔ Best Answer
Do you have a different value in every C1? If not, the code will not know which "C1" to activate.

This should get you started:

1 - Create a new sheet and name it "Index Sheet"
2 - Right Click on the tab for that sheet and choose View Code
3 - Paste both pieces of the code shown below into the pane that opens
4 - Run the code entitled CreateDropDownList to create the Drop Down.
Note: You must rerun that code whenever you change any C1 to update the Drop Down.
5 - Choose a value for the Drop Down

Sub CreateDropDownList()
'Clear Column A so new list can be created
 Sheets("Index Sheet").Columns("A").ClearContents
'Place sheet names in Column A
     For shtNum = 2 To Sheets.Count
      Sheets("Index Sheet").Range("A" & shtNum - 1) = Sheets(shtNum).Range("C1")
'Update DropDown list in B1
     With Sheets("Index Sheet").Range("B1").Validation
      .Add Type:=xlValidateList, Formula1:="=$A$1:$A$" & Sheets.Count
     End With
End Sub

Private Sub worksheet_change(ByVal Target As Range)
'Monitor Index Sheet!B1
  If Target.Address = "$B$1" Then
'Search for Target value in C1 of each sheet
    For shtNum = 2 To Sheets.Count
'Activate sheet when found
      If Sheets(shtNum).Range("C1") = Target Then
        Exit Sub
      End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question