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!!!

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.
--
Gary''s Student - gsnu200836


"tripflex" wrote:

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!!!

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.


tripflex wrote:

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!!!

--

Dave Peterson