Open tabs from combo box excel [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
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
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 :(
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Change these lines

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

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

End If



TO


Target = ComboBox1.Value
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Please re-read my comments earlier.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!