Worksheet Name in a Dropdown List

[CA PUNEET OBEROI]

Dear Experts,

I have a problem, I need to insert a dropdown list in my first excel sheet named "POC" Which should automatically show any sheet being added and on a click of button can directly take me to that sheet. My other sheets are named as "APRIL 2015" "MAY 2015" and so on. Kindly advise. I am new to excel VBA programing.

 

[mumps]

I am assuming that your "POC" sheet is Sheet1. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. On the left hand side you will see a list of your worksheets. Double click the "POC" sheet and an empty code window will open up. Copy and paste the following macro into this code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Sheets(Target.Value).Activate
    End If
End Sub

Next in the left hand panle, double click "ThisWorkbook". Another empty code window will open up. Copy and paste the following macro into this code window.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim arrWS() As Variant
    ReDim arrWS(1 To Sheets.Count)
    Dim i As Long
    For i = 2 To Sheets.Count
        arrWS(i) = Sheets(i).Name
    Next i
    With Sheets("POC").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(arrWS, ",")
    End With
End Sub

Whenever a new sheet is added, a drop down list will be created in cell A1 of the "POC" sheet. When you select a sheet from the list, that sheet will be activated.

 

[CA PUNEET OBEROI]

Dear Mumps,

Thanks for your help but I am sorry but this didnt work. When i tried to run this, it shows "Run time Error 424, Object Required". When I try to debug this, it opens window for Module1 and give following:

Sub OpenSampleForm()
frmSample.Show
End Sub

with middle line highlighted as yellow.

Please help

 

[mumps]

When I tried it using some dummy data, it worked properly. It looks like you are using some additional code that I'm not aware of. It would be easier to help if I could see your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.