Can you check my code please

[ipbr21054]

Hi,
I have an excel sheet of which i would like to copy a specific range, then save that range as a pdf file on the desktop.
When the file is saved it just needs to be called LR CODES

The range to copy is A1:K16

I have the code below but not entirely sure its correct,because i copied it from another page & removed some items etc.

Private Sub CommandButton1_Click()    Dim strFileName As String
    
    strFileName = "C:\Users\Ian\Desktop\ ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "SHEET " & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
        Exit Sub
    End If
    
    With ActiveSheet
        .PageSetup.PrintArea = "$A$1:$K$16"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "SHEET " & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
    End With
End Sub

 

[Logit]

.
Duh ! My old eyes didn't see that. Sorry ..

Get rid of this line :

[COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]

Have your command button connected to this macro instead :

[COLOR=#333333]Sub SveRngPDF()[/COLOR]

 

[ipbr21054]

Sorry but im really confused now as each bit of code im entering is a guess etc then when it dont work struggle to continue from there.

Can you just post the whole code then i know these bits of code are in the correct please.

Thanks

 

[Logit]

.
I've read this over several times .. hope there are no errors.

Option Explicit


Private Sub CommandButton2_Click()
    Range("A1:K16").PrintOut
End Sub


Function FileExists(FilePath As String) As Boolean
Dim TestStr As String


    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
    
End Function


Sub SveRngPDF()
Dim Filename As String
Filename = "C:\Users\Ian\Desktop\Test.pdf"


    If FileExists(Filename) Then
        MsgBox "Exists"
        Exit Sub
    Else
        MsgBox "Doesn't Exists"
    End If
       
    Sheets("DR SITE").Range("A1:K16").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Ian\Desktop\Test.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False


    If Not Application.Intersect(Target, Range("E6,F6,G6,H6,I6,J6,K6")) Is Nothing Then
        Target(1).Value = UCase(Target(1).Value)
    End If
    
    Application.EnableEvents = True


End Sub

 

[ipbr21054]

Hi,
I deleted all the code on my page.
I copied the whole code supplied in last message.
Save,close then open again.

I click my command button which does nothing.

Right click on command button and see the following.

Private Sub CommandButton1_Click()

End Sub

So i think ive entered it wrong.
So i delete it all again.
Right click command button so i now see this.

Private Sub CommandButton1_Click()

End Sub

Then i paste the code in last message between these two lines.
Save,close then open again.
Click command button to see this message Invalid inside procedure

 

[Logit]

.
Ok .. let's try a different route.

From my last post (#13) ... copy and paste all of that code, as is. Do not change anything yet.


Once the code is pasted, delete this line :

Sub SveRngPDF()

Replace that line with this line :

Private Sub CommandButton1_Click()

Now, your CommandButton1 will be attached to that macro and should work.

 

[ipbr21054]

Great.
Now that works.

One thing need to look at.

I click the command button & the file is saved BUT i notice that it also opens for me to see.
Which part is removed as i dont need to see it.

Just click on command & save.

Many thanks

 

[ipbr21054]

Ive now seen the code to remove.

Many thanks

 

[Logit]

.
CHANGE : OpenAfterPublish:=True TO: OpenAfterPublish:=False

You are welcome.

Glad to help.

 

[ipbr21054]

Or just remove it like i did.

Thanks