CODEWITHSUNDEEP
excelvba
Tornado168
Tornado168

Reputation: 465

Unselect or Deselect Worksheet

I have a workbook with x worksheets. And a button (commandbutton1) in Sheet1 which opens a userform with two checkboxes and a Preview Button to choose between 2 sheets to preview in PDF.

Here’s my Preview button 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

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)

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

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

    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.

Upvotes: 2

Views: 14189

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

The trick is how you pass the parameter False in

Sheets(Me.Controls(CheckBoxName).Caption).Select (False)

Is this what you are trying?

Private Sub CommandButton1_Click()
    Dim i As Integer, j As Integer

    For i = 1 To 6
        CheckBoxName = "CheckBox" & i
        If Me.Controls(CheckBoxName).Value = True Then
            j = j + 1
            Worksheets(Me.Controls(CheckBoxName).Caption).Select (j = 1)
        End If
    Next
    If j = 0 Then Exit Sub

    '
    '~~> Rest of your code
    '
End Sub

Before:

enter image description here

After:

enter image description here

OR

enter image description here

Upvotes: 1

Related Questions