Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Toggle Buttons to select multiple sheets for printing

  1. #1
    New Member
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Toggle Buttons to select multiple sheets for printing

    Hi All.

    I created a user form with multiple toggle buttons. Each buttons represents a sheet in the spread sheet. I would like to be able to only print the pages of the buttons that the user selected.

    At first I tried to use the respective click button subs to select the sheets. It only select the one sheet and as soon as I click on the next button it selects the next sheet and unselect the previous sheet.

    I need it to keep all the sheets selected according to the buttons that was selected. That is if the toggle button value is true the sheet needs to be selected and if the value is false the sheet must not be selected.

    What code will I need to write for this?

    I would prefer not to use a list box with multi select as it is sometimes easy to forget to press the control button for multi selection and most of the users that will use this spreadsheet is not really computer literate and will not know how to use shift and control to select multiple selections in a list box.

    I'm pretty new to vba, so any help will be appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    If you set the MultiSelect property of a listbox to 1-fmMultiSelectMulti you do not need to Shift Click to select multiple items, you can just click each item one by one.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    If you want to stick with toggles then you could use something like
    Code:
    Dim ShtDic As Object
    Private Sub CommandButton1_Click()
      If ShtDic.Count > 0 Then Sheets(ShtDic.Keys).PrintOut
    End Sub
    
    Private Sub ToggleButton1_Click()
    If Me.ToggleButton1 Then
       ShtDic.Add "Sheet1", Nothing
    Else
       ShtDic.Remove "Sheet1"
    End If
    End Sub
    Private Sub ToggleButton2_Click()
    If Me.ToggleButton2 Then
       ShtDic.Add "Sheet2", Nothing
    Else
       ShtDic.Remove "Sheet2"
    End If
    End Sub
    
    Private Sub UserForm_Initialize()
    Set ShtDic = CreateObject("scripting.dictionary")
    End Sub
    The line in blue must be at the very top of the module, before any code.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    Thanks for the help.

    I have modified the code to have my sheet names and button names instead of the generic names you used.


    Code:
    Dim ShtDic As Object
    
    
    Private Sub cmdPrint_Click()
      If ShtDic.Count > 0 Then Sheets(ShtDic.Keys).PrintOut
    End Sub
    
    
    Private Sub tglSummary_Click()
    
    
      If Me.tglSummary Then
        ShtDic.Add shSummary, Nothing
      Else
        ShtDic.Remove shSummary
      End If
    End Sub
    
    
    Private Sub tglMon_Click()
      If Me.tglMon Then
         ShtDic.Add shMon, Nothing
      Else
         ShtDic.Remove shMon
      End If
    End Sub
    
    
    Private Sub tglTue_Click()
      If Me.tglTue Then
         ShtDic.Add shTue, Nothing
      Else
         ShtDic.Remove shTue
      End If
    End Sub
    
    
    Private Sub tglWed_Click()
      If Me.tglWed Then
         ShtDic.Add shWed, Nothing
      Else
         ShtDic.Remove shWed
      End If
    End Sub
    
    
    Private Sub tglWeek_Click()
      If Me.tglWeek.Value = True Then
        Me.tglSummary.Value = True
        Me.tglMon.Value = True
        Me.tglTue.Value = True
        Me.tglWed.Value = True
        Me.tglThu.Value = True
        Me.tglFri.Value = True
        Me.tglSat.Value = True
        Me.tglSun.Value = True
      Else
        Me.tglSummary.Value = False
        Me.tglMon.Value = False
        Me.tglTue.Value = False
        Me.tglWed.Value = False
        Me.tglThu.Value = False
        Me.tglFri.Value = False
        Me.tglSat.Value = False
        Me.tglSun.Value = False
      End If
      
    End Sub
    
    
    Private Sub cmdCancel_Click()
      Unload Me
    End Sub
    
    
    Private Sub UserForm_Initialize()
    Set ShtDic = CreateObject("scripting.dictionary")
    End Sub
    but when I run the code and select the print button, I get a run-time error '13: Type mismatch. When I click on the debug button the following line of code is highlighted

    Code:
    Private Sub cmdPrint_Click()
      If ShtDic.Count > 0 Then Sheets(ShtDic.Keys).PrintOut   'Sheets(ShtDic.Keys).PrintOut is highligted in Yellow
    End Sub
    I only did a few of the sheets for the selection in the userform to see if it is working. As soon as these buttons is working, I will add the rest of the code to accomodate those buttons.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    Are those actual sheet names your using (as seen on the sheet tab) or are they the sheet codenames?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    Sheet Code names.
    Should I be using the actual sheet names?

    Edit:
    I changed the code names to the actual sheet names and it is working perfectly. Only one more thing, currently it is sending the document directly to the printer and I would rather save it as a pdf.
    Last edited by kakiebos; Sep 2nd, 2019 at 01:35 AM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    In that case change the .PrintOut to .Select & then add some code to export to pdf.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    That works like a charm. Thank you so much. Sorry for not always replying quickly. This is not my primary job, I only do this to better my life and those around me. I'm normally out on a site doing my normal mechanic work.


    After I saved the document as a PDF, the sheets that was selected stays selected. Currently I have a workaround by using code to select another sheet that will not be selected by the user and then selecting the sheet from where the user initialized the saving of the document. I believe that is a cumbersome way of doing it. Is there a better way to deselect those sheets?

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    How about
    Code:
    Private Sub CommandButton1_Click()
       Dim Ws As Worksheet
       Set Ws = ActiveSheet
       If ShtDic.Count > 0 Then Sheets(ShtDic.Keys).Select
       'print code here
       Ws.Select
    End Sub
    And no worries about the time taken to reply.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jun 2018
    Location
    South Africa
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Toggle Buttons to select multiple sheets for printing

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Private Sub CommandButton1_Click()
       Dim Ws As Worksheet
       Set Ws = ActiveSheet
       If ShtDic.Count > 0 Then Sheets(ShtDic.Keys).Select
       'print code here
       Ws.Select
    End Sub
    And no worries about the time taken to reply.
    This code is working perfect. I have the following code for the printing.

    Code:
    Sub Print_Selected_Sheets()
      
      Dim FileDir As String
      Dim PDF As String
      Dim SDateRslt As String
      
      Name = shSummary.Range("B7").Value
      WWID = UCase(shSummary.Range("B8").Value)
    '  InputBox "What is the start date of this Time Sheet?", "Start Date", shSummary.Range("Y7").Value
      SDate = Format(shSummary.Range("Y7").Value, "yyyymmdd")
      FileDir = "C:\Users\" & Environ$("UserName") & "\Documents\" & "Timesheets\"
      PDF = "Timesheets " & Name & " (" & WWID & ") " & SDate & ".pdf"
      
      Application.ScreenUpdating = False
      
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileDir & PDF, _
      OpenAfterPublish:=True
      
      
      Application.ScreenUpdating = True
    End Sub
    I want to force the .pdf file name to have the user's name in Proper Case. I understand how to do it for upper case and lower case, but cant find something to work for Proper Case.
    SDate is the first day of the week that is in cell Y7.
    Sometimes the month only start on another day of the week and then SDate is to be the first day of the month. Can I use something like a input box where the user changes the SDate to the first day of the month manually/automatically?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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