Push button and input box?

[tripflex]

I was wondering if anybody knows how to create a push button on a spreadsheet
that will then pop up something that the user can input a number and then it
will run a macro. Like basically i have a sub called DuplicateSheet (sheets
as integer) and i want it to ask the user how many sheets to duplicate then
it will run the script.

Thanks!!!

 

[Gary''s Student]

Either:

View > Toolbars > Forms and draw the button from there or

Put any piece of ClipArt on the worksheet, right-click it, and assign the
macro.

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim HowMany As Long

HowMany = CLng(Application.InputBox(Prompt:="How many duplicates?", _
Type:=1))

'some validity checks
If HowMany < 1 _
Or HowMany > 100 Then
MsgBox "Get Real!"
Exit Sub
End If

Call DuplicateSheet(HowManySheets:=HowMany)
End Sub

Sub DuplicateSheet(HowManySheets As Long)
MsgBox HowManySheets
End Sub

I wouldn't use Sheets as a variable (Excel's VBA has an object called Sheets).

And I wouldn't use "as integer". It's limited to smaller numbers and your
computer will convert it to a Long before it uses it anyway.