+ Reply to Thread
Results 1 to 3 of 3

Build sheet array from checkboxes?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-17-2009
    Location
    PA USA
    MS-Off Ver
    Excel 2000
    Posts
    184

    Build sheet array from checkboxes?

    I have a user form with checkboxes, one for each sheet in the workbook. The user selects the appropriate checkboxes then clicks a command button that prints selected sheets.

    How do I build the array so it holds only the selected sheets?

    I'm using this to print the sheets:
    Sheets(Array(sh1, sh2, sh3)).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    sh1, sh2 hold the sheet names:
    If CheckBox1.Value = True Then
    sh1 = "Control Panel"
    End If
    I hope this is enough information, thanks for helping me.

    Excel 2000
    Last edited by proepert; 03-28-2010 at 09:08 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Build sheet array from checkboxes?

    Hello proepert,

    Here is a macro for CommandButton1 on a UserForm to print the worksheets that have their CheckBox checked. Regular Arrays are zero based. The Sheets collection is 1 based. This is way you can not copy the names into a standard VBA array. The macro assumes each CheckBox.Caption is the name of a worksheet and that these are the only checkboxes on the UserForm.
    Private Sub CommandButton1_Click()
     'PRINT SHEETS
      
      Dim ChkBox As MSForms.CheckBox
      Dim Ctrl As Object
      Dim I As Integer
      Dim ShtArray() As Variant
      
        For Each Ctrl In Me.Controls
          If TypeName(Ctrl) = "CheckBox" Then
            Set ChkBox = Ctrl
            If ChkBox.Value = True Then
              I = I + 1
              ReDim Preserve ShtArray(1 To I)
              ShtArray(I) = ChkBox.Caption
            End If
          End If
        Next Ctrl
    
        Sheets(ShtArray).PrintOut
      
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    11-17-2009
    Location
    PA USA
    MS-Off Ver
    Excel 2000
    Posts
    184

    Re: Build sheet array from checkboxes?

    Thank you very much. This works well.
    Last edited by proepert; 03-28-2010 at 09:08 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1