Hi,

After some searching on the forum I found a article which was what I
was looking for.

WHAT AM I DOING

I have a sheet with data on it, I want to populate a list box with data
displayed on this sheet.

CODE CURRENTLY USING


Code:
--------------------

Dim ws As Worksheet
Set ws = Worksheets("Member_list")

With ws
Me.lstMembers.List = Application.Transpose(.Range(.Range("A2"), .Range("A2").End(xlDown)).Value)
End With

--------------------


PROBLEMS


- The list box populates with every row in the worksheet. I want the
list box to populate with only the data on the sheet so I guess I
need to do a check on available data first any idears ???

- The listbox is only using the first colum A2 but since I dont
really understand the code I not sure what to change. Could someone
explain and give possible solutions


Kind Regards


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

..range("a2").end(xldown)

is the equivalent of selecting A2, then hitting the End key and then the down
arrow.

If you only have data in A2 (or nothing in A2 all the way down to A65536), then
that .end(xldown) goes all the way to the bottom of the worksheet.

So you either have to make sure A2 and A3 (at a minimum) are populated or add
some checks to your code. I also like to start at the bottom of the column
(A65536) and work my way up the column. (Like going to A65536, hitting End,
then up arrow--but this can suffer the same problem if there's nothing in A2.)

I guess the next question is what should happen if you don't have data in that
range.

This may give you some ideas:

Option Explicit
Private Sub Worksheet_Activate()

Dim ws As Worksheet
Dim ListBoxArray As Variant

Set ws = Worksheets("Member_list")

With ws
If IsEmpty(.Range("a2")) Then
'do nothing
ElseIf IsEmpty(.Range("a3")) Then
ListBoxArray = Array(.Range("a2").Value)
Else
ListBoxArray = .Range(.Range("A2"), .Range("A2").End(xlDown)).Value
End If
End With

If IsArray(ListBoxArray) = False Then
MsgBox "what happens here?"
Me.lstMembers.Clear
Else
Me.lstMembers.List = ListBoxArray
End If

End Sub




bach wrote:

Hi,

After some searching on the forum I found a article which was what I
was looking for.

WHAT AM I DOING

I have a sheet with data on it, I want to populate a list box with data
displayed on this sheet.

CODE CURRENTLY USING

Code:
--------------------

Dim ws As Worksheet
Set ws = Worksheets("Member_list")

With ws
Me.lstMembers.List = Application.Transpose(.Range(.Range("A2"), .Range("A2").End(xlDown)).Value)
End With

--------------------


PROBLEMS


- The list box populates with every row in the worksheet. I want the
list box to populate with only the data on the sheet so I guess I
need to do a check on available data first any idears ???

- The listbox is only using the first colum A2 but since I dont
really understand the code I not sure what to change. Could someone
explain and give possible solutions


Kind Regards

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

Hi,

Thanks for your help, it was very usefull although I am still a little
lost over some of the code.

DUM

The line of code which sets the array

.range("A2"), .Range("A2")

What does this do why is the range in twice.

Also would it be possible to implement more than one colum. I would
like to implement a mixture of colums for the list box but they are not
next to each other.

Basically I would like there userid - A, surname - E, forename - F etc


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

I'm guessing you're asking about this line:

ListBoxArray = .Range(.Range("A2"), .Range("A2").End(xlDown)).Value

If you select A2, then hit the End key, followed by ctrl-shift-down arrow,
you'll be selecting A2 until your data has a gap in it (no gaps in the data
means that A2 through the last used cell of column A will be selected).

This is the equivalent in code.

And you can get more columns using something like:

Option Explicit
Private Sub Worksheet_Activate()

Dim ws As Worksheet
Dim ListBoxRng As Range
Dim myCell As Range

Set ws = Worksheets("Member_list")

Set ListBoxRng = Nothing

'don't forget to change this!
Me.lstMembers.ColumnCount = 3

With ws
If IsEmpty(.Range("a2")) Then
'do nothing
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
MsgBox "what happens here?"
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
'don't forget to change this!
.ColumnCount = 3
.ListFillRange = ""
For Each myCell In ListBoxRng.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
Next myCell
End With
End If

End Sub

I used 3 columns: A, C, D
(.offset(0,2) is two to the right)
(.offset(0,3) is three to the right)

bach wrote:

Hi,

Thanks for your help, it was very usefull although I am still a little
lost over some of the code.

DUM

The line of code which sets the array

range("A2"), .Range("A2")

What does this do why is the range in twice.

Also would it be possible to implement more than one colum. I would
like to implement a mixture of colums for the list box but they are not
next to each other.

Basically I would like there userid - A, surname - E, forename - F etc

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

Hi,

Thanks for your help, I have got the following with errors lol


Code:
--------------------
Dim ws As Worksheet
Dim ListBoxArray As Variant

Set ws = Worksheets("Member_list")

With ws
If IsEmpty(.Range("A2")) Then
ElseIf IsEmpty(.Range("A3")) Then
ListBoxArray = Array(.Range("A2").Value)
Else
ListBoxArray = .Range(.Range("A2"), .Range("A2").End(xlDown)).Value
End If

End With


If IsArray(ListBoxArray) = False Then
Me.lblTotalNo.Caption = "There are no members currently in the database."
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
.ColumnCount = 3
.ListFillRange = ""
For Each myCell In ListBoxArray.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
Next myCell
End With
End If
--------------------


doesnt like .listfillrange and mycell any ideas.


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

The code changed. Try using the newer version.



bach wrote:

Hi,

Thanks for your help, I have got the following with errors lol

Code:
--------------------
Dim ws As Worksheet
Dim ListBoxArray As Variant

Set ws = Worksheets("Member_list")

With ws
If IsEmpty(.Range("A2")) Then
ElseIf IsEmpty(.Range("A3")) Then
ListBoxArray = Array(.Range("A2").Value)
Else
ListBoxArray = .Range(.Range("A2"), .Range("A2").End(xlDown)).Value
End If

End With


If IsArray(ListBoxArray) = False Then
Me.lblTotalNo.Caption = "There are no members currently in the database."
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
.ColumnCount = 3
.ListFillRange = ""
For Each myCell In ListBoxArray.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
Next myCell
End With
End If
--------------------


doesnt like .listfillrange and mycell any ideas.

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

Dave Peterson,

Thanks for you help with this, I have copied the code and it does not
like *.listfillrange*. I have commented this bit of the code out and
it seems to populate the listbox.

I will have to play with the code to ensure all the colums they want
are included inside the list box.

If I have any issues will post back.

P.S
-Would you have an idea why it does not like *.listfillrange* and is it
required.-


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

Maybe if you swap the order...

With Me.lstMembers
.ListFillRange = ""
.Clear

But I was just trying to make sure you didn't assign a range address to the
listbox. If you didn't do that, then this line of code isn't really necessary.

bach wrote:

Dave Peterson,

Thanks for you help with this, I have copied the code and it does not
like *.listfillrange*. I have commented this bit of the code out and
it seems to populate the listbox.

I will have to play with the code to ensure all the colums they want
are included inside the list box.

If I have any issues will post back.

P.S
-Would you have an idea why it does not like *.listfillrange* and is it
required.-

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

Hi,


Thanks for your help the generattion of data inside a textbox seems
fine.

Now I need to write some code to handle a double click event and bring
up another form with the details displayed any tips??

Bach


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

Use the lstMembers_DblClick event???

bach wrote:

Hi,

Thanks for your help the generattion of data inside a textbox seems
fine.

Now I need to write some code to handle a double click event and bring
up another form with the details displayed any tips??

Bach

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

That was the easy bit lol.

I was thinking along the lines of storing the listbox index of the item
selected. Using this variable to select the row in the spreadsheet and
display the data in the appropriate form elements does this sound
right.

Bach.


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

Yeah, I wondered what you were fishing for...

Seems ok to me.

But if the listbox only supports a single selection, you could use .value, too.
But the index seems nicer if you're populating the original listbox using
..rowsource (well, and you want to use that worksheet info for the next
userform).

bach wrote:

That was the easy bit lol.

I was thinking along the lines of storing the listbox index of the item
selected. Using this variable to select the row in the spreadsheet and
display the data in the appropriate form elements does this sound
right.

Bach.

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

Hi,

This is harder than i thought it would be, I have the listbox index
stored with +2 so that it matches the first bit of data on the sheet
(row2).

This is stored in a variable listSelect, how do i use this to select a
row and display the results

I am thinking that i would need to use this

ws.Cells(irow, 2).Value = UCase(Me.cboYourDeacon.Value)
but in reverse, but this used the irow which was generated to find the
last row.

I need irow to equal the row which i have attempted with

irow = ws.Cells(Rows(lstSelect & ":" & lstSelect))

which didn't work any idears.

Bach


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

I'm kind of confused about how you populated the listbox.

I used a range on sheet1 and this seemed to work ok.

Option Explicit
Dim myInputRng As Range
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim myRow As Long
With Me.ListBox1
If .ListIndex < 0 Then
Beep 'nothing to do
Else
myRow = myInputRng.Cells(1).Offset(.ListIndex).Row
MsgBox myRow & vbLf & .Value & vbLf & _
myInputRng.Cells(1).Offset(.ListIndex).Value
End If
End With
End Sub
Private Sub UserForm_Initialize()

With Worksheets("sheet1")
Set myInputRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ListBox1
.List = myInputRng.Value
End With
End Sub



