Private Sub ComboBox1_Click() loop

Question

I have a code for a ComboBox, whose purpose is to write the value of the ComboBox after selecting its value and then to reset the ComboBox. The code is as follows:

Private Sub ComboBox1_Click()
  Dim valor As String
  Dim celda As Range
  Set celda = ActiveCell
  valor = ComboBox1.Value
  ComboBox1.ListIndex = "-1"
  celda = valor
  celda.Offset(1, 0).Select
End Sub

It looks like the statement ComboBox1.ListIndex = "-1" triggers the Sub ComboBox1_Click() over and over again. This happens only some times. Any ideas how to fix it?

you declared `celda` variable as of a `Range` type -> use `Set` keyword: `Set celda = ...` — user3598756, Mar 23 '17 at 20:56
@Mat's Mug, there's a major issue still to be dealt with: the need for preventing the event handler from running on top of itself. Unmarking this as a duplicate would allow me to post a solution for that — user3598756, Mar 23 '17 at 21:13
@user3598756 the question would first need to mention that problem! As it stands it *is* an exact duplicate. Are you sure setting the list index triggers the click event? If you can repro and fix up /edit the question accordingly, feel free to vote to reopen; I'll keep the tab open and take a look after the drive home =) — Mathieu Guindon, Mar 23 '17 at 21:30
@Mat'sMug, yes I'm sure. But now I'm no longer by my PC ... Maybe tomorrow... — user3598756, Mar 23 '17 at 22:52
@user3598756 now I realized... actually I was having that issue as well, do you know how to fix it? — N. Pavon, Mar 24 '17 at 11:57
`ComboBox1.ListIndex = "-1"` (why is `-1` a string literal anyway?) is triggering the `Click` event, which makes your `Click` handler recursive and eventually (quickly enough) blows the call stack. I suggest you [edit] your question to make it specifically about this issue, and then your question can be reopened - and answered. — Mathieu Guindon, Mar 24 '17 at 13:34
@Mat'sMug I edited the question, could you please reopen it? — N. Pavon, Mar 24 '17 at 13:41

A.S.H · Answer 1 · 2017-03-26T21:22:21.713

There are many possible solutions to this problem. I suggest two solutions, one which ca solve easily this specific situation, and another that is "general" to avoid re-entrance in any subroutine.

Solution 1.

This solution is specific to your situation. You can simply check the ListIndex property before proceeding, at the first line of your sub:

    If ComboBox1.ListIndex = -1 Then Exit Sub

The routine will be entered twice, but at the second occurrence it will exit immediately, with no effect.

Solution 2.

This is a general solution to avoid re-entrance of any routine. you can define a state variable for the routine, which is a static Boolean variable that indicates whether the routine is already in the call stack, in which case you don't re-enter it.

Private Sub NoReEnter()
    Static isActive as Boolean ' <-- indicates that this routine is already in the call stack
    If isActive then Exit Sub
    isActive = True
    On Error Goto Cleanup

    '''''''''''''''''''''''''''''''''''''''''''''''''''' 
    '  .... ' Body of the routine
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
Cleanup: ' make sure to reset the state variable before exiting
    isActive = False
End Sub

Solution 2 can apply to any routine that you want to make non-recursive. Translating this solution into your code, without meddling with other potential (off-topic) issues, gives the following:

Private Sub ComboBox1_Click()
    Static isActive As Boolean
    If isActive then Exit Sub
    isActive = True
    On Error Goto Cleanup

    ' You routine's code as is
    '''''''''''''''''''''''''''''''''''''''''''''''''''' 
    Dim valor As String
    Dim celda As Range
    Set celda = ActiveCell
    valor = ComboBox1.Value
    ComboBox1.ListIndex = -1
    celda = valor
    celda.Offset(1, 0).Select
    '''''''''''''''''''''''''''''''''''''''''''''''''''' 

Cleanup:
   isActive = False
End Sub

score 0 · Answer 2 · answered Mar 26 '17 at 20:09

0

try this

Private Sub ComboBox1_Click()
  Dim valor As String
  Dim celda As Range
  Set celda = ActiveCell
  valor = ComboBox1.Value
  ComboBox1.ListIndex = 0
  celda.value=valor
End Sub

answered Mar 26 '17 at 20:09

Deb

121
1
1
12

score 0 · Answer 3 · answered Mar 27 '17 at 19:56

I believe your code might be triggering the event, try disabling events while the code runs:

Private Sub ComboBox1_Click()
  Dim valor As String
  Dim celda As Range

  Application.EnableEvents = False ' disable events

  Set celda = ActiveCell
  valor = ComboBox1.Value
  ComboBox1.ListIndex = "-1"
  celda = valor
  celda.Offset(1, 0).Select

  Application.EnableEvents = True ' re-enable events

End Sub

Private Sub ComboBox1_Click() loop

3 Answers3