Saving pdf with a few textboxes and folders.

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81
Hi there.

I currently got a code, for saving pdf files, that works good, but i want to improve it a bit, but im struggling real hard.

Currently, i got 1 textbox, 1 combobox and 2 checkboxes, which i fill in, then press the button, and it opens a dialog, then i find the folder i want, and it saves the pdf to that folder.
Code:

VBA Code:
Sub SavePDF5()
Dim Path, FileName1 As String

Dim flder As FileDialog
Dim foldername As String
Dim GetFolder As String

Set flder = Application.FileDialog(msoFileDialogFolderPicker)

'Profile
ActiveWorkbook.ActiveSheet.Unprotect ("")

ActiveWorkbook.ActiveSheet.Range("AN1").Value = (ComboBox1.Value)

If ComboBox1.Text = "" Then
        MsgBox "Please choose the operator name!"
        Exit Sub
   Else
'      Code if not empty
   End If

   
    With flder
        .Title = "Select the folder containing data"
        .AllowMultiSelect = True
        If .Show = 0 Then GoTo NextCode
        foldername = .SelectedItems(1)
       
        Path = foldername & "\"
               
                If CheckBox2.Value = True And CheckBox1.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Dummy - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
               
                If CheckBox2.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Dummy - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                If CheckBox1.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                    FileName1 = TextBox1.Text & " - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                End If
                End If
                End If
       
    End With

Set flder = Nothing
MsgBox "File Saved to " & FileName1, vbInformation, "Saved File"
Unload Me
NextCode:
Exit Sub
ActiveWorkbook.ActiveSheet.Protect ("")
End Sub


But i am trying to improve it.
Now i got 2 textboxes and 2 comboboxes (Textbox 1 and combobox1 does the same thing as in the old code)

What i want now is this:

I select the data from combobox 1 (same as before in the old code),
Then i type the data needed in textbox1 (same as before in the old code)
In combobox2 i choose some data that i have added (this one is important)
In Textbox2 i type some required numbers (this one is important)

So, the data from combobox2, is going to be linked to spesific folders on the computer.
Textbox2 is going to make a subfolder in combobox2 folder, or if it allready exsists, just create the pdf file within it.

So the dilemma here, is making the textbox 2 either creating a subfolder, then save the pdf in it, or check if it exists, and if so, open it and save the pdf in it.

I have tried mixing those codes, since combobox1 and textbox1 works good, but i am stuck, and it feels like the new code is a mess.

Code:

Code:
Sub PDFSMAIN()

    Dim sPath As String 'path of folder for the text file to be held
    Dim sPath2 As String 'path of folder for the text file to be held
    Dim sPath3 As String 'path of folder for the text file to be held
    Dim sPath4 As String 'path of folder for the text file to be held
    Dim sPath5 As String 'path of folder for the text file to be held
    Dim sPath6 As String 'path of folder for the text file to be held
    Dim sPath7 As String 'path of folder for the text file to be held
    Dim sPath8 As String 'path of folder for the text file to be held
    Dim sPath9 As String 'path of folder for the text file to be held
    Dim sPath10 As String 'path of folder for the text file to be held
    Dim sPath11 As String 'path of folder for the text file to be held
    Dim sPath12 As String 'path of folder for the text file to be held
    Dim sPath13 As String 'path of folder for the text file to be held
    Dim sPath14 As String 'path of folder for the text file to be held
    Dim sName As String 'name of file
    Dim sName2 As String 'name of file
    Dim sName3 As String 'name of file
    Dim sName4 As String 'name of file
    Dim sName5 As String 'name of file
    Dim sName6 As String 'name of file
    Dim sName7 As String 'name of file
    Dim sName8 As String 'name of file
    Dim sName9 As String 'name of file
    Dim sName10 As String 'name of file
    Dim sName11 As String 'name of file
    Dim sName12 As String 'name of file
    Dim sName13 As String 'name of file
    Dim sName14 As String 'name of file
   
    On Error GoTo errHandle
   
    'set the path
    sPath =  "O:\1\"
    sPath2 = "O:\2\"
    sPath3 = "O:\3\"
    sPath4 = "O:\4\"
    sPath5 = "O:\5\"
    sPath6 = "O:\6\"
    sPath7 = "O:\7\"
    sPath8 = "O:\8\"
    sPath9 = "O:\9\"
    sPath10 = "O:\10\"
    sPath11 = "O:\11\"
    sPath12 = "O:\12\"
    sPath13 = "O:\13\"
    sPath14 = "O:\14\"
       

   
 
   
   
    If UserForm6.CheckBox2.Value = True And UserForm6.CheckBox1.Value = True Then
                    sName = UserForm6.TextBox1.Text & " - " & "Dummy - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                Else
                If UserForm6.CheckBox2.Value = True Then
                    sName = UserForm6.TextBox1.Text & " - " & "Dummy - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                Else
                If UserForm6.CheckBox1.Value = True Then
                    sName = UserForm6.TextBox1.Text & " - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                Else
                    sName = UserForm6.TextBox1.Text & " - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                End If
                End If
                End If
   
   
 
   
    Open sPath & sName For Output As #1
   
     
       
    Close #1
   
    MsgBox "Success!", vbInformation, "Textbox Text Saved"
    'Unload Me

Exit Sub

errHandle:
    MsgBox Err.Description, vbCritical, "Failed"
 
End Sub


Can anyone help me with this? Please just ask if i am beeing unclear on something :)

Thanks in advance.

EDIT: The new code is imcomplete, just tried to see if it worked before i did more code on it, just if you wondered :)
 
Last edited:
This worked perfectly! Exactly as i wanted it, with no problems what so ever.
Thank you very much Sijpie for the hard work and great help!
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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