EXCEL 360 vba code print hidden excel sheets

Attachment 26160



Good morning all,
I have a problem with my excel file at the time of printing.
My sheets are hidden.
I use this code to print the sheets hide

Code:

---

Private Sub print_Click ()
Dim Fe As Worksheet
Application.ScreenUpdating = False
For Each Fe In Worksheets
If Fe.Visible = xlSheetHidden Then
Fe.Visible = xlSheetVisible
Fe.PrintOut
Fe.Visible = xlSheetHidden
End If
Next Fe
Application.ScreenUpdating = False
End Sub.

---

But impossible to print the sheet that I select in the UserForme.
This prints the three sheets visible in the ListBox.
I am a noob in VBA I do not understand where the error in the codes is.
I need your help
Thank you in advance.

The corrected apercu_Click procedure is below. In a similar way correct imprimer_Click

Code:

---

Private Sub apercu_Click()    Dim i          As Integer
    Dim isVisible As XlSheetVisibility
   
    Me.Hide
    Application.ScreenUpdating = False
   
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
              isVisible = Sheets(.List(i)).Visible
              Sheets(.List(i)).Visible = xlSheetVisible
              Sheets(.List(i)).PrintPreview
              Sheets(.List(i)).Visible = isVisible
            End If
        Next
    End With
   
    Application.ScreenUpdating = True
    Me.Show
End Sub

---

Artik

Thank you very much Artik :clap:
it takes away a big thorn from my foot :thumb

Code:

---

Private Sub apercu_Click()       
    Dim i As Integer
    Dim isVisible As XlSheetVisibility
   
    Me.Hide
    Application.ScreenUpdating = False
   
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
              isVisible = Sheets(.List(i)).Visible
              Sheets(.List(i)).Visible = xlSheetVisible
              Sheets(.List(i)).PrintPreview
              Sheets(.List(i)).Visible = isVisible
            End If
        Next
    End With
   
    Application.ScreenUpdating = True
    Me.Show
End Sub


----------------------------------------------------------------------------------------------------------
Private Sub imprimer_Click()   
    Dim i As Integer
    Dim isVisible As XlSheetVisibility
   
    Me.Hide
    Application.ScreenUpdating = False
   
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
              isVisible = Sheets(.List(i)).Visible
              Sheets(.List(i)).Visible = xlSheetVisible
              Sheets(.List(i))..PrintPreview
              Sheets(.List(i)).Visible = isVisible
            End If
        Next
    End With
   
    Application.ScreenUpdating = True
    Me.Show
End Sub

---

Good morning all,
I reopen this thread because I would like to integrate this code below into my Userform with a button

Code:

---

Private Sub CommandButton1_Click()Dim sh As Worksheet, i&
For i = 2 To Sheets.Count
  With Sheets(i).PageSetup
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
  End With
Next
  Set sh = ActiveSheet
  Sheets(Array .Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\"\" & Sheets(1).[C2].Value & ".pdf"
  sh.Select
End Sub

---

To print the sheets in a single PDF (my original file contains more than twenty sheets and I don't want to waste sheets and ink for testing).
The sheets must remain hidden.
Thank you in advance for your precious help

Code:

---

Private Sub CommandButton1_Click()
    Dim i          As Long
    Dim k          As Long
    Dim varrSelected() As Variant
    Dim varrToSave  As Variant
    Dim shActiv    As Object


    k = -1
    Application.ScreenUpdating = False
   
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            k = k + 1
            ReDim Preserve varrSelected(0 To 1, 0 To k)


            varrToSave = varrToSave & "/" & ListBox1.List(i)


            varrSelected(0, k) = ListBox1.List(i)
            varrSelected(1, k) = ThisWorkbook.Sheets(varrSelected(0, k)).Visible


            ThisWorkbook.Sheets(varrSelected(0, k)).Visible = xlSheetVisible
        End If
    Next i


    If k > -1 Then
        Set shActiv = ActiveSheet


        varrToSave = Mid(varrToSave, 2)
        varrToSave = Split(varrToSave, "/")


        ThisWorkbook.Sheets(varrToSave).Select
       
        'Feuil2 is a CodeName of "Parametres" sheet
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\" & Feuil2.Range("C2").Value & ".pdf"


        shActiv.Select


        For i = 0 To UBound(varrSelected, 2)
            ThisWorkbook.Sheets(varrSelected(0, i)).Visible = varrSelected(1, i)
        Next i
       
        MsgBox "Selected sheets were saved in a PDF file.", vbInformation
    End If
   
    Application.ScreenUpdating = True
End Sub

---

Artik

Thanks Artik,
you are a boss

Code:

---

Private Sub Enregistrer_1_seul_PDF_Click ()
Dim k As Long
Dim varrSelected () As Variant
Dim varrToSave As Variant
Dim shActiv As Object
k = -1
Application.ScreenUpdating = False
For i = 0 To ListBox1.ListCount - 1
Si ListBox1.Selected (i) Alors
k = k + 1
ReDim Preserve varrSelected (0 To 1, 0 To k)
varrToSave = varrToSave & "/" & ListBox1.List (i)
varrSelected (0, k) = ListBox1. List (i)
varrSelected (1, k) = ThisWorkbook.Sheets (varrSelected (0, k)). Visible
ThisWorkbook.Sheets (varrSelected (0, k)). Visible = xlSheetVisible
End If
Next i
Si k> -1, alors
définissez shActiv = ActiveSheet
varrToSave = Mid (varrToSave, 2)
varrToSave = Split (varrToSave, "/")
ThisWorkbook.Sheets (varrToSave)
. Sélectionnez ' Feuil2 is a code name from the "Parameters" sheet '' add the path of the file to be saved '' add the name of the final file in the cell
ActiveSheet.ExportAsFixedFormat Type: = xlTypePDF, Nom de fichier: = "C: \ Utilisateurs \ Nom \ Bureau \ Projet vierge \ "& Feuil2.Range (" C2 ").

Pour i = 0 à UBound (varrSelected, 2)
ThisWorkbook.Sheets (varrSelected (0, i)). Visible = varrSelected (1, i)
Next i
MsgBox "Les feuilles sélectionnées ont été enregistrées dans un fichier PDF

---

Bonjour,
Nouvelle conception de mon projet
Artik,
je ne peux pas intégrer votre code ci-dessus dans mon projet
Désolé de ne pas avoir eu le temps de le traduire

Merci