listbox click event help

[Michael Malinsky]

I have a userform on which I have TextBox1, TextBox2, TextBox3,
ListBox1, and CommandButton1. The user enters info into the three
textboxes then clicks CommandButton1 to update ListBox1 with the data
as shown in the following code:

'If no selection is made in the listbox then create a new
listbox item...
If ListBox1.ListIndex = -1 Then
ListBox1.AddItem TextBox1.Value
ListBox1.List(ListBox1.ListCount - 1, 1) =
TextBox2.Value
ListBox1.List(ListBox1.ListCount - 1, 2) =
TextBox3.Value
'Else if a selection is made in the listbox, update the
values
'to those in the textboxes.
Else
ListBox1.List(ListBox1.ListIndex, 1) =
TextBox1.Value
ListBox1.List(ListBox1.ListIndex, 2) =
TextBox2.Value
ListBox1.List(ListBox1.ListIndex, 3) =
TextBox3.Value
End If

My problem is that I have a ListBox1_Click event that, when clicked,
populates TextBox1, TextBox2, and TextBox3 with the information from
the row selected in ListBox1 as follows:

Private Sub ListBox1_Click()

If ListBox1.ListIndex <> -1 Then
TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 1)
TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 2)
TextBox3.Value = ListBox1.List(ListBox1.ListIndex, 3)
End If

End Sub

So when I select a row from ListBox1, TextBox1, TextBox2, and TextBox3
are populated. The idea is to be able to populate the textboxes,
modify them, then click CommandButton1 to update the information on
that line. It appears that the ListBox1_Click event is being fired
again when the data is modified, reverting to the original amounts as
opposed to the updated amounts. I can update ListBox1 without a
problem if I do not have the ListBox1_Click event, but this is
something I would like to have.

Thanks,
Mike.

 

[RB Smissaert]

How about make a (private) variable, something like
bCancelClick as Boolean and set this to True when the
command button code runs and make it False again after
the command button code has finished.

Then in your click event have:
If bCancelClick Then
Exit Sub
End If

RBS

 

[Michael Malinsky]

I'm not sure I follow...can you expand on your response?

Thanks,
Mike.

 

[RB Smissaert]

Yes, your code will be like this:

At the top (in the declarations section) of your form code have this:

private bCancelClick as Boolean

Then your altered code will be:

In the commandbutton:
----------------------------------

bCancelClick = True

'If no selection is made in the listbox then create a new
listbox item...
If ListBox1.ListIndex = -1 Then
ListBox1.AddItem TextBox1.Value
ListBox1.List(ListBox1.ListCount - 1, 1) =
TextBox2.Value
ListBox1.List(ListBox1.ListCount - 1, 2) =
TextBox3.Value
'Else if a selection is made in the listbox, update the
values
'to those in the textboxes.
Else
ListBox1.List(ListBox1.ListIndex, 1) =
TextBox1.Value
ListBox1.List(ListBox1.ListIndex, 2) =
TextBox2.Value
ListBox1.List(ListBox1.ListIndex, 3) =
TextBox3.Value
End If

bCancelClick = False


In the Click event:
------------------------------

Private Sub ListBox1_Click()

If bCancelClick Then
Exit Sub
End If

If ListBox1.ListIndex <> -1 Then
TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 1)
TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 2)
TextBox3.Value = ListBox1.List(ListBox1.ListIndex, 3)
End If

End Sub


RBS

 

[Michael Malinsky]

Thanks...after I sent my last post, I was messing around with it and
came up with the same solution. My initial confusion was regarding the
placement of the

Private bCancelClick as Boolean

Once I figured that out I followed the rest.

Thanks again.