Hair Pulling Problem...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have spent way to much time on this. I wish my programming skills were much better...

I have a form with 3 textboxes and 1 listbox. The listbox is populated with items from a range. When the listbox is clicked, it populates the textboxes. This works great.

Private Sub Data_LtBx_Click()
Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex
PrevMRN_TxBx.Value = SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value
PrevName_TxBx.Value = SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value
PrevDate_TxBx.Value = SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value
End Sub

Now if I change the first textbox "PrevMRN_TxBx" and use the following sub:

Private Sub Change_CoBn_Click()
Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex

SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value = PrevMRN_TxBx.Value
SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value = PrevName_TxBx.Value
SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value = PrevDate_TxBx.Value
End Sub

The change is made and I am a happy customer, but if I change either of the other 2 textboxes, the values are not changed. I have run several tests and the numbers for the offsets are correct, but no update.

What am I doing wrong? (Besides trying to program)
 
Troy,

The problem is that the commandbutton is triggering the Listbox click event,
causing it to be refreshed again.

Try this version

Dim fReEnter As Boolean

Private Sub Data_LtBx_Click()
If Not fReEnter Then

fReEnter = True

Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex
PrevMRN_TxBx.Value = SourceData.Offset(SourceIndex, 0).Resize(1,
1).Value
PrevName_TxBx.Value = SourceData.Offset(SourceIndex, 4).Resize(1,
1).Value
PrevDate_TxBx.Value = SourceData.Offset(SourceIndex, 10).Resize(1,
1).Value

fReEnter = False

End If
End Sub

Private Sub Change_CoBn_Click()
If Not fReEnter Then

fReEnter = True

Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex

SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value =
PrevMRN_TxBx.Value
SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value =
PrevName_TxBx.Value
SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value =
PrevDate_TxBx.Value
Application.EnableEvents = True

fReEnter = False

End If
End Sub



--

HTH

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

Troy said:
I have spent way to much time on this. I wish my programming skills were much better...

I have a form with 3 textboxes and 1 listbox. The listbox is populated
Click to expand...
with items from a range. When the listbox is clicked, it populates the
textboxes. This works great.
Private Sub Data_LtBx_Click()
Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex
PrevMRN_TxBx.Value = SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value
PrevName_TxBx.Value = SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value
PrevDate_TxBx.Value = SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value
End Sub

Now if I change the first textbox "PrevMRN_TxBx" and use the following sub:

Private Sub Change_CoBn_Click()
Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex

SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value = PrevMRN_TxBx.Value
SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value = PrevName_TxBx.Value
SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value = PrevDate_TxBx.Value
End Sub

The change is made and I am a happy customer, but if I change either of
Click to expand...
the other 2 textboxes, the values are not changed. I have run several tests
and the numbers for the offsets are correct, but no update.
 
This worked for me. Trying to change data in the rowsource can be
troublesome:

Private Sub Change_CoBn_Click()
Set SourceData = Range(Data_LtBx.RowSource)
sRwSource = Data_LtBx.RowSource
SourceIndex = Data_LtBx.ListIndex
Data_LtBx.RowSource = ""
idex = Data_LtBx.ListIndex
SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value =
PrevMRN_TxBx.Value
SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value =
PrevName_TxBx.Value
SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value =
PrevDate_TxBx.Value
Data_LtBx.RowSource = sRwSource
Date_ListIndex = idex
End Sub

--
Regards,
Tom Ogilvy


Troy said:
I have spent way to much time on this. I wish my programming skills were much better...

I have a form with 3 textboxes and 1 listbox. The listbox is populated
Click to expand...
with items from a range. When the listbox is clicked, it populates the
textboxes. This works great.
Private Sub Data_LtBx_Click()
Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex
PrevMRN_TxBx.Value = SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value
PrevName_TxBx.Value = SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value
PrevDate_TxBx.Value = SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value
End Sub

Now if I change the first textbox "PrevMRN_TxBx" and use the following sub:

Private Sub Change_CoBn_Click()
Set SourceData = Range(Data_LtBx.RowSource)
SourceIndex = Data_LtBx.ListIndex

SourceData.Offset(SourceIndex, 0).Resize(1, 1).Value = PrevMRN_TxBx.Value
SourceData.Offset(SourceIndex, 4).Resize(1, 1).Value = PrevName_TxBx.Value
SourceData.Offset(SourceIndex, 10).Resize(1, 1).Value = PrevDate_TxBx.Value
End Sub

The change is made and I am a happy customer, but if I change either of
Click to expand...
the other 2 textboxes, the values are not changed. I have run several tests
and the numbers for the offsets are correct, but no update.
 
Back
Top