UserForm Listbox issue

[bach]

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

 

[Dave Peterson]

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]

Dave,

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

What should the listtype variable be set to, it is currently set t
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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

Send to you directly ??

 

[Dave Peterson]

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]

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 ?

 

[Dave Peterson]

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.