Using input box to define variable

gwladys

New Member
Joined
Apr 2, 2005
Messages
34
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,511
Try this

VBA Code:
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

New Member
Joined
Apr 2, 2005
Messages
34
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,511
Whats the name of the Checkbox and what type of Checkbox are you using? Form Control or ActiveX Control.
 

gwladys

New Member
Joined
Apr 2, 2005
Messages
34
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,511
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.

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

gwladys

New Member
Joined
Apr 2, 2005
Messages
34
That's very useful to know. The whole of google didn't explain it as well. Works a treat, thank you very much...
 

Watch MrExcel Video

Forum statistics

Threads
1,100,040
Messages
5,472,127
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top