how to get combobox multiselect results to specific worksheets

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. 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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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:
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top