Print parts of Worksheet based on input from user

[darren4164]

My first post - I'm new to VBA.

My workbook has about 20 worksheets. I would like to get input from the user that will then call macros to print specific sheets. I've got the print macros working correctly but I keep getting Run-timer error 424 - Object required errors when I take the input and call upon the various print macros.

For simplicity I have put CheckBoxes beside the different choices (there are four choices - 'All', 'Summary', 'Service' and 'Admin').

My coding is as follows:

Sub Test()

If CheckBox1.Value = True Then
Call Print_All
End If


If CheckBox2.Value = True Then
Call Print_Summary
End If


If CheckBox3.Value = True Then
Call Print_Service
End If


If CheckBox4.Value = True Then
Call Print_Admin
End If
End Sub


I also tried getting the User input through a ListBox but got the same error.

Any help is greatly appreciated!!!

 

[dmt32]

Hi,
welcome to forum.

If you run your code from the worksheets code page, you should find it works ok

As you are getting the 424 error suggests that you have placed the code in a standard module in which case, you need to refer to the controls in a different way


Try this update

Sub Test()
    Dim i As Integer
    Dim RunMacro As String
        For i = 1 To 4
            RunMacro = Choose(i, "Print_All", "Print_Summary", "Print_Service", "Print_Admin")
            If ThisWorkbook.Worksheets("Sheet1").OLEObjects("CheckBox" & i).Object.Value Then
                Application.Run RunMacro
            End If
        Next i
End Sub

Change sheet name shown in red as required

You can read more about controls on worksheet here:https://www.engram9.info/excel-vba-macros/referring-to-a-control-on-a-worksheet.html

Dave

 

[darren4164]

Awesome Dave - thanks so much.

 

[dmt32]

Awesome Dave - thanks so much.

Most welcome


Dave