Using a form to modify a list

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

Guest

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.
 
Instead of using rowsource to populate your listbox, use code.

Private sub Userform_Initialize()
Me.ListBoxMemberDatasheetForm.List = Range("Members").Value

' or
'Me.ListBoxMemberDatasheetForm.List = Range("Members").Columns(1).Value
' depending on what is in the listbox
End Sub
 
Tom/anyone, why does this work?

Tom Ogilvy said:
Instead of using rowsource to populate your listbox, use code.

Private sub Userform_Initialize()
Me.ListBoxMemberDatasheetForm.List = Range("Members").Value

' or
'Me.ListBoxMemberDatasheetForm.List = Range("Members").Columns(1).Value
' depending on what is in the listbox
End Sub
Click to expand...
 
Thanks Tom!

Tom Ogilvy said:
Instead of using rowsource to populate your listbox, use code.

Private sub Userform_Initialize()
Me.ListBoxMemberDatasheetForm.List = Range("Members").Value

' or
'Me.ListBoxMemberDatasheetForm.List = Range("Members").Columns(1).Value
' depending on what is in the listbox
End Sub
Click to expand...
 
I can only speculate, but when you change data in the rowsource using code,
it can cause some troublesome interactions. I usually disconnect the
rowsource (if I have to have it) and make the change, then reconnect it. As
I recall, Bob Phillips posted a solution a while back that works and doesn't
disconnect the rowsource, but I don't recall what it was.

Someone else may have a better explanation.
 
Back
Top