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

--
Regards,
Tom Ogilvy

"Spencer Hutton" wrote in message
...
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/anyone, why does this work?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Spencer Hutton" wrote in message
...
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.

Thanks Tom!

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Spencer Hutton" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy


"Spencer Hutton" wrote in message
...
Tom/anyone, why does this work?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Spencer Hutton" wrote in
message
...
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.