Using a form to modify a list

[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.

 

[Tom Ogilvy]

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

 

[Guest]

Tom/anyone, why does this work?

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

 

[Guest]

Thanks Tom!

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 Ogilvy]

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.