Unhiding Sheets with Combobox selection and Duplicating Sheets Automatically

[D_Davis]

Hello,

I'm new here and seeking some assistance in a project I'm working on that's due in a couple days. I have searched at least 100 threads on multiple boards on the web for an answer and i'm partially there but not quite so with that being said here's what i'd appreciate some assistance with.

I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.

The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.

Thanks in advance for any assistance guys.

Dail

 

[AlphaFrog]

If you used a listbox instead of a combobox, the listbox has an option for multiselect where you can select more that one item in the list. Then you could have code loop though the list and unhide the selected sheets and sheets in between. I don't think a combobox has a multiselect capability.

The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ComboBox1.Value >= 1 And ComboBox1.Value <= 25 <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ComboBox1.Value<br>            Sheets("Sheet1").Copy After:=ActiveSheet<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[D_Davis]

Alpha,

Thanks for the quick response. I see how the listbox could work but knowing the folks that will be using this i know i'll get some grief for doing it that way. Is there no way to accomplish it with the combobox? Again it's not to have them do a multiselect but rather to select a number "of sites" which will then unhide the number of sheets accordingly so if "5" is selected then 5 sheets should be unhidden. Sorry to be a stickler but i just want to do this "right" to the expectations of the end users.

As for the other code to copy sheets I can't get that to work. I've added it in the primary sheet which in my case is sheet7 and I've also tried it in "this workbook" but no Joy. I haven't worked with VB in at least 6 years so I'm really rusty.

Thanks

 

[D_Davis]

Alpha,

Thanks for the quick response. I see how the listbox could work but knowing the folks that will be using this i know i'll get some grief for doing it that way. Is there no way to accomplish it with the combobox? Again it's not to have them do a multiselect but rather to select a number "of sites" which will then unhide the number of sheets accordingly so if "5" is selected then 5 sheets should be unhidden. Sorry to be a stickler but i just want to do this "right" to the expectations of the end users.

As for the other code to copy sheets I can't get that to work. I've added it in the primary sheet which in my case is sheet7 and I've also tried it in "this workbook" but no Joy. I haven't worked with VB in at least 6 years so I'm really rusty.

Thanks

Also I'm not using a userform for this, just a sheet.

 

[AlphaFrog]

As for the other code to copy sheets I can't get that to work. I've added it in the primary sheet which in my case is sheet7 and I've also tried it in "this workbook" but no Joy. I haven't worked with VB in at least 6 years so I'm really rusty.

The code goes in the worksheet module that has the combobox. So if Sheet7 has the combobox on the sheet, then right-click on its' sheet tab and select View Code from the pop-up menu. Paste the code in the VBA edit window.

Also, there are two types of comboboxes that you can put on a worksheet. The ActiveX combobox.from the Control Toolbox toolbar or the Form-type from the Form toolbar. This code is for the ActiveX type combobox from the Control Toolbox toolbar.
Two types of Controls in Excel

Just for clarification on the 1st issue, if they select say 5 from the combobox, you want to unhide Sheet1 to Sheet5? Is that correct? They select a value from the combobox and you want to unhide Sheet1 to Sheet n?

 

[D_Davis]

The code goes in the worksheet module that has the combobox. So if Sheet7 has the combobox on the sheet, then right-click on its' sheet tab and select View Code from the pop-up menu. Paste the code in the VBA edit window.

Also, there are two types of comboboxes that you can put on a worksheet. The ActiveX combobox.from the Control Toolbox toolbar or the Form-type from the Form toolbar. This code is for the ActiveX type combobox from the Control Toolbox toolbar.
Two types of Controls in Excel

Just for clarification on the 1st issue, if they select say 5 from the combobox, you want to unhide Sheet1 to Sheet5? Is that correct? They select a value from the combobox and you want to unhide Sheet1 to Sheet n?

Alpha,

I'm using the Form Combobox and your clarification is exactly what i'm looking for. Thanks again for your assistance.

 

[D_Davis]

Alpha,

Do you have any other suggestions based on the clarification you wanted?

Thanks

 

[AlphaFrog]

This will unhide the first sheet to the selected numbered sheet from the ActiveX combobox ComboBox1.

This is based on the sheets being named Sheet1, Sheet2, Sheet3... etc. Change the red code to suit your sheet naming convention.

Private Sub ComboBox1_Change()
    Dim i As Integer
    If ComboBox1.Value <> "" Then
        Application.ScreenUpdating = False
        For i = 1 To ComboBox1.Value
            Sheets([COLOR="Red"]"Sheet" & i[/COLOR]).Hidden = False
        Next i
        Application.ScreenUpdating = True
    End If
End Sub

 

[D_Davis]

Alpha,

I really appreciate all the help but unfortunately i still have errors with this code. I started over and created a new activex combobox Combobox2 and my sheets are named Site1, Site2 etc. I ammended the code accordingly and get an a runtime error 438 object doesn't support this property or method. Here's the code as i entered it

Private Sub ComboBox2_Change()
    Dim i As Integer
    If ComboBox2.Value <> "" Then
        Application.ScreenUpdating = False
        For i = 1 To ComboBox2.Value
            Sheets("Site" & i).Hidden = False
        Next i
        Application.ScreenUpdating = True
    
End Sub[CODE]

It makes sense to me but it just doesn't want to work for some reason. i even created a form and added a combobox there but same result. Sorry to be a pain with this.

 

[AlphaFrog]

I had made a mistake. It should be .Visible and not .Hidden

Private Sub ComboBox2_Change()
    Dim i      As Integer
    If ComboBox1.Value <> "" Then
        Application.ScreenUpdating = False
        For i = 1 To ComboBox2.Value
            Sheets("Site" & i)[COLOR="Red"].Visible[/COLOR] = False
        Next i
        Application.ScreenUpdating = True
    [COLOR="Red"]End If[/COLOR]
End Sub