Drop down list to select sheet name

**FinProfessional** · 12-06-2013, 11:49 AM

Good Morning!

I am trying to add a drop down list to my macro to allow the user to select from a list of names to name a new sheet created.
I have the current macro which allows them to enter a sheet name, but I have decided I would prefer to provide a list to choose from as some formulas on another tab are dependent on the sheet name. How do I adjust the below to provide a list vs input box?

    Dim VariableSheetName As String
    VariableSheetName = InputBox("Sheet Name", "Enter Projection name")
    Sheets("Sheet1").Name = VariableSheetName

Thanks in advance for any help you can provide!!!

**natefarm** · 12-06-2013, 01:04 PM

Will the number of sheets and the sheet names be static or dynamic? Also, will you want the list to include all sheets, or just certain ones?

**FinProfessional** · 12-06-2013, 01:40 PM

Hi natefarm,

Thanks for the response! The number of sheets and sheet names will be dynamic. Each time they receive the current input box they are adding a new sheet. I want the list to include a list of names they can select to name each new sheet. I do not want the user to manually enter the sheet name.

**6StringJazzer** · 12-06-2013, 01:23 PM

Please take the time to review our rules. There aren't many, and they are all important.

Rule #3 requires CODE tags around VBA code. I have added them for you because you are a new member.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**FinProfessional** · 12-06-2013, 01:43 PM

Thanks! The rules have been reviewed.

I've also adjusted my original post as I realized was not 100% clear. I want them to choose from a list of names to name a new sheet (not select and existing sheet). Sorry about the confusion.

**6StringJazzer** · 12-06-2013, 01:43 PM

InputBox is a built-in function, but list boxes have to be created and built. I have attached an example where the user is prompted with a user form that includes a list box showing all worksheet. The user selects a sheet then clicks OK to go to that sheet, or Cancel to cancel. A Sub called Test shows the form.

**6StringJazzer** · 12-06-2013, 01:48 PM

I misread your initial post. My macro allows the user to select an existing sheet, not select a name to be applied to a new sheet.

You still need a list box. How are you going to determine what the list should be?

**FinProfessional** · 12-06-2013, 01:53 PM

I have a defined list they will be able to choose from to name each sheet. I have defined the range "forecast version". Is there a way to use this defined range in the macro?

**natefarm** · 12-06-2013, 02:23 PM

Somewhere in your worbook, enter the list of names in a range, and name it NameList. Then from the Developer tab, insert an ActiveX Combobox in the desired location, and in the Properties, enter "NameList" in the ListFillRange. Right-click the combo box and select View Code. Then in the ComboBox1_Change event that it creates, put the following:

Private Sub ComboBox1_Change()
    Worksheets.Add
    ActiveSheet.Name = ComboBox1.Value
End Sub

There are other things you can do with it, but that should get you startede.

Drop down list to select sheet name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Re: Drop down list to select sheet name

Thread Information

Users Browsing this Thread

Similar Threads

Linking drop down list to select from another list ?

[SOLVED] Select from drop down list to show another drop down list in a different cel

How do I use one list drop down menu result to select another list?

Select:Drop down list

multiple select from the drop down list in excel. list in one sheet and drop down in

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions