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" wrote in message
...
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" wrote in message
...
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