[RESOLVED] Move to next item in ActiveX combobox on excel worksheet

[tepedekoy]

Hi there,

I have an ActiveX combobox on a worksheet populated from one column of cells on that worksheet. I have i set up if the first item is selected a form will open to allow the user to enter customised values. The other selections are all preset. The other option on the form is a check box to save this data.

The trigger for the userform is Private Sub CboxCircuit_Change()
ie. A change to the combobox

The problem I have is when I try to save these new values, the cells I am saving to are referenced to the combobox. So when I paste the data to these cells, it treats it as a combobox_change, and because my current value in the combobox is still set to the first item, which triggers the form to open, I end up in a loop, with the form opening again.

The solution, I believe, is to change the combobox value to the next item in the list and then run my save procedure.

My question is...how do I do this?
I have tried
CboxCircuit.value = Sheet1.Range("J7").Value 'the next item in the list (run-error '424')
CboxCircuit.ListIndex = CboxCircuit.ListIndex + 1 (same error)

Is there a .offset property I can use?

Any help would be greatly appreciated.

[westconn1]

if the code is in the forms code page you would need to specify the sheet the cboxcircuit is on
sheets("mysheet").cboxcircuit......

[anhn]

I couldn't find a better way, but you can use a flag to find out how the LinkedCell and then the CBox have been changed.

Code on Sheet1 that contains the combobox:

Option Explicit

Dim bFocusOnCboxCircuit As Boolean

Private Sub CboxCircuit_GotFocus()
    bFocusOnCboxCircuit = True
End Sub

Private Sub CboxCircuit_LostFocus()
    bFocusOnCboxCircuit = False
End Sub

Private Sub CboxCircuit_Change()
    If bFocusOnCboxCircuit = True Then
        If CboxCircuit.ListIndex = 0 Then
            UserForm1.Show
        End If
    End If
End Sub

*Notes: Make sure the combobox has no focus at the beginning when bFocusOnCboxCircuit = False by selecting a cell.

[tepedekoy]

Thanks for the help. Westconn's suggestion to include the sheet name that the combo box is on worked.