Prompt User for Number, then pass to another Sub procedure.

[zero269]

Hello,

I'm trying to figure out a way to prompt a user to input a number and to be able to use that input with a different sub procedure.
My reasoning is that I want to reuse this User Prompt in different scenarios instead of baking it into several sub procedures.

For example, I have a sub procedure that prompts me to enter a number which is then used to Insert Table Rows. I want to split the two tasks:

1. Get Input (possibly a Function?)
2. Insert Rows

Here's what I'm currently using to perform both tasks in a single sub procedure:
Any advice would be greatly appreciated.

Sub InsertRows()
'Prompt User for number of rows to insert
    Dim iPrompt As Integer
    iPrompt = InputBox("Enter number of rows to insert", "Insert Rows at end of Table", 1)

    If iPrompt < 1 Then
        Exit Sub
    End If

' Loop Insert Rows
    Dim iCounter As Integer
    iCounter = 0
    Do While iCounter < iPrompt  'Inner Loop
        ActiveSheet.ListObjects(1).ListRows.Add 'Using Table Index
        iCounter = iCounter + 1 ' Increment iCounter
    Loop

' Select first new row
    ActiveSheet.ListObjects(1).TotalsRowRange(1).Select 'Select 1st column in totals row
    ActiveCell.Offset(-iPrompt).Select 'Offsets to select first new row
End Sub

My failed attempt at a function:
I'm not even sure if a function here is the right approach.

Function GetNum(iPrompt As Integer)
    MsgBox iPrompt
End Function

 

[zero269]

When you execute the first module, you should be able to see that the 'iPrompt' value is being passed to both of the other two modules.

Hi Johnny,
I was able to test your code just now, and I can see that it worked as you've stated.
I ran Module1Sub and the Message Boxes reported back from Mod2&3 the correct number that I inputted.
Thank you!

 

[johnnyL]

Thank you for the update.