Can you check my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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
 
.
Duh ! My old eyes didn't see that. Sorry ..

Get rid of this line :

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

Have your command button connected to this macro instead :

Code:
[COLOR=#333333]Sub SveRngPDF()[/COLOR]
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
.
I've read this over several times .. hope there are no errors.

Code:
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
 
Upvote 0
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.

Code:
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.

Code:
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
 
Upvote 0
.
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 :


Code:
Sub SveRngPDF()


Replace that line with this line :


Code:
Private Sub CommandButton1_Click()


Now, your CommandButton1 will be attached to that macro and should work.
 
Upvote 0
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
 
Upvote 0
.
CHANGE : OpenAfterPublish:=True TO: OpenAfterPublish:=False

You are welcome.

Glad to help.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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