Consulting

Results 1 to 7 of 7

Thread: EXCEL 360 vba code print hidden excel sheets

  1. #1
    VBAX Regular
    Joined
    Mar 2020
    Location
    France
    Posts
    9
    Location

    Angry EXCEL 365 vba code print hidden excel sheets

    Projet test 1.0.xlsm



    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.
    Last edited by hokousai; 03-15-2020 at 07:46 PM.

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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 Sub
    Artik

  3. #3
    VBAX Regular
    Joined
    Mar 2020
    Location
    France
    Posts
    9
    Location
    Thank you very much Artik
    it takes away a big thorn from my foot


    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

  4. #4
    VBAX Regular
    Joined
    Mar 2020
    Location
    France
    Posts
    9
    Location
    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 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
    Attached Files Attached Files

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  6. #6
    VBAX Regular
    Joined
    Mar 2020
    Location
    France
    Posts
    9
    Location
    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

    Last edited by hokousai; 03-20-2020 at 07:24 PM.

  7. #7
    VBAX Regular
    Joined
    Mar 2020
    Location
    France
    Posts
    9
    Location
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •