none
Possibly Streamline Code? RRS feed

  • Question

  • Hello -

    I am wondering if anyone might know of a better way of doing what I am attempting to do with the below code. The code basically calls to print a sheet. What I would like to do is possibly shrink the size of the sheet that prints.

    I am attempting to do this by suppressing rows; rows who's checkbox is not checked

    Here is what I have thus far...oh and the catch...I have 96 rows with 96 checkboxes :) Looking to somehow consolidate the highlighted areas?

     

    Private Sub Print_All_Click()

     

        Sheets("Vendor Request").PrintOut Copies:=1, Collate:=True, _

            IgnorePrintAreas:=False

           

            If Sheets("Vendor Request").MultiSelect_CoverLabel.Visible = False Then

                Exit Sub

        Else

           

        Sheets("Properties").Unprotect Password:=""

       

            If Sheets("Properties").CheckBox_001.Value = False Then

                Sheets("Properties").Range("A13").EntireRow.Hidden = True

                Sheets("Properties").CheckBox_001.Visible = False

                End If

            If Sheets("Properties").CheckBox_002.Value = False Then

                Sheets("Properties").Range("A14").EntireRow.Hidden = True

                Sheets("Properties").CheckBox_002.Visible = False

                End If

            If Sheets("Properties").CheckBox_003.Value = False Then

                Sheets("Properties").Range("A15").EntireRow.Hidden = True

                Sheets("Properties").CheckBox_003.Visible = False

                End If

            If Sheets("Properties").CheckBox_004.Value = False Then

                Sheets("Properties").Range("A16").EntireRow.Hidden = True

                Sheets("Properties").CheckBox_004.Visible = False

                End If

       

        Sheets("Properties").Visible = True

        Sheets("Properties").Select

       

        Sheets("Properties").PrintOut Copies:=1, Collate:=True, _

            IgnorePrintAreas:=False

           

                Sheets("Properties").Range("A13").EntireRow.Hidden = False

                Sheets("Properties").CheckBox_001.Visible = True

               

                Sheets("Properties").Range("A14").EntireRow.Hidden = False

                Sheets("Properties").CheckBox_002.Visible = True

               

                Sheets("Properties").Range("A15").EntireRow.Hidden = False

                Sheets("Properties").CheckBox_003.Visible = True

               

                Sheets("Properties").Range("A16").EntireRow.Hidden = False

                Sheets("Properties").CheckBox_004.Visible = True

                   

        Sheets("Properties").Protect Password:=""

               

        Sheets("Vendor Request").Visible = True

        Sheets("Vendor Request").Select

        Sheets("Properties").Visible = False

     

        End If

       

    End Sub

    Here is a little look at the form as well....

    Any ideas if this can be done or not will be greatly appreciated.

    Thank you for your time.


    Monday, June 19, 2017 10:17 PM

Answers

  • Hi rstreets2,

    You could loop through all Check Boxes and hide/unhide correspondent rows via the checkbox's name. Such as CheckBox_001 corresponds to Row 13, CheckBox_002 corresponds to Row 14. You could loop through Sheet.OLEObjects Collection and confirm it as a checkbox according to OLEObject.progID property. Besides, you need skip the SelectAll checkbox in row 11.

    Here is the example.

    Sub FirstPart()
    Dim obj As OLEObject
    Dim num As Integer
    Dim objName As String
    For Each obj In ActiveSheet.OLEObjects
        If obj.progID = "Forms.CheckBox.1" Then
            objName = obj.name
            If Not objName = "CheckBox_SelectAll" Then 'assume that the selectall checkbox name is CheckBox_SelectAll
             If obj.Object.Value = False Then
              num = CInt(Mid(objName, 10, 3))
              ActiveSheet.Range("A" & (num + 12)).EntireRow.Hidden = True
             End If
            End If
        End If
    Next obj
    End Sub


    Best Regards,

    Terry

    • Marked as answer by rstreets2 Wednesday, June 28, 2017 10:14 PM
    Tuesday, June 20, 2017 2:56 AM
  • Hi rstreets2,

    You could use OLEObject.Visible Property to show/hide a checkbox, just like

    obj.Visible = false

    Best Regards,

    Terry

    • Marked as answer by rstreets2 Wednesday, June 28, 2017 10:14 PM
    Thursday, June 22, 2017 2:02 AM

All replies

  • Hi rstreets2,

    You could loop through all Check Boxes and hide/unhide correspondent rows via the checkbox's name. Such as CheckBox_001 corresponds to Row 13, CheckBox_002 corresponds to Row 14. You could loop through Sheet.OLEObjects Collection and confirm it as a checkbox according to OLEObject.progID property. Besides, you need skip the SelectAll checkbox in row 11.

    Here is the example.

    Sub FirstPart()
    Dim obj As OLEObject
    Dim num As Integer
    Dim objName As String
    For Each obj In ActiveSheet.OLEObjects
        If obj.progID = "Forms.CheckBox.1" Then
            objName = obj.name
            If Not objName = "CheckBox_SelectAll" Then 'assume that the selectall checkbox name is CheckBox_SelectAll
             If obj.Object.Value = False Then
              num = CInt(Mid(objName, 10, 3))
              ActiveSheet.Range("A" & (num + 12)).EntireRow.Hidden = True
             End If
            End If
        End If
    Next obj
    End Sub


    Best Regards,

    Terry

    • Marked as answer by rstreets2 Wednesday, June 28, 2017 10:14 PM
    Tuesday, June 20, 2017 2:56 AM
  • Thank you Terry. I will play around with this and let you know the outcome. Much appreciated.
    Wednesday, June 21, 2017 9:03 PM
  • Terry you are amazing! Worked like a charm! Thank you, thank you, thank you! Now if there was only a way to hide the unchecked checkboxes at the same? :) Think that might be a possibility?
    Wednesday, June 21, 2017 9:26 PM
  • Hi rstreets2,

    You could use OLEObject.Visible Property to show/hide a checkbox, just like

    obj.Visible = false

    Best Regards,

    Terry

    • Marked as answer by rstreets2 Wednesday, June 28, 2017 10:14 PM
    Thursday, June 22, 2017 2:02 AM
  • Yup. Worked perfect! Thank you again Terry for your assistance with this. Much appreciated!
    Wednesday, June 28, 2017 10:14 PM