Code to open a tabs from combox excel

[Solved/Closed]
Report
-
 Mary -
Hello,

I'm looking for a code, i can put in a combo box to show up different tabs located in my spreasheet. example: I have four tabs called tab1,tab1A,tab2 and tab2A, but only tab1 and tab 2 are shown on the combo box. My idea is that every time that I choose one of the tabs, for example tab1 it will show up tab1 and tab1A and hide tab2 and tab2A and the same if I choose the tab2, it will show up tab2 and tab2A. Could somebody help me, I would appreicate any help. :)

Mary

5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Hi, At the master tab there is a combo box, actually i would want a code for the combo box to show the tabs: tab1 and tab2, but everytime I select tab1 it will show up tab1 and tab1A and hide tab2 and tab2A. And when I select tab 2 it will show up tab2 and tab2A and hide tab1 and tab1A.

Please let me know if that is possible :)

This is the address with the file


https://authentification.site/files/22813070/master.xlsx

Mary
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You are far better off using Data Validation for drop down. The reason is that getting the value from the dropdown of the form on sheet, is a two step process.


Lets say that you go for data validation route
1. Click on sheet Master
2. Click on Data Validation
3. From the drop down titled "Allow", pick "List"
4. On the box titled "Source"", type
tab1, tab2
5. click ok

This set up basic validation

Now for the code to make sheet visible and invisible
1. Press ALT + F11
2. Press CTRL + R
3. Double click on the sheet Master
4. Paste the code

THIS CODE IS BASED ON ASSUMPTION THAT THE VALIDATION WOULD BE IN CELL A2. CORRECT THAT PART
.

 Private Sub Worksheet_Change(ByVal Target As Range)  
      
    If Target.Address = "$A$2" Then  
      
        Sheets("tab1").Visible = ((Sheets("tab1").Name = Target) Or (Target = ""))  
        Sheets("tab1A").Visible = ((Sheets("tab1A").Name = Target & "A") Or (Target = ""))  
        Sheets("tab2").Visible = ((Sheets("tab2").Name = Target) Or (Target = ""))  
        Sheets("tab2A").Visible = ((Sheets("tab2A").Name = Target & "A") Or (Target = ""))  
          
    End If  
      
End Sub
Hi again, what you just have told me is more than great,but, is there any chance that this could be done with a combo box, is just that I want to use the combo box in a form, so the first thing the user will see is the user form with the combo box, is that possible

Thanks and I really appreciate your help

Mary
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well not know how you have filled ithe combo box

Lets say combo is bound to cell A1, then have the combo box call this routine.

 Sub ComboBox_Change()
 Dim Target as String


    Target  = "I am a place holder"

    If cells(1, "A") = 1 then
        Target = "tab1"

    Elseif ( cells(1, "A") = 2) then
        Target = "tab2"

    End If
    
      
     Sheets("tab1").Visible = ((Sheets("tab1").Name = Target) Or (Target = ""))  
     Sheets("tab1A").Visible = ((Sheets("tab1A").Name = Target & "A") Or (Target = ""))  
     Sheets("tab2").Visible = ((Sheets("tab2").Name = Target) Or (Target = ""))  
     Sheets("tab2A").Visible = ((Sheets("tab2A").Name = Target & "A") Or (Target = ""))  
      
End Sub
Problem Solved, thank you : )

Mary