0 votes
in VBA by Beginner (36 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 Skilled (419 points)

Hi abdelfattah,

It sounds like you want your InputBox to accept something like these examples:

  • 1,2 (print/publish sheets 1 and 2)
  • 1-4 (print/publish sheets 1 2 3 and 4)
  • 1,2,8-10 (print/publish sheets 1  2 8 9 and 10)

If this is what you want, I can show you a cool macro to parse such input.

If you want something else, please describe it as clearly as possible.

 

By the way, CASE statements are "Case-sensitive". Sheet2 is probably the name of the worksheet and it will not match the option sheet2 as it appears in all lower-case.

 

I like to use 

Select Case UCASE(ws.name)

     Case "SHEET1", "SHEET2", "SHEET3"

:

 

Cheers,

 

Mitch

ago by Beginner (36 points)

hi  pareser    first  of  i'm really  sorry  about delaying   to  reply  i  don't  noted    the  second   i  noted  you   choosed the  sheets  Consecutively   actually    if  i  have  10   sheets   i  would  choose  sheet2,sheet4     and    if   is   possible   to  write   more than  sheet  in inputbox  to   do   that   one  time   like   this       sheet2, &sheet4   then  export and  print both like  this

https://www.dropbox.com/s/zaofsfs42sel8bf/13.gif?dl=0

ago by Skilled (419 points)

Hi abdelfattah,

No worries on the delay. Your process looks pretty complete, to me. The only things I would do is make the input as simple as possible. Since you didn't show the code for the Input box button, I'll just add these suggestions as a comment:

  • Allow the InputBox to accept shortened-names for each sheet (1,2,3)
  • Allow the InputBox value to have ranges (like 1-4)
  • Allow the InputBox to accept a mix of ranges and non-contiguous pages (1-3,5,7,8-11)
The second two might be overkill, if you only have six or fewer sheets. The first suggestion simply saves you time. Let the macro concatenate each value to the string "Sheet".
 
If you need more help, just reply.
 
Cheers,
 
Mitch
 
ago by Beginner (36 points)
but  somtimes  the    sheet  calls   other  names  like   master    then  this  is  not  practical      to write 1,2,3    it  should   master  ,sales,phurchase  ,data ...etc    so  if  you add another  option  is  ok   but  the  most  important  related nam's  sheet   and  print
ago by Skilled (419 points)
I see. Well, can you share the code that worked on the Dropbox GIF? I'd like to make recommendations based on that, since it seems to work and you're looking for more flexibility.

 

Cheers,

 

Mitch
ago by Beginner (36 points)
actually  this  is  the  same  code   above   when   i  said   about  inputbox   i hope  somebody   to  adjust    the  code   by  add  line  code   about  inputbox   i  have  no  experience   about vba

Please log in or register to answer this question.

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.

...