InputBox answer for multiple Sub Calls results in blank cells

[Seedling]

I have a Sub that I had to split up into 12 Subs because of the 64k limit and an hugely expanded product list. Each Sub does exactly the same thing just in smaller chunks than before.
When this was a single Sub, the user was prompted to enter a product name which then populated a column and was used to populate other columns. It works great.
Now that I have 12 Subs, I need that same InputBox answer to carryover to all 12 without having the code in each one.

This is a portable, stand-alone workbook that gets used by many locations so I do not want to put my "lists" in another workbook to be called upon.
Below is the simple version that works.

Sub ProductLoad()
Dim product As String
product = InputBox("Enter Product", "Product")


Cells(5, 1).Value = "040"
Cells(5, 2).Value = product
Cells(5, 3).Value = "F"
Cells(5, 4).Value = "N"
Cells(5, 5).Value = "UNS"
Cells(5, 6).Value = "Standard"
Cells(5, 7).Value = "20KG"


Cells(6, 1).Value = "040"
Cells(6, 2).Value = product
Cells(6, 3).Value = "F"
Cells(6, 4).Value = "N"
Cells(6, 5).Value = "UNS"
Cells(6, 6).Value = "Standard"
Cells(6, 7).Value = "25KG"


Cells(5, 2).Select


MsgBox "The Product Load is Complete."


End Sub

Now take 12 of these Subs with many many more and different values within them and it looks like below.
When I run this, the input box comes up, I can enter the product and click OK, but nothing is populated in the cells which in turn leaves other cells blank.
If I put the input code in each of the sub-subs, the users gets prompted 12 times for the same data. I don't want to annoy my users like this.
How do I make it populate for all of the Subs with one input?

Sub ProductLoad()
Dim product As String
product = InputBox("Enter Product", "Product")


Call Product1
Call Product2
Call Product3
Call Product4
Call Product5
Call Product6
Call Product7
Call Product8
Call Product9
Call Product10
Call Product11
Call Product12
MsgBox "The Commercial Product Load is Complete."
End Sub

 

[Roderick_E]

I think the entire design is bad if it requires that many calls...BUT... why not store the input in temporary cells on a hidden sheet and then gather the inputs from there?

 

[dmt32]

Hi,
probably do not need 12 subs performing a similar function but to answer your question - a couple of ways you can pass a variable to other procedures are

- declare variable as Public ( this will need to be at Top of your module)

- pass value as an argument to called procedure

and example to pass your variable to the 12 subs

Sub ProductLoad()
    Dim Product As String
    Dim i As Integer
    Product = InputBox("Enter Product", "Product")
'cancel pressed
    If StrPtr(Product) = 0 Then Exit Sub
    
    For i = 1 To 12
        Application.Run ("Product" & i), Product
    Next i
    
    MsgBox "The Commercial Product Load is Complete.", 64, "Complete"
End Sub




Sub Product1(ByVal Product As String)
MsgBox Product
End Sub


Sub Product2(ByVal Product As String)
MsgBox Product
End Sub


Sub Product3(ByVal Product As String)
MsgBox Product
End Sub


Sub Product4(ByVal Product As String)
MsgBox Product
End Sub

'etc etc

If you share with board how you other subs are configured, someone here may be able to offer further guidance.

Hope Helpful

Dave

 

[Fluff]

You can also simplify the sub you've shown like

Sub ProductLoad()
Dim product As String

product = InputBox("Enter Product", "Product")

Range("A5:F6").Value = Array("040", product, "F", "N", "UNS", "Standard")
Range("G5:G6").Value = Application.Transpose(Array("20KG", "25KG"))
Cells(5, 2).Select

MsgBox "The Product Load is Complete."

End Sub