PDA

View Full Version : Solved: Listbox - Double Click to add data into userform field



phendrena
02-04-2009, 07:17 AM
Hi There,

If the user double clicks an item in the listbox list i want this to be placed into a seperate field on a nother userform. How can I go about doing this?

The forms used are :-

frmDSTCallLog
frmStaffList

frmStaffList is called from within frmDSTCallLog when the user clicks the 'StaffList' command button, although ideally it would also launch when the user double clicks into the field txtOne.

Thanks,

lucas
02-04-2009, 07:35 AM
So you want to open staff list and double click a name and have it go into what field? Original processed by?

lucas
02-04-2009, 08:03 AM
Well, since you didn't provide a password and your file is read only, I had to recreate the entire scenario the best I could......

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmDSTCallLog.txtOne.Value = Me.ListBox1.ListIndex + 1
Unload Me
End Sub

phendrena
02-04-2009, 08:06 AM
Yeah, that would be an accurate assumption.
I'm looking at changing the way the email section of the main form (frmDSTCallLog) works.
Instead of the user choosing a subject line, The user would choose the "original processed by" and this will then fill in the "email subject". However, i've no idea how to make VBA do a vlookup.

So,

User enters orginal processed by,
VBA does a vlookup for the name on 'StaffListLookup' (two columns, 'Name | Location'),
Location is then placed into a field txtLocation.

Your thoughts?

Thanks,

phendrena
02-04-2009, 08:07 AM
Well, since you didn't provide a password and your file is read only, I had to recreate the entire scenario the best I could......

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmDSTCallLog.txtOne.Value = Me.ListBox1.ListIndex + 1
Unload Me
End Sub


I thought i'd stripped the password! Sorry!
Password is 0range (with a zero).

phendrena
02-04-2009, 08:49 AM
The code does work, however instead of entering the value it enters a number. The listbox(s) is populated by the following :
cboTempList.List = WorksheetFunction.Transpose(ws.Range("HalifaxTemps"))
cboHalifaxCM.List = WorksheetFunction.Transpose(ws.Range("HalifaxCM"))
cboSundCM.List = WorksheetFunction.Transpose(ws.Range("SunderlandCM"))
How can I get the text value and not a numerical entry?

Cheers,

lucas
02-04-2009, 09:19 AM
Well Dave, the password you provide does not allow me access to the file and the one I created used numbers to populate the listbox which has nothing to do with your workbook except that it demonstrates how to double click a listbox and have the selected item be placed in a different userform and textbox.......

If you add my code to your userform frmstafflist what happens?

You can either provide a password that works or remove the protection and re-upload the workbook......otherwise, I can't help much.

phendrena
02-04-2009, 09:34 AM
Steve, my apologies. it would appear I didn't remove the 'read-only' password which is p1neapple. Assuming that I also failed to remove the other passwords they are : k1wifruit (worksheet) and as previously noted 0range (code).

phendrena
02-04-2009, 09:38 AM
Steve, I added the code that you provided into my worksheet without looking at your attachment. So the comment I nade regarding it producing a number is correct from what I can see.

thanks for your help so far.

lucas
02-04-2009, 09:51 AM
Ok Dave, that helps a lot. Try it now and see if we are getting close. By the way, I strongly advise that you add Option explicit to the very top line of every module, userform, sheet, thisworkbook, etc.

By the way, you were right about the number after I was able to look at it....index was incorrect, I changed it and added this code to your frmstafflist

Private Sub lbxHalifax_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmDSTCallLog.txtOne.Value = Me.lbxHalifax.Value
Unload Me
End Sub

Private Sub lbxHalifaxTemps_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmDSTCallLog.txtOne.Value = Me.lbxHalifaxTemps.Value
Unload Me
End Sub

Private Sub lbxSunderland_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmDSTCallLog.txtOne.Value = Me.lbxSunderland.Value
Unload Me
End Sub

phendrena
02-05-2009, 02:01 AM
Steve,

Many thanks for the assistance wth this one the above code works nicely.

Thanks,