VBA, Array based on check box values?

Question

Good morning everyone. I am having a bit of difficulty trying to get a button working properly in Excel 2007, VBA. What this button does is group worksheets based on the check box value being true. Then it will display the print dialogue so the the user can print to PDF or to their printer.

I am not sure how to go about this. I did try using:

If me.chkTable1 = True Then Sheets(Table3.Name).Select
If me.chkTable2 = True Then Sheets(Table4.Name).Select

However I found that with this method the previous worksheet will deselect. I thought that maybe an array will work. That if the value of me.chkTableX = True then the array would be populated with the worksheet. Then at the end I could select the array for printing.

I am fairly new, so my apologies if I am missing something.

Scott Craner · Accepted Answer · 2016-04-13 16:36:46Z

0

Something like this will work:

Sub sheetselect()
Dim wsStr() As Variant
Dim ostr() As Variant

ReDim wsStr(0 To 0)

If Me.chktable1 = True Then
    wsStr(UBound(wsStr)) = table3.Name
    ReDim Preserve wsStr(UBound(wsStr) + 1) As Variant
End If
If Me.chktable2 = True Then
    wsStr(UBound(wsStr)) = table4.Name
    ReDim Preserve wsStr(UBound(wsStr) + 1) As Variant
End If
ReDim ostr(0 To UBound(wsStr) - 1)
For k = 0 To UBound(wsStr) - 1
    ostr(k) = wsStr(k)
Next k
Sheets(ostr).Select
End Sub

It loads an array with the names of the sheets. Then uses this array to select the proper sheets.

answered Apr 13, 2016 at 16:36

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA, Array based on check box values?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related