You don't need to select a cell/row to work with it.

But I still don't know what you're doing with those variables or what they're
populated with....

I put this in a General module:

Option Explicit
Public ListBoxRng As Range

I put this behind the first userform:

Option Explicit
Private Sub UserForm_Initialize()
Call AllMembers
End Sub
Private Sub AllMembers()
'Variables for worksheet, and range
Dim ws As Worksheet
Dim myCell As Range

'Sets the worksheet for the listbox
Set ws = Worksheets("Member_list")

'Clears the listboxrng variable
Set ListBoxRng = Nothing

With ws
If IsEmpty(.Range("a2")) Then
Me.lblTotalNo = "There are no members in the Database"
ElseIf IsEmpty(.Range("a3")) Then
Set ListBoxRng = .Range("a2")
Else
Set ListBoxRng = .Range(.Range("A2"), .Range("A2").End(xlDown))
End If
End With

If ListBoxRng Is Nothing Then
Me.lblTotalNo = "There are no members in the Database"
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
'don't forget to change this!
.ColumnCount = 4
For Each myCell In ListBoxRng.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 4).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 5).Value
.List(.ListCount - 1, 3) = myCell.Offset(0, 10).Value
Next myCell
End With
End If

End Sub

Private Sub lstMembers_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim ws As Worksheet
Dim iRow As Long
Dim ListType As String
Dim MemberType As Long
Dim mySelectedCell As Range

With Me.lstMembers
If .ListIndex < 0 Then
Beep
Cancel = True
Exit Sub
End If

Application.ScreenUpdating = False
Set mySelectedCell = ListBoxRng.Resize(1).Offset(.ListIndex)

'pick up the 3rd column of the listbox????????
ListType = CLng(.List(.ListIndex, 2))

'or pick it up from the member_list worksheet
ListType = CLng(mySelectedCell.Offset(0, 8).Value)

Load frmMember

Select Case ListType
Case 1 'Members
Set ws = Worksheets("Member_list")
MemberType = 3
frmMember.txtMemID.Value = ListType
Case 2 'Deacons
ws = Worksheets("Deacon_list")
End Select
Me.Hide
frmMember.Show
Application.ScreenUpdating = True

End With

End Sub

I guessed you were either picking up the value from the userform or from the
worksheet.

One of these may help, but you won't need both:

'pick up the 3rd column of the listbox????????
ListType = CLng(.List(.ListIndex, 2))

'or pick it up from the member_list worksheet
ListType = CLng(mySelectedCell.Offset(0, 8).Value)

But I still don't know...

bach wrote:

Dave,

That code was my attempt at starting it, I throught I would need to
select the entire row of data so when the frm loads all the texts and
other objects can be populated with the data (14 objects).

Not sure through

--
bach
------------------------------------------------------------------------
bach's Profile: http://www.excelforum.com/member.php...o&userid=26134
View this thread: http://www.excelforum.com/showthread...hreadid=468634

--

Dave Peterson