Toggle Buttons to select multiple sheets for printing

[kakiebos]

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.

 

[Fluff]

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.

 

[Fluff]

If you want to stick with toggles then you could use something like

[COLOR=#0000ff]Dim ShtDic As Object[/COLOR]
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.

 

[kakiebos]

Thanks for the help.

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

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

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.

 

[Fluff]

Are those actual sheet names your using (as seen on the sheet tab) or are they the sheet codenames?

 

[kakiebos]

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.

 

[Fluff]

In that case change the .PrintOut to .Select & then add some code to export to pdf.

 

[kakiebos]

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?

 

[Fluff]

How about

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.

 

[kakiebos]

How about

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.

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?