G
Guest
I have a userform with textboxes for the user to enter data then click a save
button. This moves the data to a worksheet (hidden) and also into a listbox
on the same userform. (listbox tied to worksheet)
Also on the same userform is a set of textboxes that reflect calcuation
results from the worksheet. Each has a single cell set as the control source.
All that works fine. I type into the textboxes, click the save button, the
data moves into the listbox and the textboxes are updated with the
calculation results.
I wanted to add a way to edit the data already saved to the listbox. When
the user double-clicks an item in the listbox, the data is loaded back into
the textboxes it was orginally entered in. and is removed from the the
listbox(worksheet)
Here is the problem:
Now when I click the save button, the data moves back into the listbox but
the textboxes are not updated with the calculated results.? I ran this with
the worksheet visible and can see the controlsource cells update but the
textbox values will not chage.
Any help would be greatly appreciated!
Here is the code I added as a way to edit the data
Private Sub lbCurrent_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'when an item is double clicked in the 'current transactions' listbox
'populate the enter a transaction area with the entries for editing
Dim Rowcnt As Long
Dim r As Long
Rowcnt = 0
For r = 0 To lbCurrent.ListCount - 1
If lbCurrent.Selected(r) Then
Rowcnt = r + 11
ufTrustEntry.tbDate.Value = Sheet4.Range("a" & Rowcnt).Value
ufTrustEntry.cbTransaction.Value = Sheet4.Range("d" & Rowcnt).Value
ufTrustEntry.cbAccount.Value = Sheet4.Range("c" & Rowcnt).Value
ufTrustEntry.cbCustomer.Value = Sheet4.Range("b" & Rowcnt).Value
ufTrustEntry.tbAmount.Value = Sheet4.Range("e" & Rowcnt).Value
ufTrustEntry.tbAddInfo.Value = Sheet4.Range("g" & Rowcnt).Value
'Now remove the transaction from the pending list
Dim rng As Range
Set rng = Range(lbCurrent.RowSource)
lbCurrent.RowSource = ""
Sheet4.Range("a" & Rowcnt).EntireRow.Delete
lbCurrent.RowSource = rng.Resize(rng.Rows.Count -
1).Address(external:=True)
Sheet4.Range("n1", "v60").ClearContents
SetupCurrentCalculate
SetupCurrent3083
End If
Next r
Set rng = Nothing
End Sub
button. This moves the data to a worksheet (hidden) and also into a listbox
on the same userform. (listbox tied to worksheet)
Also on the same userform is a set of textboxes that reflect calcuation
results from the worksheet. Each has a single cell set as the control source.
All that works fine. I type into the textboxes, click the save button, the
data moves into the listbox and the textboxes are updated with the
calculation results.
I wanted to add a way to edit the data already saved to the listbox. When
the user double-clicks an item in the listbox, the data is loaded back into
the textboxes it was orginally entered in. and is removed from the the
listbox(worksheet)
Here is the problem:
Now when I click the save button, the data moves back into the listbox but
the textboxes are not updated with the calculated results.? I ran this with
the worksheet visible and can see the controlsource cells update but the
textbox values will not chage.
Any help would be greatly appreciated!
Here is the code I added as a way to edit the data
Private Sub lbCurrent_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'when an item is double clicked in the 'current transactions' listbox
'populate the enter a transaction area with the entries for editing
Dim Rowcnt As Long
Dim r As Long
Rowcnt = 0
For r = 0 To lbCurrent.ListCount - 1
If lbCurrent.Selected(r) Then
Rowcnt = r + 11
ufTrustEntry.tbDate.Value = Sheet4.Range("a" & Rowcnt).Value
ufTrustEntry.cbTransaction.Value = Sheet4.Range("d" & Rowcnt).Value
ufTrustEntry.cbAccount.Value = Sheet4.Range("c" & Rowcnt).Value
ufTrustEntry.cbCustomer.Value = Sheet4.Range("b" & Rowcnt).Value
ufTrustEntry.tbAmount.Value = Sheet4.Range("e" & Rowcnt).Value
ufTrustEntry.tbAddInfo.Value = Sheet4.Range("g" & Rowcnt).Value
'Now remove the transaction from the pending list
Dim rng As Range
Set rng = Range(lbCurrent.RowSource)
lbCurrent.RowSource = ""
Sheet4.Range("a" & Rowcnt).EntireRow.Delete
lbCurrent.RowSource = rng.Resize(rng.Rows.Count -
1).Address(external:=True)
Sheet4.Range("n1", "v60").ClearContents
SetupCurrentCalculate
SetupCurrent3083
End If
Next r
Set rng = Nothing
End Sub