Change Combo Box Value in VBA

[dmacmillan]

Hi,

I'm using a Combo Box to drive graphs in a Dashboard. The Combo Box consists of five values that I want to cycle every 10 seconds.

I have tried a variations of 'combobox.value' and'.ListFillRange' in 'combobox_Change()' without luck.

Thanks in advance,
David

 

[Sektor]

Try ComboBox.AddItem method.

 

[dmacmillan]

Thanks. I have tried the following code but am getting 'Run-time Error 70' .... Permission denied. For the record, Range("DateParam") is the ListFillRange for the Combobox cboC10.

Tried to use Code Tags but couldn't find them ...

Private Sub cboC10_Change0()
Dim cDateSeq As Range
Dim ws As Worksheet
Set ws = Worksheets("Parameters")

For Each cDateSeq In ws.Range("DateParam")

With cboC10
.AddItem cDateSeq.Value
End With
Next

End Sub

 

[Sektor]

If you manually entered ListFillRange (or with code) and at the same time use AddItem, then this error occurs. You need to remove all ListFillRange values from Properties (if any) and from code.

 

[dmacmillan]

Thanks for such a prompt reply. Mighty perplexed ... if I have a ListFillRange, why do I need to use the AddItem method? Is there a why of cycling through the ListFillRange values?

 

[Sektor]

You better ask Microsoft guys. ListFillRange and AddItem can't live together.

 

[Greg Truby]

This will update the LinkedCell and any dependent values and charts, etc. However the value that displays in the combobox itself does not update on the screen.

Sub RollTheList()
    Dim cbxItem As MSForms.ComboBox, _
        rngList As Excel.Range, _
        strList As String, _
        i%
 
    Set cbxItem = Sheet3.ComboBox1
 
    Let strList = cbxItem.ListFillRange
    Set rngList = Sheet3.Range(strList)
 
    For i = 0 To rngList.Count - 1
        cbxItem.ListIndex = i
        DoEvents
        Application.Wait (Now + TimeSerial(0, 0, 5))
        Debug.Print Now
    Next i
 
End Sub

It's late and I don't remember if or how one can pass a parameter using ONTIME, but that might be one way to get that combobox to show the changes.

 

[dmacmillan]

Greg,

Thank you for your efforts, I'm definitely moving in the right direction. May revert later if I encounter a snag.

Kind regards,
David

 

[Greg Truby]

I should probably point out that my code is referencing the worksheet by its CodeName and not by its Name.