Combo Box Not Working Unless Run in VBA

[jbiehl]

Hello all!
I have a few ActiveX combo boxes that don't work unless I open the VBA and click "Run". After I do that, they work fine until I close the workbook. Any thoughts on how I might get these to work all of the time?
I have other ActiveX command buttons in the same workbook that use a "click" code in VBA and they work every as soon as I open the workbook without opening VBA.

I tried both the "click" and "change" commands below.

Private Sub CB_ST_GRA_Change()
CB_ST_STA.Clear
CB_ST_GRA.List = Array("Kindergarten", "1st Grade", "2nd Grade", "3rd Grade", "4th Grade", "5th Grade", "6th Grade", "7th Grade", "8th Grade", "9th Grade", "10th Grade", "11th Grade", "12th Grade")
End Sub

Sub CB_ST_GRA_click()
CB_ST_STA.Clear
CB_ST_GRA.List = Array("Kindergarten", "1st Grade", "2nd Grade", "3rd Grade", "4th Grade", "5th Grade", "6th Grade", "7th Grade", "8th Grade", "9th Grade", "10th Grade", "11th Grade", "12th Grade")
End Sub

Any ideas about what might be happening are appreciated!
Thanks!

 

[Yongle]

The code does exactly what it is instructed to do when I change the value of combobox CB_ST_GRA
- it clears the dropdown list from STA and refreshes the list in GRA with values from a static array

Private Sub CB_ST_GRA_Change()
'clear dropdown options in CB_ST_STA
    CB_ST_STA.Clear
'refresh dropdown options in CB_ST_GRA
    CB_ST_GRA.List = Array("Kindergarten", "1st Grade", "2nd Grade", "3rd Grade", "4th Grade", "5th Grade", "6th Grade", "7th Grade", "8th Grade", "9th Grade", "10th Grade", "11th Grade", "12th Grade")
End Sub

1. Please confirm that is what you want it to do
- why refresh a combobox list with a static list after its value has changed?
- refreshing with a dynamic list would be more usual so that the range of options changes before the user selects again
- OR refreshing a list in a different combobox list ready for the user hopping to the next combobox

2. If that is indeed what you want, then please test the code in a new workbook ...
... add 2 comboboxes with the same names
... add the code above and the code below (in sheet code window)
... run code below to place those values in the respective dropdowns
... click on both dropdowns to confirm that STA = A,B and GRA = 1,2,3
... amend the value in GRA
... check both dropdowns

Sub Load_Options()
    CB_ST_STA.List = Array("A", "B")
    CB_ST_GRA.List = Array(1, 2, 3)
End Sub

 

[jbiehl]

Thanks for your quick response. That has the same issue. It works beautifully, until I close and reopen the workbook.
I guess I don't need to refresh that combo box every time. I started doing that way because it was originally going to change based on another sheet, but I decided to keep all grades in it.
I still want CB_ST_STA to clear each time, because that is getting populated based on CB_ST_GRA and another combo box, CB_ST_SUB that I left out for simplicity's sake.
My goal is really just for the user to be able to drill down to a certain standard, based on the chosen grade and subject. The "Standards" worksheet has a list of all standards, with the grade level in column A, the subject in column B, and the standard in column D.

*I'm on the fence about whether I want the subject to clear each time the grade is changed, even though the subjects are staying the same. However, the way I have the standards box set up, it won't populate until the subject box is clicked. If they just changed the grade and not the subject, the standards box wouldn't be refreshed.

Thanks again!

Option Explicit

Private Sub CB_ST_GRA_Change()
CB_ST_SUB.Clear
CB_ST_STA.Clear
End Sub

Sub Load_Options()
    CB_ST_SUB.List = Array("ELA", "Math", "Science", "Social Studies")
    CB_ST_GRA.List = Array("Kindergarten", "1st Grade", "2nd Grade", "3rd Grade", "4th Grade", "5th Grade", "6th Grade", "7th Grade", "8th Grade", "9th Grade", "10th Grade", "11th Grade", "12th Grade")
End Sub

Private Sub CB_ST_sub_click()
'Populate standard based on domain
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("Standards")
Dim i As Integer
Me.CB_ST_SUB.List = Array("ELA", "Math", "Science", "Social Studies")
Me.CB_ST_STA.Clear
For i = 2 To Sh.Range("d" & Application.Rows.Count).End(xlUp).Row
    If Sh.Range("b" & i).Value = Me.CB_ST_SUB And Sh.Range("a" & i).Value = Me.CB_ST_GRA Then
          Me.CB_ST_STA.AddItem Sh.Range("d" & i)
    End If
Next i
End Sub

Here's a snip of the Standards sheet that this is pulling from.

 

[Yongle]

I understand why you are having the problem and it is easy to fix.
Before I suggest anything ...
As I see it, the the drop-down values in each of the 3 comboboxes is either static or is determined by a dynamic range.
So we should be able to use 1 consistent rule for each combobox.
Is that correct?



Before I explain what you need to do ..

 

[jbiehl]

That's correct.

 

[jbiehl]

I understand why you are having the problem and it is easy to fix.
Before I suggest anything ...
As I see it, the the drop-down values in each of the 3 comboboxes is either static or is determined by a dynamic range.
So we should be able to use 1 consistent rule for each combobox.
Is that correct?



Before I explain what you need to do ..

That's correct.

 

[jbiehl]

I still haven't found a solution to this. Are there any ideas out there?
Thanks!