vba Deselect Sheet

Tornado1981

Board Regular
Joined
Apr 1, 2010
Messages
248
Hi,

I have a workbook with x worksheets.

And a button (commandbutton1) in Sheet1 which opens this userform to choose between 2 sheets to preview in PDF.

Here’s my Preview button code.


VBA Code:
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long[/FONT][/COLOR]

Private Sub CommandButton1_Click()
     Dim i As Integer
Dim CheckBoxName As String

For i = 1 To 2
CheckBoxName = "CheckBox"& i
If Me.Controls(CheckBoxName).Value = True Then
Sheets(Me.Controls(CheckBoxName).Caption).Select (False)
End If
Next i
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="D:\Preview.pdf"
Unload Me

Dim File As String
     File = "D:\Preview.pdf"
    ShellExecute 0, "Open", File, "", "", vbNormalNoFocus
End Sub


Now, the problem is that Sheet1 is always included in the preview file since it’s the sheet containing commandbutton1.


So is there a way to deselect sheet1 before exporting sheets 6 & 21 ??


I also tried another way but it always stuck at this line (Sheets(Array(SheetNames)).Select)


VBA Code:
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long[/FONT][/COLOR]

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim CheckBoxName As String
    Dim SheetNames As String
    Dim PreviewSheets As Variant

    For i = 1 To 2
        CheckBoxName = "CheckBox"&  i
        If Me.Controls(CheckBoxName).Value = True Then
            SheetNames = SheetNames &Me.Controls(CheckBoxName).Caption & ","
        End If
    Next i    

    SheetNames = Mid(SheetNames, 1, Len(SheetNames) - 1)    
    Sheets(Array(SheetNames)).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="D:\Preview.pdf"  
    Unload Me

    Dim File As String

    File = "D:\Preview.pdf" 
    ShellExecute 0, "Open", File, "", "", vbNormalNoFocus
End Sub


Any help with any of these please ??

Thank u in advance.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi Tornado1981,

You can add a flag to help you Replace the current selection of sheet(s) when the first print sheet is selected then Add to the selected sheet(s) when subsequent sheets are selected.

Code:
Private Sub CommandButton1_Click()
 Dim bNoneSelected As Boolean
 Dim i As Integer
 Dim CheckBoxName As String
 Dim wksStart As Worksheet
 
 Const sFILENAME As String = "C:\Test\Preview.pdf"
 
 '--flag if at least one checked sheet selected
 bNoneSelected = True
 Set wksStart = ActiveSheet
 
 For i = 1 To 2
    CheckBoxName = "CheckBox" & i
    If Me.Controls(CheckBoxName).Value = True Then
      Sheets(Me.Controls(CheckBoxName).Caption).Select _
         Replace:=bNoneSelected
      bNoneSelected = False
    End If
 Next i
   
 If bNoneSelected Then
   MsgBox "No sheets to were selected to preview"
 Else
   Unload Me
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=sFILENAME
   
   ShellExecute 0, "Open", sFILENAME, "", "", vbNormalNoFocus
   '--select starting sheet; deselect other sheets
   wksStart.Select Replace:=True
 End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Instead of using ActiveSheet why not explicitly reference the sheets you want to print to PDF?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,494
Members
414,071
Latest member
hijackhippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top