Open tabs from combo box excel

Closed
clara - Jun 9, 2010 at 07:44 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 10, 2010 at 04:09 PM
Hi rizviza1,

Maybe you remember me , a few days ago you help me with this code:

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

Do you know what needs to be change to have this combobox in form and not on a cell in the spreadsheet as i have it right now...

Thank you Rizvisa1, and also thank you for the explanation of how the codes works

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 10, 2010 at 01:37 AM
As I might have told you then, if you use the combobox from the toolbox it is not directly accessible. First the combobox needs to be bound to a cell and then you use that cell to do what needs to be done.
If you want to use it on userform then you have to make use of rountine

Private Sub ComboBox1_Change()

End Sub

Note:ComboBox1 would be the name of the dropdown on the form
and instead of "cells(1, "A") " as in the code you would be using

combobox1.value
0
Hi Ihave tried what you told me but apparently something is wrong.

1. I used the Private Sub ComboBox1_Change() in the userform1

2. and changed the cells(1, "A") to combobox1.value

3. I used the at the dropdown properties menu the row source and entered B1:B2

But is not working :(
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 10, 2010 at 11:16 AM
Change these lines

If ComboBox1.Value = 1 Then
Target = "Tab1"

ElseIf ComboBox1.Value = 2 Then
Target = "Tab2"

End If



TO


Target = ComboBox1.Value
0
Hi, that's what I have righ now:

Private Sub ComboBox1_Change()
Dim Target As String
Dim Value As String

Target = "I am a place holder"

If ComboBox1.Value = 1 Then
Target = "Tab1"

ElseIf ComboBox1.Value = 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 = ""))
Sheets("KPI").Visible = ((Sheets("KPI").Name = Target & "A") Or (Target = ""))
End Sub

Still not working risviza1 :(. Please help me
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 10, 2010 at 04:09 PM
Please re-read my comments earlier.
0