how to get combobox multiselect results to specific worksheets

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,737
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,322
Office Version
2013
Platform
Windows
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

Rich (BB code):
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
 
Last edited:

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,737
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:

Trip Name DateTrip Sheet Name
Prawn Tour with James, Fri 01 Nov 2019PrawnTour 01Nov19
Carrara Markets, Sat 02 Nov 2019CarararaMkts 02Nov19
The Plant Shack, Fri 08 Nov 2019PlantShack 08Nov19
Harbour Town, Sat 09 Nov 2019HarbourTown 09Nov19
Esk Bush Bashers, Tue 12 Nov 2019EskBushBash 12Nov19
The Plant Shack, Fri 15 Nov 2019PlantShack2 15Nov19
Carrara Markets, Sat 16 Nov 2019CarararaMkts2 16Nov19
Prawn Tour with James, Tue 19 Nov 2019PrawnTour2 19Nov19
General Meeting, Thu 21 Nov 2019GenMeet 21Nov19
The Plant Shack, Fri 22 Nov 2019PlantShack3 22Nov19
<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> </tbody>


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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,875
I think that this will return the worksheet when given the Trip Name.

Code:
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
 
Last edited:

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,737
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?
 

Forum statistics

Threads
1,084,776
Messages
5,379,812
Members
401,629
Latest member
LEMANOIS

Some videos you may like

This Week's Hot Topics

Top