Push button and input box?
 

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

Push button and input box?
 

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

Push button and input box?
 

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