Using input box to define variable

[gwladys]

I've included a few print macros in a Excel file, helped along the way in this forum.

The macro works when a checkbox is ticked. The output in this case is cell J4.

I've defined a variable (printfilling) which is then used to define number of copies to be printed. Obviously I could just leave the number in the main part of the macro.

But I would like to enable the user to define the value of the variable using an input box.

Any help gratefully received.




Sub filling()
'
' filling Macro
'
Dim printfilling As Integer
printfilling = 3

If Range("j4") = True Then
ActiveSheet.PageSetup.PrintArea = "$b$41:$ac$68"
ActiveWindow.SelectedSheets.PrintOut Copies:=printfilling, Collate:=True, IgnorePrintAreas:=False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Else
Range("j4").Select
End If

End Sub

 

[Trevor G]

Try this

Sub filling()
'
' filling Macro
'
Dim printfilling As Integer
printfilling = InputBox("Please enter the amount of copies you require", "Print")

If Range("j4") = True Then
ActiveSheet.PageSetup.PrintArea = "$b$41:$ac$68"
ActiveWindow.SelectedSheets.PrintOut Copies:=printfilling, Collate:=True, IgnorePrintAreas:=False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Else
Range("j4").Select
End If
End Sub

 

[gwladys]

Hi Trevor, that works, many thanks.

There is just one issue: ticking the checkbox makes the input box work and the macro run, but unticking also makes the input box work, although it won't print.
Is there any command that will untick a checkbox at the end of a macro?

 

[Trevor G]

Whats the name of the Checkbox and what type of Checkbox are you using? Form Control or ActiveX Control.

 

[gwladys]

I'm using Form Control and J4 is the cell link.

As for the name, I've looked in several places and can't find out either what it is or how to change it.

 

[Trevor G]

When you right click the checkbox the name will appear in the name box next to the formula bar.

Add this line of code below the end if and it should switch back to unchecked.

 ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = -4146

 

[gwladys]

That's very useful to know. The whole of google didn't explain it as well. Works a treat, thank you very much...

 

[Trevor G]

Happy to help and thanks for letting me know