Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a form to modify a list
i have a range of data named members in a worksheet, sheet1. when the
userform loads, a listbox on the form is filled with data from "members". on the Click event for the list box, a series of textboxes are filled with data from the range "members" by this code: Private Sub LBMemberDatasheetForm_Click() Dim RowRange As Range Set RowRange = Range("Members").Rows _ (Me.ListBoxMemberDatasheetForm.ListIndex + 1) If Me.ListBoxMemberDatasheetForm.ListIndex < -1 Then With MemberDatasheetForm .TBMemDataAddress1.Text = RowRange.Columns(1).Value .TBMemDataAddress2.Text = RowRange.Columns(2).Value .TBMemDataApt.Text = RowRange.Columns(3).Value .TBMemDataCity.Text = RowRange.Columns(4).Value .TBMemDataState.Text = RowRange.Columns(5).Value .TBMemDataZip.Text = RowRange.Columns(6).Value .TBMemDataPhone.Text = RowRange.Columns(7).Value .TBMemDataMobile.Text = RowRange.Columns(8).Value .TBMemDataEmail.Text = RowRange.Columns(9).Value End With End If End Sub This code works fine, and fast to fill the 9 text boxes with data. i have another macro that runs when the user clicks ok. this takes the information from the textboxes, and back to the record indicated by RowRange: Private Sub MemDataOKButton_Click() Dim RowRange As Range Set RowRange = Range("Members").Rows _(ListBoxMemberDatasheetForm.ListIndex + 1) If ListBoxMemberDatasheetForm.ListIndex < -1 Then With MemberDatasheetForm RowRange.Columns(1).Value = .TBMemDataAddress1.Text RowRange.Columns(2).Value = .TBMemDataAddress2.Text RowRange.Columns(3).Value = .TBMemDataApt.Text RowRange.Columns(4).Value = .TBMemDataCity.Text RowRange.Columns(5).Value = .TBMemDataState.Text RowRange.Columns(6).Value = .TBMemDataZip.Text RowRange.Columns(7).Value = .TBMemDataPhone.Text RowRange.Columns(8).Value = .TBMemDataMobile.Text RowRange.Columns(9).Value = .TBMemDataEmail.Text End With End If This macro only works on the first text box. meaning that it only updates the 1st column in RowRange. if i modify Address2, then click ok, the textbox goes back to blank, and does not update the record. please help. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to modify a list | Excel Discussion (Misc queries) | |||
modify drop down list | Excel Discussion (Misc queries) | |||
Modify Automatic Form | Excel Discussion (Misc queries) | |||
Replace/modify standard data form? | Excel Discussion (Misc queries) | |||
Can I create a FORM (that I can modify etc) from an existing spreadsheet? | Excel Programming |