Execute Macro using Listbox on Userform

[goob90]

Hello!

I have a code in my workbook that exports the workbook as a CSV in a specific format. I have multiple sheets in my workbook, one called "Data" and twelve others named for each month. I would like to be able to run the code by having the user select which sheet (or month) they would like to export and only have the chosen sheet exported.

Can anyone assist me with this? And maybe how to change the export file name as the month of the sheet?

Sub CreateCSV()
    Dim a, b, w, i As Long, ii As Long, n As Long
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * 2)
    For i = 2 To UBound(a, 1)
        ReDim w(1 To 26)
        w(1) = a(i, 2): w(3) = a(i, 3): w(4) = "active"
        w(5) = "-": w(22) = a(i, 4)
        For ii = 8 To 9: w(ii) = "yes": Next
        For ii = 10 To 14: w(ii) = "no": Next
        For ii = 24 To 26: w(ii) = "-": Next
        n = n + 1: b(n) = Join(w, ",")
        If (w(3) Like "MP*") + (w(3) Like "DP*") Then
            w(3) = Left$(w(3), 2) & "B"
            n = n + 1: b(n) = Join(w, ",")
        End If
    Next
    ReDim Preserve b(1 To n)
    Open ThisWorkbook.Path & "\example.csv" For Output As #1
        Print #1, Join(b, vbCrLf);
    Close #1
End Sub

Thanks!

 

[Domenic]

The the following . . .

Sub CreateCSV()

    'if user did not select a month, exit sub
    With Me.ListBox1
        If .ListIndex = -1 Then
            MsgBox "Please select a month for which to" & vbCrLf & "export the data, and try again!", vbExclamation
            Exit Sub
        End If
    End With
    
    Dim exportSheetName As String
    exportSheetName = Me.ListBox1.Value

    Dim a, b, w, i As Long, ii As Long, n As Long
    a = Sheets(exportSheetName).Cells(1).CurrentRegion.Value
    'etc
    '
    '
End Sub

Note that it assumes that the code resides within the userform code module. If this is not the case, simply replace Me with the name of your userform. For example, if your userform is called UserForm1, replace . . .

With Me.ListBox1

with

With UserForm1.ListBox1

Hope this helps!

 

[goob90]

The the following . . .

Sub CreateCSV()

    'if user did not select a month, exit sub
    With Me.ListBox1
        If .ListIndex = -1 Then
            MsgBox "Please select a month for which to" & vbCrLf & "export the data, and try again!", vbExclamation
            Exit Sub
        End If
    End With
   
    Dim exportSheetName As String
    exportSheetName = Me.ListBox1.Value

    Dim a, b, w, i As Long, ii As Long, n As Long
    a = Sheets(exportSheetName).Cells(1).CurrentRegion.Value
    'etc
    '
    '
End Sub

Note that it assumes that the code resides within the userform code module. If this is not the case, simply replace Me with the name of your userform. For example, if your userform is called UserForm1, replace . . .

With Me.ListBox1

with

With UserForm1.ListBox1

Hope this helps!

This is awesome! Thank you so much! Do you know how I can set the export file name to be the sheet name that is selected from the listbox?

 

[Domenic]

In that case, try the following instead . . .

Open ThisWorkbook.Path & "\" & exportSheetName  & ".csv" For Output As #1

 

[goob90]

In that case, try the following instead . . .

Open ThisWorkbook.Path & "\" & exportSheetName  & ".csv" For Output As #1

That worked perfectly. I cannot thank you enough!

 

[Domenic]

That's great, glad I could help.

Cheers!