Userform - Combobox Question

vba_hugo

New Member
Joined
Aug 4, 2016
Messages
5
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 :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Last edited:
Upvote 0
Try this:

Code:
Private Sub ComboBox1_Click()
ComboBox2.Enabled = False

End Sub

Private Sub ComboBox2_Click()
ComboBox1.Enabled = False

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

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,155
Messages
6,170,403
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top