Copying information from listbox into textboxes in new form

[bostenson]

Hello all...yet again,

Here is the issue, I have a userform titled General Ledger with a listbox that displays entered general ledger accounts. When the user selects a record in the listbox and then clicks an edit command, a new form opens and the data populates the textboxes in the form so the data can be edited and re-saved in the worksheet titled GeneralLedger as well as the worksheet that displays the G/L data in the listbox. So far, the code I have for populating the textboxes in the Edit G/L Userform is the following:


Private Sub UserForm_Initialize()

Me.txbEditGL.Value = Application.WorksheetFunction.Match(Me.lbxGeneralLedger.List(Me.lbxGeneralL edger.ListIndex, 0), ThisWorkbook.Sheets("GeneralLedger").Range("A:A"), 0)

Me.cmbEditGLCategory.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 0)
Me.txbEditGLName.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 1)
Me.txbEditGLBeginBal.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 2)
Me.txbEditGLMonthTotalDue.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 3)
Me.txbEditGLCreditLimit.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 4)
Me.cmbEditGLTermCode.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 5)
Me.cbxEditGLAutoWithdrawal.Value = Me.lbxGeneralLedger.List(Me.lbxGeneralLedger.List, 6)

End Sub



Where I am lost is how to source the data in the listbox - i.e. Me.lbxGeneralLedger - to populate the textboxes in the userform. But I am also not certain that my approach is the best. What recommendations are available? Thanks in advance.

-Ben

[SamT]

Your explanation is very confusing. You mention 2 UserForms and a Worksheet Form. Your Code only references the Edit G/L UserForm. (Note that I am assuming that sub
UserForm_Initialize is a UserForm Event Sub and not a Worksheet sub that you are Calling from another Procedure.)

The Keyword "Me" in the Initialize Event Sub is only referring to the same UserForm, (Code Module,) as the Initialize Event Sub is located in, not to the UserForm Captioned/Titled "General ledger.". If it is, in fact, a Worksheet Sub, then "Me" refers to the Worksheet. In a Standard Module "Me" is meaningless. In a Class Module, "Me" refers to the "Class" Object. (Note: Workbooks, Worksheets, and UserForms are Class Objects.)



Once a UserForm is INSTANTIATED/SHOWn, it stays in Memory until it is UNLOADed, That means that you can Retrieve the Account Name from the UserForm Captioned "General Ledger" by... (I am going to use "frmGenLedger", (AKA, UserForm.Name,) as the name of the Code Module that contains the code for the UserForm Captioned "General Ledger.")


Dim AcctName as String
AcctName = frmGenLedger.lbxGeneralLedger.Value
UnLoad frmGenLedger

[Paul_Hossler]

Where I am lost is how to source the data in the listbox - i.e. Me.lbxGeneralLedger - to populate the textboxes in the userform. But I am also not certain that my approach is the best. What recommendations are available? Thanks in advance.

Hard to just guess

Can you make a very simplified workbook and attach it here with the user forms, some sample data, and an example/description of what you're looking to do

[bostenson]

27904

Above is the file. The way it works: frmGeneralLedger has a populated listbox of different general ledger names. Sorry for the small pictures

27905

When a G/L name is hi-lighted in the listbox the user should be able to click the edit command and edit the information in a new userform titled frmEditGeneralLedger.

27906

However, I cannot figure how to populate the text and combobox's with data that is based upon the selection in frmGeneralLedger's listbox. I should note, the listbox lists data stored in shtGeneralLedger and displays data saved to shtGLDisplay. I have written some code into frmEditGeneralLedger but I know it is not correct, as I receive an run-time error when I click on the Edit command button in frmGeneralLedger. Below is the code I am attempting to use.


Private Sub UserForm_Initialize()

txbEditGL.Value = Application.WorksheetFunction.Match(frmGeneralLedger.lbxGeneralLedger.List( frmGeneralLedger.lbxGeneralLedger.ListIndex, 0), ThisWorkbook.Sheets("GeneralLedger").Range("A:A"), 0)


cmbEditGLCategory.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 0)
txbEditGLName.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 1)
txbEditGLBeginBal.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 2)
txbEditGLMonthTotalDue.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 3)
txbEditGLCreditLimit.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 4)
cmbEditGLTermCode.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 5)
cbxEditGLAutoWithdrawal.Value = frmGeneralLedger.lbxGeneralLedger.List(frmGeneralLedger.lbxGeneralLedger.Li st, 6)


End Sub

[Paul_Hossler]

Not sure I see wherre all the dta comes from, but try something like this



Option Explicit


Private Sub UserForm_Initialize()
Dim i As Long
Dim r As Range


With frmGeneralLedger
i = Application.WorksheetFunction.Match(.lbxGeneralLedger.Value, Sheets("GeneralLedger").Range("A:A"), 0)
Set r = Sheets("GeneralLedger").Rows(i)

' 1 2 3 4 5 6 7 8
'Category GL Name BegBal Monthly CreditLim Term Auto CurBal


cmbEditGLCategory.Value = r.Cells(1, 1).Value
txbEditGLName.Value = r.Cells(1, 2).Value
txbEditGLBeginBal.Value = r.Cells(1, 3).Value
txbEditGLMonthTotalDue.Value = r.Cells(1, 8).Value
txbEditGLCreditLimit.Value = r.Cells(1, 5).Value
cmbEditGLTermCode.Value = r.Cells(1, 6).Value
cbxEditGLAutoWithdrawal.Value = 999.99
End With


End Sub

[bostenson]

Thanks Paul, this is the code I came up with:


Dim i As Long Dim r As Range

Dim GL As Worksheet
Set GL = ThisWorkbook.Sheets("GeneralLedger")

With frmGeneralLedger
i = Application.WorksheetFunction.Match(.lbxGeneralLedger.Value, Sheets("GeneralLedger").Range("A:A"), 0)
Set r = Sheets("GeneralLedger").Rows(i)

cmbEditGLCategory.Value = r.Cells(1, 1).Value
txbEditGLName.Value = r.Cells(1, 2).Value
txbOrigGLName.Value = r.Cells(1, 2).Value
txbEditGLBeginBal.Value = r.Cells(1, 3).Value
txbEditGLMonthTotalDue.Value = r.Cells(1, 4).Value
txbEditGLCreditLimit.Value = r.Cells(1, 5).Value
cmbEditGLTermCode.Value = r.Cells(1, 6).Value

If r.Cells(1, 7).Value = "X" Then
cbxEditGLAutoWithdrawal.Value = True
Else
cbxEditGLAutoWithdrawal.Value = False
End If
End With

And the data does come from shtGeneralLedger. shtGLDisplay was only a display sheet for the listbox that can be sorted according to name or category.