bach wrote:

Hi,

This is harder than i thought it would be, I have the listbox index
stored with +2 so that it matches the first bit of data on the sheet
(row2).

This is stored in a variable listSelect, how do i use this to select a
row and display the results

I am thinking that i would need to use this

ws.Cells(irow, 2).Value = UCase(Me.cboYourDeacon.Value)
but in reverse, but this used the irow which was generated to find the
last row.

I need irow to equal the row which i have attempted with

irow = ws.Cells(Rows(lstSelect & ":" & lstSelect))

which didn't work any idears.

Bach

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

Thanks for your reply

Using the above posts I have populated the listbox with the following
code.



Code:
--------------------
Private Sub AllMembers()
'Variables for worksheet, and range
Dim ws As Worksheet
Dim ListBoxRng As Range
Dim myCell As Range

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

'Clears the listboxrng variable
Set ListBoxRng = Nothing

'Sets the columns to be used in the list box
Me.lstMembers.ColumnCount = 4

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


I had to take out .listfillrange it doesn't like it, what I am now
trying to achieve is on the double click of an item on this list
userform, a members userform is loaded with all the details of the
selected member from the list userform.


Code:
--------------------
Private Sub lstMembers_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim lstSelect As Integer
Dim ws As Worksheet
Dim irow As Integer

Application.ScreenUpdating = False
lstSelect = Me.lstMembers.ListIndex + 2

Select Case ListType
Case 1 'Members
Set ws = Worksheets("Member_list")
irow = ws.Cells(Rows(lstSelect & ":" & lstSelect))
MemberType = 3
Load frmMember
frmMember.txtMemID.Value = irow
Case 2 'Deacons
ws = Worksheets("Deacon_list")
End Select

MsgBox lstSelect

End Sub
--------------------


Regards


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

I'm confused about what's happening in he

Private Sub lstMembers_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim lstSelect As Long
Dim ws As Worksheet
Dim iRow As Long
Dim membertype As Long

Application.ScreenUpdating = False
lstSelect = Me.lstMembers.ListIndex + 2

Select Case ListType
Case 1 'Members
Set ws = Worksheets("Member_list")
iRow = ws.Cells(Rows(lstSelect & ":" & lstSelect))
membertype = 3
Load frmMember
frmMember.txtMemID.Value = iRow
Case 2 'Deacons
ws = Worksheets("Deacon_list")
End Select

MsgBox lstSelect

End Sub

I don't know what ListType is.

It kind of looks like you want this:
iRow = lstSelect
instead of
iRow = ws.Cells(Rows(lstSelect & ":" & lstSelect))



bach wrote:

Thanks for your reply

Using the above posts I have populated the listbox with the following
code.

Code:
--------------------
Private Sub AllMembers()
'Variables for worksheet, and range
Dim ws As Worksheet
Dim ListBoxRng As Range
Dim myCell As Range

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

'Clears the listboxrng variable
Set ListBoxRng = Nothing

'Sets the columns to be used in the list box
Me.lstMembers.ColumnCount = 4

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


I had to take out .listfillrange it doesn't like it, what I am now
trying to achieve is on the double click of an item on this list
userform, a members userform is loaded with all the details of the
selected member from the list userform.

Code:
--------------------
Private Sub lstMembers_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim lstSelect As Integer
Dim ws As Worksheet
Dim irow As Integer

Application.ScreenUpdating = False
lstSelect = Me.lstMembers.ListIndex + 2

Select Case ListType
Case 1 'Members
Set ws = Worksheets("Member_list")
irow = ws.Cells(Rows(lstSelect & ":" & lstSelect))
MemberType = 3
Load frmMember
frmMember.txtMemID.Value = irow
Case 2 'Deacons
ws = Worksheets("Deacon_list")
End Select

MsgBox lstSelect

End Sub
--------------------

Regards

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

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

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

Dave,

Thanks for your reply, I have used the code and i get a type
mismatch.

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


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

I have no idea what ListType was, so I declared it as long (an integer).

Since I didn't know what it was, I just took the value from that cell and tried
to make sure it was a nice number.

You never shared what ListType was and how it was derived--so I just guessed.

bach wrote:

Dave,

Thanks for your reply, I have used the code and i get a type
mismatch.

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

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

Dave,

It was my attempt, to create the form. As I am not sure what I need to
do for it I have made up some code playing around, so the variable might
not be needed.

I need the double click event to display all the data for the select
user in the frmmember userform. There are a number of fields, which
need to be populated.

Bach


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

You can pick out the data from the line you double clicked by using lines like:

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



bach wrote:

Dave,

It was my attempt, to create the form. As I am not sure what I need to
do for it I have made up some code playing around, so the variable might
not be needed.

