I have a userform with several controls, one of which is a
ComboBoxcontaining a list of names. When I select a name, subroutines
(triggered by a ComboBox2_Click event) write data from the other controls to
a spreadsheet, and then populate the controls with new data if any exists in
the spreadsheet.
The problem is, I recently changed the ComboBox so it now has two columns;
the second column is the name (as before), and the first column is either
blank or an asterisk. I add the asterisk when the information displayed in
the userform is updated and written to the spreadsheet.
However, the Click event calls the code that includes adding the asterisk to
the ComboBox list, which triggers the Click event again. What I am
discovering is that with this looped (or loopy) event, I'm getting erratic
results (for example, when I select another name from the combobox, the rest
of the controls in the form aren't being updated.
Is there a way to programmatically turn the click event off temporarily, or
is there another solution to deal with this? (My current solution is to
forget about the asterisk updating, and when I omit this subroutine,
everything works as it should.)
Thanks for any insights.
Bert

Posting the event code for the comboboxes will allow a more conclusive
analysis of the problem.

"Bert" wrote:

> I have a userform with several controls, one of which is a
> ComboBoxcontaining a list of names. When I select a name, subroutines
> (triggered by a ComboBox2_Click event) write data from the other controls to
> a spreadsheet, and then populate the controls with new data if any exists in
> the spreadsheet.
> The problem is, I recently changed the ComboBox so it now has two columns;
> the second column is the name (as before), and the first column is either
> blank or an asterisk. I add the asterisk when the information displayed in
> the userform is updated and written to the spreadsheet.
> However, the Click event calls the code that includes adding the asterisk to
> the ComboBox list, which triggers the Click event again. What I am
> discovering is that with this looped (or loopy) event, I'm getting erratic
> results (for example, when I select another name from the combobox, the rest
> of the controls in the form aren't being updated.
> Is there a way to programmatically turn the click event off temporarily, or
> is there another solution to deal with this? (My current solution is to
> forget about the asterisk updating, and when I omit this subroutine,
> everything works as it should.)
> Thanks for any insights.
> Bert
>
>

Here's the code for the click event and the Add asterik subroutine.
This is the Click event code:
Private Sub ComboBox2_Click()
' Selects name to do next
If CB2C = True Then
CB2C = False
Exit Sub
End If
If Redo1 Then
Redo1 = False
Exit Sub
End If
If ReDo = True Then
Redo1 = True
UserForm1.ComboBox2.ListIndex = ActiveNameNo - 1
Exit Sub
End If
Call UF1_to_Student_Data(StudentRows(ActiveNameNo)) ' writes all data to
the proper name's entry
OldANNo = NamesRows(ActiveNameNo)
ActiveNameNo = UserForm1.ComboBox2.ListIndex + 1
Call PopulateForm(NamesRows(ActiveNameNo))
Call SetTimer(1000)
Call AddStar(OldASNo)
End Sub

This is the asterisk-writing code that causes the loop back to
ComboBox2_Click which called it in the first place.
Sub AddStar(s)
'add asterik if data is complete
v1 = Worksheets("Name_Data").Cells(s, LstNmClm) & ", " &
Worksheets("Name_Data").Cells(s, FstNmClm)
v2 = Worksheets("Name_Data").Cells(s, LstNmClm) & ", " &
Worksheets("Name_Data").Cells(s, NckNmClm)
If IsDone(s) Then
With UserForm1.ComboBox2
For B = 0 To .ListCount - 1
If .List(B, 1) = v1 Or .List(B, 1) = v2 Then
CB2C = True ' exit out of the ComboBox2_Click subroutine as soon
as you get there.
.List(B, 0) = "*"
Exit Sub
End If
Next
End With
End If
CB2C = False
End Sub

I've posted the code in a reply to my original post.
Thanks.
Bert

"JLGWhiz" <(E-Mail Removed)> wrote in message
news:6C9D23FF-05B1-40A3-AB00-(E-Mail Removed)...
> Posting the event code for the comboboxes will allow a more conclusive
> analysis of the problem.
>
> "Bert" wrote:
>
>> I have a userform with several controls, one of which is a
>> ComboBoxcontaining a list of names. When I select a name, subroutines
>> (triggered by a ComboBox2_Click event) write data from the other controls
>> to
>> a spreadsheet, and then populate the controls with new data if any exists
>> in
>> the spreadsheet.
>> The problem is, I recently changed the ComboBox so it now has two
>> columns;
>> the second column is the name (as before), and the first column is either
>> blank or an asterisk. I add the asterisk when the information displayed
>> in
>> the userform is updated and written to the spreadsheet.
>> However, the Click event calls the code that includes adding the asterisk
>> to
>> the ComboBox list, which triggers the Click event again. What I am
>> discovering is that with this looped (or loopy) event, I'm getting
>> erratic
>> results (for example, when I select another name from the combobox, the
>> rest
>> of the controls in the form aren't being updated.
>> Is there a way to programmatically turn the click event off temporarily,
>> or
>> is there another solution to deal with this? (My current solution is to
>> forget about the asterisk updating, and when I omit this subroutine,
>> everything works as it should.)
>> Thanks for any insights.
>> Bert
>>
>>

Unless one of these was a typo in the posting only, then it could be your
problem.

OldANNo = NamesRows(ActiveNameNo)
Call AddStar(OldASNo)

It looks like you are attempting to pass the variable OldANNo as OldASNo,
which won't work.



"Bert" wrote:

> Here's the code for the click event and the Add asterik subroutine.
> This is the Click event code:
> Private Sub ComboBox2_Click()
> ' Selects name to do next
> If CB2C = True Then
> CB2C = False
> Exit Sub
> End If
> If Redo1 Then
> Redo1 = False
> Exit Sub
> End If
> If ReDo = True Then
> Redo1 = True
> UserForm1.ComboBox2.ListIndex = ActiveNameNo - 1
> Exit Sub
> End If
> Call UF1_to_Student_Data(StudentRows(ActiveNameNo)) ' writes all data to
> the proper name's entry
> OldANNo = NamesRows(ActiveNameNo)
> ActiveNameNo = UserForm1.ComboBox2.ListIndex + 1
> Call PopulateForm(NamesRows(ActiveNameNo))
> Call SetTimer(1000)
> Call AddStar(OldASNo)
> End Sub
>
> This is the asterisk-writing code that causes the loop back to
> ComboBox2_Click which called it in the first place.
> Sub AddStar(s)
> 'add asterik if data is complete
> v1 = Worksheets("Name_Data").Cells(s, LstNmClm) & ", " &
> Worksheets("Name_Data").Cells(s, FstNmClm)
> v2 = Worksheets("Name_Data").Cells(s, LstNmClm) & ", " &
> Worksheets("Name_Data").Cells(s, NckNmClm)
> If IsDone(s) Then
> With UserForm1.ComboBox2
> For B = 0 To .ListCount - 1
> If .List(B, 1) = v1 Or .List(B, 1) = v2 Then
> CB2C = True ' exit out of the ComboBox2_Click subroutine as soon
> as you get there.
> .List(B, 0) = "*"
> Exit Sub
> End If
> Next
> End With
> End If
> CB2C = False
> End Sub
>
>

Yes, it was a typo. It should have been OldANNo in both places and was in
the actual code.
However, I went through the code and cleaned it up some, using only one
variable (SkipEvent, set to True at every point the click event might be
trigggered unnecessarily). I also replaced the "Exit Sub" commands with
"GoTo " Line label (just before the End Sub). Finally just before the line
of code that triggered the event, I added Application.EnableEvents = False
(and followed the code with Application.EnableEvents = True). This
combination of changes solved the problem.
Bert

"JLGWhiz" <(E-Mail Removed)> wrote in message
news:41B07FA5-C1F3-493F-A8BC-(E-Mail Removed)...
> Unless one of these was a typo in the posting only, then it could be your
> problem.
>
> OldANNo = NamesRows(ActiveNameNo)
> Call AddStar(OldASNo)
>
> It looks like you are attempting to pass the variable Call
> InsertComment((InsPt), (i))
as OldASNo,
> which won't work.
>
>
>
> "Bert" wrote:
>
>> Here's the code for the click event and the Add asterik subroutine.
>> This is the Click event code:
>> Private Sub ComboBox2_Click()
>> ' Selects name to do next
>> If CB2C = True Then
>> CB2C = False
>> Exit Sub
>> End If
>> If Redo1 Then
>> Redo1 = False
>> Exit Sub
>> End If
>> If ReDo = True Then
>> Redo1 = True
>> UserForm1.ComboBox2.ListIndex = ActiveNameNo - 1
>> Exit Sub
>> End If
>> Call UF1_to_Student_Data(StudentRows(ActiveNameNo)) ' writes all data
>> to
>> the proper name's entry
>> OldANNo = NamesRows(ActiveNameNo)
>> ActiveNameNo = UserForm1.ComboBox2.ListIndex + 1
>> Call PopulateForm(NamesRows(ActiveNameNo))
>> Call SetTimer(1000)
>> Call AddStar(OldASNo)
>> End Sub
>>
>> This is the asterisk-writing code that causes the loop back to
>> ComboBox2_Click which called it in the first place.
>> Sub AddStar(s)
>> 'add asterik if data is complete
>> v1 = Worksheets("Name_Data").Cells(s, LstNmClm) & ", " &
>> Worksheets("Name_Data").Cells(s, FstNmClm)
>> v2 = Worksheets("Name_Data").Cells(s, LstNmClm) & ", " &
>> Worksheets("Name_Data").Cells(s, NckNmClm)
>> If IsDone(s) Then
>> With UserForm1.ComboBox2
>> For B = 0 To .ListCount - 1
>> If .List(B, 1) = v1 Or .List(B, 1) = v2 Then
>> CB2C = True ' exit out of the ComboBox2_Click subroutine as
>> soon
>> as you get there.
>> .List(B, 0) = "*"
>> Exit Sub
>> End If
>> Next
>> End With
>> End If
>> CB2C = False
>> End Sub
>>
>>