PDA

View Full Version : EXCEL 360 vba code print hidden excel sheets



hokousai
03-15-2020, 06:37 PM
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



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.

Artik
03-15-2020, 07:22 PM
The corrected apercu_Click procedure is below. In a similar way correct imprimer_Click
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 SubArtik

hokousai
03-15-2020, 09:02 PM
Thank you very much Artik :clap:
it takes away a big thorn from my foot :thumb



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

hokousai
03-20-2020, 08:20 AM
Good morning all,
I reopen this thread because I would like to integrate this code below into my Userform with a button

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 SubTo 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

Artik
03-20-2020, 04:26 PM
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

hokousai
03-20-2020, 07:10 PM
Thanks Artik,
you are a boss


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

hokousai
03-27-2020, 07:44 PM
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