Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping when it should not
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping when it should not
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping when it should not
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 -----Original Message----- 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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping when it should not
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) "Todd Huttenstine" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
stopping code from looping | Excel Worksheet Functions | |||
Looping macros using VB code | Excel Discussion (Misc queries) | |||
"Looping" Code | Excel Programming | |||
VBA code for looping through open workbooks and worksheets | Excel Programming |