I need the double click event to display all the data for the select
user in the frmmember userform. There are a number of fields, which
need to be populated.

Bach

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

Dave,

Could you tell me what that line of code is doing, what is the offset
for.

What should the listtype variable be set to, it is currently set to
string for data but I get an error message of type mismatch.

Bach

I have played with the number is the offset setting the column number?


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

You have a variable named ListType in your original code--but you didn't say
where it came from.

This was your code:

Select Case ListType
Case 1 'Members
Set ws = Worksheets("Member_list")
irow = ws.Cells(Rows(lstSelect & ":" & lstSelect))
MemberType = 3
Load frmMember
frmMember.txtMemID.Value = irow
Case 2 'Deacons
ws = Worksheets("Deacon_list")
End Select

I guessed that when you double clicked on an entry that you wanted something
from the same row. And since you had "case 1" and "case 2", I figured that
listtype had to be a number.

Since I had nothing to go by, I guessed that maybe it was 8 columns to the right
of that double clicked value.

ListType = CLng(mySelectedCell.Offset(0, 8).Value)

These were just guesses and would only be useful as a sample--I have no idea how
your data looks.

bach wrote:

Dave,

Could you tell me what that line of code is doing, what is the offset
for.

What should the listtype variable be set to, it is currently set to
string for data but I get an error message of type mismatch.

Bach

I have played with the number is the offset setting the column number?

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

Dave,

I have taken some pictures, hopefully this will help you understand.

'Member Details form' (http://www.starwithin.co.uk/memberdetails.gif)
'Sheet data' (http://www.starwithin.co.uk/spreadsheet.gif)
'Member List' (http://www.starwithin.co.uk/memberlist.gif)

The member details userform is the first image this is where all the
details for a user is enetered. This is then added to the spreadsheet
"MemberList" which can be seen in the second image. Finally the last
image is the member list which shows all users on the system.

The double click event needs to open the member details form and
populate all the fields in it with the data stored for that record in
the spread sheet image 2.

Does this help.
??

Bach


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

I'm sorry. I don't open attachments or go off to other sites to look at
pictures.

Maybe someone else will jump in.

bach wrote:

Dave,

I have taken some pictures, hopefully this will help you understand.

'Member Details form' (http://www.starwithin.co.uk/memberdetails.gif)
'Sheet data' (http://www.starwithin.co.uk/spreadsheet.gif)
'Member List' (http://www.starwithin.co.uk/memberlist.gif)

The member details userform is the first image this is where all the
details for a user is enetered. This is then added to the spreadsheet
"MemberList" which can be seen in the second image. Finally the last
image is the member list which shows all users on the system.

The double click event needs to open the member details form and
populate all the fields in it with the data stored for that record in
the spread sheet image 2.

Does this help.
??

Bach

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

Send to you directly ??


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

No.

It's usual for posters to explain the problem in plain text. And by posting to
the newsgroup, you'll get lots of readers and more responders.

But others have volunteered to take files directly--maybe someone will volunteer
here, too.

bach wrote:

Send to you directly ??

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

Dave,

What is the issue with the links ?? would it be better if i pasted the
link as text so you can see it is an image file ??

Bach

The member details userform has the following fields:

MemberID
Deacon
DateJoined
Title
Surname
Forename
Name/No
Address
Town
PostCode
TelephoneNo
MobileNo
Deacon
Reverent
DeaconId

These are then stored in the member_list spread sheet.

The list box will:

MemberID
forename
Surname
TelephoneNo

Double click procedure i want to load the Member Details UserForm and
load all the details back in the text boxes

This help ?


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

I figure that you're going to have to describe what you want anyway--pictures
don't always tell enough of the story.

And since this is a text only newsgroup, attachments aren't welcome--and
embedded images fall into that category, too.

If you find the "cell" that was double clicked--that's mySelectedCell in the
previous suggestions, then I'm not sure why you can't use .offset()'s to
retrieve the values.

ListType = CLng(mySelectedCell.Offset(0, 8).Value)

If listtype isn't numeric, then drop the clng() portion.

If you want the 2nd column of the row with that "selected cell", you can use:

msgbox myselectedcell.offset(0,1).value

I used msgbox, but you could populate the second userform using this kind of
technique.



bach wrote:

Dave,

What is the issue with the links ?? would it be better if i pasted the
link as text so you can see it is an image file ??

Bach

The member details userform has the following fields:

MemberID
Deacon
DateJoined
Title
Surname
Forename
Name/No
Address
Town
PostCode
TelephoneNo
MobileNo
Deacon
Reverent
DeaconId

These are then stored in the member_list spread sheet.

The list box will:

MemberID
forename
Surname
TelephoneNo

Double click procedure i want to load the Member Details UserForm and
load all the details back in the text boxes

This help ?

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