Userform - Combobox Question

[vba_hugo]

Hey all,

So I'm made a userform with two comboboxes, let's call them "combobox 1" and "combobox2". I, as a user, can select an option in ONLY ONE OF THE COMBOBOXES. Once that combobox is selected I can then load a chart based on the selection.

HOWEVER, I want both comboboxes to work independently from each other, meaning that if the user chooses to select an option in combobox1, then the combobox2 cannot be used, and if the user chooses to select an option in combobox2 then combobox1 cannot be used. BUT, I don't know how to code for this. I'm only able to code so that only combobox1 can take commands and display a chart. If I try to select anything in combobox2, I don't get anything.

Thanks a lot

 

[RoryA]

Rather than using two comboboxes, why not have two option buttons that determine the contents of one combobox? Then your existing code will do what you need.

 

[My Aswer Is This]

Try this:

Private Sub ComboBox1_Click()
ComboBox2.Enabled = False

End Sub

Private Sub ComboBox2_Click()
ComboBox1.Enabled = False

End Sub

 

[vba_hugo]

So I tried what you suggested but I'm not sure how to structure it. Here's my code:

Option ExplicitPrivate Sub OptionButton1()


If OptionButton1.Value = True Then


    Dim MyChart As Chart
    Dim ChartData As Range
    Dim chartIndex As Integer
    Dim ChartName As String
    
    chartIndex = ComboBox1.ListIndex
    
    Select Case chartIndex


        Case 0
            Set ChartData = ThisWorkbook.Sheets("Static").Range("B2:B6")
            ChartName = "Loan Balance R12 " & ActiveSheet.Range("B1")
        Case 1
            Set ChartData = ThisWorkbook.Sheets("Static").Range("C2:C6")
            ChartName = "Loan Balance R12 " & ActiveSheet.Range("C1")
        Case 2
            Set ChartData = ThisWorkbook.Sheets("Static").Range("D2:D6")
            ChartName = "Loan Balance R12 " & ActiveSheet.Range("D1")
        Case 3
            Set ChartData = ThisWorkbook.Sheets("Static").Range("E2:E6")
            ChartName = "Loan Balance R12 " & ActiveSheet.Range("E1")
    End Select


Application.ScreenUpdating = False


Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart


MyChart.SeriesCollection.NewSeries
MyChart.SeriesCollection(1).Name = ChartName
MyChart.SeriesCollection(1).Values = ChartData
MyChart.SeriesCollection(1).XValues = ThisWorkbook.Sheets("Static").Range("A2:A6")
    
Dim imageName As String
imageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"


MyChart.Export Filename:=imageName, FilterName:="GIF"


ActiveSheet.ChartObjects(1).Delete


Application.ScreenUpdating = True


UserForm1.Image1.Picture = LoadPicture(imageName)
    
End Sub
Private Sub OptionButton2()


If OptionButton2.Value = True Then


    Dim MyChart2 As Chart
    Dim ChartData2 As Range
    Dim chartIndex2 As Integer
    Dim ChartName2 As String
    
    chartIndex2 = ComboBox2.ListIndex
    
    Select Case chartIndex2
        Case 0
            Set ChartData2 = ThisWorkbook.Sheets("Agg").Range("A1:A5")
            ChartName2 = "Loan Balance R12 " & ActiveSheet.Range("A6")
        Case 1
            Set ChartData2 = ThisWorkbook.Sheets("Agg").Range("B1:B5")
            ChartName2 = "Loan Balance R12 " & ActiveSheet.Range("B6")
    End Select


Application.ScreenUpdating = False


Set MyChart2 = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart


MyChart.SeriesCollection.NewSeries
MyChart.SeriesCollection(2).Name = ChartName2
MyChart.SeriesCollection(2).Values = ChartData2
MyChart.SeriesCollection(2).XValues = ThisWorkbook.Sheets("Agg").Range("A6:B6")
    
Dim imageName2 As String
imageName2 = Application.DefaultFilePath & Application.PathSeparator & "TempChart2.gif"


MyChart.Export Filename:=imageName2, FilterName:="GIF"


ActiveSheet.ChartObjects(2).Delete


Application.ScreenUpdating = True


UserForm1.Image2.Picture = LoadPicture(imageName)
    
End Sub
Private Sub UserForm_Initialize()
    ComboBox1.AddItem ("000000bis")
    ComboBox1.AddItem ("000000")
    ComboBox1.AddItem ("000011")
    ComboBox1.AddItem ("001521")
    
    
    ComboBox2.AddItem ("Agriculture")
    ComboBox2.AddItem ("MGO")
End Sub

 

[My Aswer Is This]

I see nothing in all this code about clicking on a Combobox.

If your able to write all this detailed code I would think you would know how to install this small little bit of code in your Combobox script.

I see in this script where you load data into the Combobox but I see nothing about clicking on the combobox.

 

[vba_hugo]

I see nothing in all this code about clicking on a Combobox.

If your able to write all this detailed code I would think you would know how to install this small little bit of code in your Combobox script.

I see in this script where you load data into the Combobox but I see nothing about clicking on the combobox.

Hey MyAnswerIsThis, just fixed it, thanks for your help.. forgot to add the click command.... early morning

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Hey MyAnswerIsThis, just fixed it, thanks for your help.. forgot to add the click command.... early morning