Combo box List to save workbook

[mattless1]

HI Guys,

New to vba, but trying to learn

i need a little help with this?

I have a combo box which i have made with visual basic in excel 2013.
i have a button when it's clicked it brings up the combo list box userform & it has five names in the list.
What i would like to do is. When i click on the name in the combo box userform it make the folder if it doesn't exist and then saves the file in the selected folder once it's made. i do have code which creates the folder in c:/ & saves the files inside the folder,
but i can't work out how to get the name in the list to save when i click on the name names.

Can anyone help me here?

 

[andrewkard]

Hello, do you mean how to get the selected name from list of combobox? Try:

[/COLOR]Sub test2()
sName = UserForm1.ComboBox1.SelText
MsgBox (sName)
End Sub
[COLOR=#333333]

 

[JLGWhiz]

HI Guys,

New to vba, but trying to learn

i need a little help with this?

I have a combo box which i have made with visual basic in excel 2013.
i have a button when it's clicked it brings up the combo list box userform & it has five names in the list.
What i would like to do is. When i click on the name in the combo box userform it make the folder if it doesn't exist and then saves the file in the selected folder once it's made. i do have code which creates the folder in c:/ & saves the files inside the folder,
but i can't work out how to get the name in the list to save when i click on the name names.

Can anyone help me here?

It sounds like you would need:

Private Sub ComboBox1_Click() 'Substitute your ComboBox name for ComboBox1 if it is different.
nm = Me.ComboBox1.Value 'Put the selected name in the nm variable and use nm wherever it is needed in subsequent code.
'put your code to create the folder here
End Sub

This, when merged and modified, would be installed in the UserForm code module by right clicking the UserForm in design mode, then click 'View Code' in the pop up menu. If you already have a ComboBox1_Click event code, You would need to merge this with that code using 'If...Then' statements to control which actions are needed.
Good Luck,
Regards, JLG

 

[mattless1]

Sorry JLGWhiz i can't manage to get this right maybe you could insert this in correct place for me it for the first name in the combox1 userform
then i will add the code below for each name in the combo userform.

Sub DateFolderSave()

    Dim saveAsFileName As String
    Dim folders As Variant, i As Integer, path As String
    
    saveAsFileName = "C:\" & Year(Date) & "\" & MonthName(Month(Date)) & "\Belfast\" & Format(Date, "mm.dd.yy") & ".xlsm"
    
    folders = Split(saveAsFileName, "\")
    path = folders(0)
    For i = 1 To UBound(folders) - 1
        path = path & "\" & folders(i)
        If Dir(path, vbDirectory) = "" Then MkDir path
    Next
    
    Application.DisplayAlerts = False
    
    ' Save File
    ActiveWorkbook.SaveAs FileName:=saveAsFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
    Application.DisplayAlerts = True
    
    ' Popup Message
    MsgBox "File Saved As:" & vbNewLine & saveAsFileName

https://plus.google.com/107309265952219048011/posts/i1ZkttSs16R


Thanks you if can help me?

 

[andrewkard]

Try to change:

saveAsFileName = "C:\" & Year(Date) & "\" & MonthName(Month(Date)) & "\Belfast\" & Format(Date, "mm.dd.yy") & ".xlsm"

on

saveAsFileName = "C:\" & Year(Date) & "\" & MonthName(Month(Date)) & "\"&[COLOR=#333333]UserForm1.ComboBox1.SelText[/COLOR]&"\" & Format(Date, "mm.dd.yy") & ".xlsm"

 

[mattless1]

Try to change:

saveAsFileName = "C:\" & Year(Date) & "\" & MonthName(Month(Date)) & "\Belfast\" & Format(Date, "mm.dd.yy") & ".xlsm"

on

saveAsFileName = "C:\" & Year(Date) & "\" & MonthName(Month(Date)) & "\"&[COLOR=#333333]UserForm1.ComboBox1.SelText [/COLOR]&"\" & Format(Date, "mm.dd.yy") & ".xlsm"

Thanks for the code, but it gives me a error on the seltext area it's Highlighted yellow??
i moved the "&" away as it was next to selectext but still the same error?
i have added a picture below of the vba screen code so you can see what i have

Hope you can see the error

https://plus.google.com/107309265952219048011/posts/7CX9KiD6Jdk

 

[andrewkard]

Try:

Sub DateFolderSave()


    Dim saveAsFileName As String
    Dim folders As Variant, i As Integer, path As String
    
   Debug.Print UserForm1.ListBox1.Value
    
    saveAsFileName = "C:\" & Year(Date) & "\" & MonthName(Month(Date)) & "\" & UserForm1.ListBox1.Value & "\" & Format(Date, "mm.dd.yy") & ".xlsm"
    
    folders = Split(saveAsFileName, "\")
    path = folders(0)
    For i = 1 To UBound(folders) - 1
        path = path & "\" & folders(i)
        If Dir(path, vbDirectory) = "" Then MkDir path
    Next
    
    Application.DisplayAlerts = False
    
    ' Save File
    ActiveWorkbook.SaveAs Filename:=saveAsFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
    Application.DisplayAlerts = True
    
    ' Popup Message
    MsgBox "File Saved As:" & vbNewLine & saveAsFileName
End Sub

 

[mattless1]

Thank you soo much that works well.
I can work with it now so only saves the active sheet not whole workbook.

Once again thanks so much.