InputBox answer for multiple Sub Calls results in blank cells

Seedling

New Member
Joined
Jun 19, 2018
Messages
1
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?
 
Upvote 0
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

Code:
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
 
Last edited:
Upvote 0
You can also simplify the sub you've shown like
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top