Code looping when it should not

[Todd Huttenstine]

Hey guys

I have a userform. On the form is combobox4. I have a
change event for the combobox and a drpdownclick event for
the combobox. Below is the code in the dropdown event:

ComboBox4.Clear
ComboBox4.AddItem "Select File to Publish"
ComboBox4.AddItem "All Files"
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(TextBox16.Value)
Set fc = f.Files
For Each f1 In fc
ComboBox4.AddItem f1.Name
' s = s & f1.Name
' s = s & vbCrLf
Next
' MsgBox s

This code populates the dropdown box with values. When I
select a value the combobox change event then runs. This
is where the problem happens. Instead of running ONLY the
combobox change event, after it runs it, it then goes back
to the dropdownbutton click event and runs that. Because
it runs it again, it clears out what I just selected in
the combobox. Then the code quits and leaves me with an
empty combobox. How do I fix this?


Todd

 

[Bob Phillips]

Todd,

This is because the other event is being triggered. This is similar to
workbook and worksheet events which can be controlled by setting the
application enableevents property. This doesn't work for forms, so you have
to manage it yourself. One way is to have a module level variable and set
that in the event code, something like this

Dim fCombobox1 As Boolean

Private Sub Combobox1_Change()

If Not fCombobox1 Then
fCombobox1 = True
'do your thing
fCombobox1 = False
End If

End Sub


Private Sub Combobox1_DropButtonClick()

If Not fCombobox1 Then
fCombobox1 = True
'do your thing
fCombobox1 = False
End If

End Sub

This way, as the change event sets the flag immediately on entry, any firing
of other events will do nothing as it also checks that flag, and exits
because it is set.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

Hey Bob

Take a look below at what I did. I stored the value of
combobox4 in a variable called StrVal at the start of the
code, so when the change event exits and then comes back
in the dropdown event procedure, the combobox value is
stored before it is cleared again. At the very end of the
code I set the combobox back to its original value by
setting it equal to variable StrVal. So far I have not
had any problems... Do you forsee any problems with this
method?

Dim StrVal

StrVal = ComboBox4.Value
ComboBox4.Clear
ComboBox4.AddItem "Select File to Publish"
ComboBox4.AddItem "All Files"
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(TextBox16.Value)
Set fc = f.Files
For Each f1 In fc
ComboBox4.AddItem f1.Name
' s = s & f1.Name
' s = s & vbCrLf
Next
' MsgBox s
ComboBox4.Value = StrVal

 

[Bob Phillips]

Todd,

I haven't been worked through all of the possible combinations, but the
thought that immediately occurs to me is that your approach could still
trigger other events for that control, and they well work the whole way
through, whereas my approach was to ensure that the other events were
effectively bypassed.

The other thing to be aware of (just thought of this), your code may be
dependent upon the value of the control, and if another event changes it,
your logic may be skewed!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bill Hoffman]

Todd,

I was having the same problem, but your idea of saving and
restoring the combobox value did not work. I finally
solved it as follows:
1. create a global variable called reset
2. initialize reset in the form's activate routine:
reset = true
3. at the beginning of the DropButtonClick routine, place
the following code.
if not reset then
exit sub
end if
reset = false

Now the combobox works as it should. The list remains
intact and can be reused, and the selected item is shown
in the combobox value line.

Bill Hoffman