0 votes
in VBA by Beginner (48 points)

 

hello

i  need  help to  add my  code  inputbox  to  become flexible   i would    write  in inputbox   specific sheets  to  export  sheets as pdf and  gives me  message about printing   two  choice   if  yes then  print out directly  if  no    then just  publish the  file  as  pdf 
note:  i  would  write  more  than sheet  in inputbox  to do  one time  not  every  time i enter each sheet in inputbox 

Sub Sample()

    ToggleVisible False

    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "exported file.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    ToggleVisible True

End Sub

Private Sub ToggleVisible(state As Boolean)
    Dim ws As Object

    For Each ws In ThisWorkbook.Sheets
        Select Case ws.Name
        Case "Sheet1", "sheet2", "sheet3"
        Case Else
            ws.Visible = state
        End Select
    Next ws
End Sub

 

by Beginner (48 points)
actually  i    ran it  so  i would for you  test it   what's wrong
by Skilled (477 points)
Hi abdelfattah,

Your last comment is unclear.

Sorry.

Cheers,

 

Mitch
by Beginner (48 points)
i meant   running  SelectWorksheets macro. as  you  said  me   did  you test  my  file  becausethe  problem  still continues
by Skilled (477 points)

Hi abdelfatah,

Thanks for letting me know. I found the problem. Your copy is still calling SAMPLE. Right-click the button and Assign Macro Selectworksheets.

by Beginner (48 points)
hi, paraser  

i  have  no   macro  called simple   i  run  the  macro   from inside  the  module    no  changes  so  far

1 Answer

+1 vote
by Skilled (477 points)
selected by
 
Best answer


Hi abdelfattah,

I've completely modified the solution to print single PDFs. The macro no longer has to hide and unhide worksheets. It will validate the Inputbox by simply failing to match typos. This time, I tested the code with your Dropbox file. Just remember to assign the SelectWorksheets macro to the button!

 

Option Explicit

    ' Targets is a variable holding all of the worksheets to be printed
    ' Typos and non-existent sheets will not be printed, because the DEFAULT BEHAVIOR
    ' of PrintWorksheets is to export sheets that do appear in TargetList.
    ' This turns out to be a simple way to validate the input.
    
    Dim Targets As String
    
Sub SelectWorksheets()
    
    ' Create an UPPERCASE copy of the string from InputBox.
    ' This will ensure proper functioning of the Case statement in PrintWorksheets
    
    Targets = UCase(InputBox("CHOOSE SHEET", "SELECT"))
    
    If Targets = "" Then
        Exit Sub
    End If
    
    ' Print worksheets
    PrintWorksheets

End Sub

Private Sub PrintWorksheets()
    
    Dim ws As Worksheet
    Dim SaveDir As String
    Dim SaveDirFileName As String
    
    ' Don't stop macro if there is nothing to print
    On Error Resume Next
    
    SaveDir = ThisWorkbook.Path & "\"
    For Each ws In ThisWorkbook.Sheets
        With ws
            If InStr(Targets, UCase(.Name)) > 0 Then
                SaveDirFileName = .Name & ".pdf"
                .Activate
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                SaveDir & SaveDirFileName _
                , Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
                ' Comment out next line if you WANT to see page breaks
                .DisplayPageBreaks = False
            End If
        End With
    Next ws
    
    ThisWorkbook.Sheets("Master Sheet").Activate
    
    ' Reset error handling
    On Error GoTo 0
    
End Sub

Cheers,

 

Mitch

 

by Beginner (48 points)
yes, now   it  works   but  remains  one  thing  , what about  print   it  supposses  give  me  two choice to  print or  not   when  publish  the  file  as  what  i  ask   in my post above
by Skilled (477 points)
Hi abdelfattah,

I've done all that I'm going to do with this question. You may want to hire someone to add the final feature.

 

Cheers,

 

Mitch
by Beginner (48 points)
hi, parser i  appreciate  your  effort   i do not  ask  something else  not  relating  in  my  question  i  asked    when  i  issue   my  post    you   should read  well  i  have  ever asked about  the  print   you can  check   my  post
by Skilled (477 points)

Hi abdelfattah,

You're absolutely right. I didn't notice the request for either print or publish. On the other hand, I think these forums are for help, not for complete applications. In your post you say, " i  need  help to  add my  code  inputbox  to  become flexible  "

The code I supplied will help with the publish part. You just need to figure out how to do the print part.

 

Cheers,

 

Mitch

 

by Beginner (48 points)
thanks parser  for  every thing  ,actually  you  worked    a great  work  i would  thank you  for  your Patience and interest  to  solve  my  problem ,  i considerd  you   have solved  my  problem  as  for the  print  i  can  find  way  to  do  that

god bless you
by Skilled (477 points)
I appreciate you, abdelfattah. Thanks for your comments. I was happy to help you along the path.

Cheers,

 

Mitch

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...