how to get combobox multiselect results to specific worksheets

[ajm]

hi all. I have a userform in which people can select trips/events from a multi select listbox. The list box's row source is a named range containing the name of all trips. each trip has its own tab. The tab names are an abbreviated version of the trip name. I need to have the details of the user placed onto the respective sheets matching the users selection. how do I match up the trip selected to the abbreviated sheet name and transfer the user details accordingly?

 

[dmt32]

hi all. I have a userform in which people can select trips/events from a multi select listbox. The list box's row source is a named range containing the name of all trips. each trip has its own tab. The tab names are an abbreviated version of the trip name. I need to have the details of the user placed onto the respective sheets matching the users selection. how do I match up the trip selected to the abbreviated sheet name and transfer the user details accordingly?

Hi,
Just a guess but maybe

Private Sub OKButton_Click()
    Dim ws As Worksheet
    Dim i As Integer
    Dim LastRow As Long
    Dim lb As Object
    
    Set lb = Me.ListBox1
    
        For i = 0 To lb.ListCount - 1
            If lb.Selected(i) Then
                For Each ws In ThisWorkbook.Worksheets
                    If UCase(ws.Name) Like "*" & UCase(lb.List(i)) & "*" Then
'match found
'example use
'apply your users selection code here
                        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
                         With ws.Cells(LastRow, 1)
                            .Value = lb.List(i)
                            .Offset(0, 1).Value = Me.TextBox1.Text
                            .Offset(0, 2).Value = Me.TextBox2.Text
                         End With
                        Exit For
                    End If
                    
                Next ws
            End If
        Next i
End Sub

If solution does not produce desired result, then provide some examples of values in your listbox & the abbreviate sheet names - plenty here to help

Dave

 

[ajm]

thanks Dave. I should have thought to put the names and abbreviations in last night. it was meant to be a quick easy thing for a friend....


The list is:

[TABLE="width: 312"]
<colgroup><col width="261" style="width: 196pt; mso-width-source: userset; mso-width-alt: 9545;"> <col width="154" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5632;"> <tbody>[TR]
[TD="width: 261, bgcolor: transparent"]Trip Name Date[/TD]
[TD="width: 154, bgcolor: transparent"]Trip Sheet Name[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Prawn Tour with James, Fri 01 Nov 2019[/TD]
[TD="bgcolor: transparent"]PrawnTour 01Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Carrara Markets, Sat 02 Nov 2019[/TD]
[TD="bgcolor: transparent"]CarararaMkts 02Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]The Plant Shack, Fri 08 Nov 2019[/TD]
[TD="bgcolor: transparent"]PlantShack 08Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Harbour Town, Sat 09 Nov 2019[/TD]
[TD="bgcolor: transparent"]HarbourTown 09Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Esk Bush Bashers, Tue 12 Nov 2019[/TD]
[TD="bgcolor: transparent"]EskBushBash 12Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]The Plant Shack, Fri 15 Nov 2019[/TD]
[TD="bgcolor: transparent"]PlantShack2 15Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Carrara Markets, Sat 16 Nov 2019[/TD]
[TD="bgcolor: transparent"]CarararaMkts2 16Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Prawn Tour with James, Tue 19 Nov 2019[/TD]
[TD="bgcolor: transparent"]PrawnTour2 19Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]General Meeting, Thu 21 Nov 2019[/TD]
[TD="bgcolor: transparent"]GenMeet 21Nov19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]The Plant Shack, Fri 22 Nov 2019[/TD]
[TD="bgcolor: transparent"]PlantShack3 22Nov19[/TD]
[/TR]
</tbody>[/TABLE]


As the abbreviation isn't just a shortening of the trip name, i was searching for a method to essentially vlookup the sheetname. Would using a multi column listbox do this?

 

[mikerickson]

I think that this will return the worksheet when given the Trip Name.

Function SheetFromTripName(aTripName As String) As Worksheet
    Dim Prefix As String, Suffix As String
    Dim oneSheet As Worksheet

    aTripName = LCase(aTripName)
    
    If aTripName Like "the *" Then
        aTripName = Mid(aTripName, 5)
    End If
    
    Prefix = Left(aTripName, 3)
    
    Suffix = Replace(Mid(Split(aTripName & ",", ",")(1), 5), " ", vbNullString)
    
    For Each oneSheet In ThisWorkbook.Worksheets
        If LCase(oneSheet.Name) Like Prefix & "*" & Suffix Then
            Set SheetFromTripName = oneSheet
        End If
    Next oneSheet
End Function

 

[ajm]

thank you both. i now have values transferred to my spreadsheets as desired. This is done by clicking a command button. How do i close the form and reset once all values at transferred to the sheets.

my code:

[Private Sub cmdAdd_Click()
Dim i As Long
'Dim sPrompt As String
'Dim sTitle As String
Dim ws As Worksheet
Dim LastRow As Long
Dim wsFullNameRg As Range, wsFirstNameRg As Range, wsSurnameRg As Range

Set wsFullNameRg = Worksheets("Members").Range("MFullName")
Set wsFirstNameRg = Worksheets("Members").Range("MFirstNm")
Set wsSurnameRg = Worksheets("Members").Range("MSurname")
'sTitle = "You selected..."
With Me.lbxTripDate
For i = 0 To .ListCount - 1
If .Selected(i) Then

For Each ws In ThisWorkbook.Worksheets
If UCase(ws.Name) Like "*" & UCase(lbxTripDate.Column(2, i)) & "*" Then
'ws.Range("C2").value = .Column(2, i)

LastRow = ws.Cells(ws.Rows.Count, "e").End(xlUp).Row + 1
With ws.Cells(LastRow, 5)
.Value = cboMembName.Text
.Offset(0, 1).Value = Me.txtPhone.Text
.Offset(0, 2).Value = Me.txtemail.Text
.Offset(0, 3).Value = Me.txtCommsReqs.Text
.Offset(0, 4).Value = Me.txtLogDate.Text
.Offset(0, 5).Value = Me.txtTmMember.Text
.Offset(0, -1).Value = Application.Index(wsFullNameRg, Application.Match(cboMembName.Text, wsFirstNameRg, 0))
.Offset(0, -2).Value = Application.Index(wsFullNameRg, Application.Match(cboMembName.Text, wsSurnameRg, 0))
End With
Exit For


End If
'LastRow = 0
Next ws
End If
Next i

End With


End Sub[/code]

also, the last two values don't are not correctly done. i need the first name and surname which are held in a named ranges in the workbook. how should i do this?