VBA change form control and print first page as .pdf

[JoaoGabriel]

How can i make a button or a macro who change the form control to print each city in its range and save the first page in red as .pdf with the name of the city selected by the form control?

Can anyone hep? Thank you very much in advance

 

[SQUIDD]

Hi

So i can see you have a red or pink range. A1 TO G59.

Laso, 7 buttom conrols? and a drop combo box, withthe city names in from the column N.

If the above is correct, then what do you require. When you select your combobox it populates the name of the 1st button?

 

[JoaoGabriel]

Hi

So i can see you have a red or pink range. A1 TO G59.

Laso, 7 buttom conrols? and a drop combo box, withthe city names in from the column N.

If the above is correct, then what do you require. When you select your combobox it populates the name of the 1st button?

red range is A1 to G68

I've already configured the form control to fill the info spaces when I change the city on it, what's inside the red area is just an example of what I want in the pdf

I'm trying to create a button, a macro, or a button with a macro that changes the form control below and saves the area in red (first page) as .pdf with the name of the city, that's is what i need

everything after the K column was used to configure the form control

 

[SQUIDD]

ok

So you are only really after a PDF file to be created and saved after the control changes?

The below should save your range to PDF, check the comments in the code to set it up properly.

So i guess if the below works you just need to trigger it?

You can just call the macro SAVE_RANGE_PDF in your existing change event?

Sub SAVE_RANGE_PDF()

MY_DIR = "c:\" 'ENTER YOUR DIRECTORY TO SAVE TO
MY_NAME = "DB.pdf" 'ENTER THE .PDF NAME

Range("A1:G68").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MY_DIR & MY_NAME, Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

 

[SQUIDD]

upon reading your 1st post, i am still a little unsure if i have understood you properly, although i am confident you wanted .pdf created from that range.

to you want to create a loop that cycles through your combox box control and save all of these .pdf's

?

 

[SQUIDD]

Ok, i have written this in any case.

assuming your form control is a combobox called "combobox1"

The below should loop through all the city names from combobox1(you may need to change) and save the .pdf in the directory(you need to change)
The .PDF's will be saved as the city name as requested.

Sub SAVE_PDFS()
Application.ScreenUpdating = False
    For A = 0 To Me.ComboBox1.ListCount - 1
        MY_DIR = "c:\" 'ENTER YOUR DIRECTORY TO SAVE TO
        MY_NAME = Me.ComboBox1.List(A)
        Range("A1:G68").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MY_DIR & MY_NAME, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
Application.ScreenUpdating = True
End Sub

 

[JoaoGabriel]

worked perfectlly

ok

So you are only really after a PDF file to be created and saved after the control changes?

The below should save your range to PDF, check the comments in the code to set it up properly.

So i guess if the below works you just need to trigger it?

You can just call the macro SAVE_RANGE_PDF in your existing change event?

Sub SAVE_RANGE_PDF()

MY_DIR = "c:\" 'ENTER YOUR DIRECTORY TO SAVE TO
MY_NAME = "DB.pdf" 'ENTER THE .PDF NAME

Range("A1:G68").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MY_DIR & MY_NAME, Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

it worked perfectly saving one by one thank you very much, you saved me

 

[SQUIDD]

Your welcome ?

 

[JoaoGabriel]

Ok, i have written this in any case.

assuming your form control is a combobox called "combobox1"

The below should loop through all the city names from combobox1(you may need to change) and save the .pdf in the directory(you need to change)
The .PDF's will be saved as the city name as requested.

Sub SAVE_PDFS()
Application.ScreenUpdating = False
    For A = 0 To Me.ComboBox1.ListCount - 1
        MY_DIR = "c:\" 'ENTER YOUR DIRECTORY TO SAVE TO
        MY_NAME = Me.ComboBox1.List(A)
        Range("A1:G68").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MY_DIR & MY_NAME, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
Application.ScreenUpdating = True
End Sub

Yes, that was exactly what I needed since the range of cities is very wide

However, my form control is a Drop-down, not activeX. This will work anyway?

I tried renaming the button to "combobox1" and it didn't work

Thanks again

 

[JoaoGabriel]

Ok, i have written this in any case.

assuming your form control is a combobox called "combobox1"

The below should loop through all the city names from combobox1(you may need to change) and save the .pdf in the directory(you need to change)
The .PDF's will be saved as the city name as requested.

Sub SAVE_PDFS()
Application.ScreenUpdating = False
    For A = 0 To Me.ComboBox1.ListCount - 1
        MY_DIR = "c:\" 'ENTER YOUR DIRECTORY TO SAVE TO
        MY_NAME = Me.ComboBox1.List(A)
        Range("A1:G68").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MY_DIR & MY_NAME, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
Application.ScreenUpdating = True
End Sub

I finally got it to work, but how can i change the combobox selected item before saves the .pdf? Is there any way to do this using vba?

Sub SAVE_PDFS()
Application.ScreenUpdating = False
    For A = 0 To Sheets("sheet1").ComboBox1.ListCount - 1
        MY_DIR = "c:\Desktop\"
        MY_NAME = Sheets("sheet1").ComboBox1.List(A)
        Range("A1:G68").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MY_DIR & MY_NAME, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
Application.ScreenUpdating = True
End Sub

Another question: if I put a directory that doesn't exist on the desktop will excel automatically create the folder?

Sorry for bothering you again.