VBA Macro to identify text in cell, activate subfunction to enter input box data, and return value to another workbook

[aeddy2008]

VBA Macro to identify text in cell on welcome(ActiveWorksheet)(adders for project cost), activate subfunction to enter input box data(have input box that will save and return value to another workbook. I cant get anything to activate and have run out of my limited knowledge of vba coding. Your help would be appreciated.
Thanks,

Aaron



Sub AddersEnter()
If Range(E268).Value = "Array_Additional Array" Then
Call ArrayAdditionalArray
ElseIf Range("E268:E277").Value = "Array_Floating Panel" Then
Call Array_Floating_Panel
ElseIf Range("E268:E277").Value = "Array_Hazardous Metal Roof (Over 22 degrees)" Then
Call Array_Hazardous_Metal_Roof
ElseIf Range("E268:E277").Value = "Array_Hazardous Roof (Over 30 degrees)" Then
Call Array_Hazardous_Roof
ElseIf Range("E268:E277").Value = "Array_High Roof - Greater than 2 Stories" Then
Call Array_High_Roof
ElseIf Range("E268:E277").Value = "Array_Landscape Orientation" Then
Call Array_Landscape_Orientation
ElseIf Range("E268:E277").Value = "Array_Lansdscape Orientation (ET SolarBridge)" Then
Call Array_Landscape_SolarBridge
ElseIf Range("E268:E277").Value = "Array_Snap-N-Rack Edge Screen Rodent Barrier" Then
Call Array_Rodent_Barrier
ElseIf Range("E268:E277").Value = "BOS_Additional Inverters Subpanel" Then
Call BOS_Additional_Inverter_Subpanel
ElseIf Range("E268:E277").Value = "BOS_SMA Sunny Boy TL-US Secure Power Supply" Then
Call BOS_SMA_SunnyBoy_SecurePowerSupply
ElseIf Range("E268:E277").Value = "BOS_SolarBridge Additional Equipment" Then
Call BOS_SolarBridge_AdditionalEquipment
ElseIf Range("E268:E277").Value = "Install_Attic/Crawlspace Run" Then
Call Install_Attic_Crawlspace
ElseIf Range("E268:E277").Value = "Install_Conduit Painting - Greater than 70'" Then
Call Install_Conduit_Painting_Greater_70
ElseIf Range("EE268:E277").Value = "Install_Long Conduit Run - Greater than 70'" Then
Call Install_Long_Conduit_Run
ElseIf Range("E268:E277").Value = "Install_Mileage/Travel" Then
Call Install_MileageTravel
ElseIf Range("E268:E277").Value = "Install_Plumbing Roof Vent - Relocate" Then
Call Install_PlumbingVent
ElseIf Range("E268:E277").Value = "Install_Trenching" Then
Call Install_Trenching
ElseIf Range("E268:E277").Value = "Racking_AeroCompact Ballast Blocks(35lbs)" Then
Call Racking_AeroCompact_Ballast_Blocks
ElseIf Range("E268:E277").Value = "Racking_Ground Mount Additional Array" Then
Call Racking_GroundMount_Additional_Array
ElseIf Range("E268:E277").Value = "Racking_Metal Roof w/ Penetrations" Then
Call Racking_MetalRoof_Penetrations
ElseIf Range("E268:E277").Value = "Service_Custom Upgrade" Then
Call Service_Custom_Upgrade
ElseIf Range("E268:E277").Value = "Service_Main Breaker Kit(Custom)" Then
Call Service_Main_Breaker_Kit_Custom
End If
End Sub


Sub ArrayAdditionalArray()
Dim Quantity As String

Quantity = InputBox("Please enter the quantity of this adder")


With Sheets("Adders")
.Range("D5").Value = strMyIPBMsg
End With
End Sub

Sub Array_Floating_Panel()
Call QuantityCommand
Worksheets("Adders").Range("D7").Value = Quantity
End Sub
Sub Array_Hazardous_Metal_Roof()
Call QuantityCommand
Worksheets("Adders").Range("D8").Value = Quantity
End Sub

 

[StephenCrump]

Welcome to the Forum!

I am not clear what it is you are trying to do ...

On the face of it, you are calling any number of different Subs, but if the coding is virtually identical, e.g.

Sub Array_Floating_Panel()
    Call QuantityCommand
    Worksheets("Adders").Range("D7").Value = Quantity
End Sub
Sub Array_Hazardous_Metal_Roof()
    Call QuantityCommand
    Worksheets("Adders").Range("D8").Value = Quantity
End Sub

then we can simplify these into just a few lines of code.

Some other questions:

- How will the spreadsheet be used? Will the user type a new value in E268:E277 and press a button to update?

- Why do you need an input box to specify quantity, rather than having the user type the quantity directly into Excel?

- What's special about the range E268:E277? Do you need to check the value of each of these cells? What if the value is different across this range?

- Why are results pasted in D5, D7 and D8. Is this perhaps because A5, B5 or C5 is "Array_Additional Array", A7,B7 or C7 is "Array_Floating Panel" etc?

It will help if you can you provide some screenshots to show what your spreadsheet looks like, and what you want to happen.

Post #2 here shows you one way to attach these: http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

 

[aeddy2008]

I was going to upload a screen shot but the html download is having technical difficulties according to the website.

The coding is virtually identical per sub routine we are only changing the destination of the input results.

So we are creating a estimating sheet with the first few sheets having user interface, and the other sheets locked; protected from the user. These lock sheets include all the adders with cost.

Info on the adders from the adders sheet

There are some 55 adders the user can select from the dropdown list linked to the adder sheet. But per estimated project we only use around 6 of the adders. So we have alloted E266-E277 for adder selection. We want to check the values in each of these cells to vlookup the correct price. The variable that we want the user to enter is the quantity, but as we are using vlookup so i need the input from the user to be extracted and placed into the locked adders sheet using an input box to calculate the total adder. The total adder is then extracted using vlookup with the corresponding adder back to G267-277.

The result from the input box are passed to D5, D6, D7.....ect to the quantity column. From there we calculate total adder and link the total back to the user interface page.

Column D
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Array Additional Adder[/TD]
[TD]ea[/TD]
[TD]$0.00[/TD]
[TD]quantity[/TD]
[TD]total adder[/TD]
[/TR]
[TR]
[TD]Array Floating Panel[/TD]
[TD]ea[/TD]
[TD]$1.00[/TD]
[TD]2[/TD]
[TD]$2.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Welcome to the Forum!

I am not clear what it is you are trying to do ...

On the face of it, you are calling any number of different Subs, but if the coding is virtually identical, e.g.

Sub Array_Floating_Panel()
    Call QuantityCommand
    Worksheets("Adders").Range("D7").Value = Quantity
End Sub
Sub Array_Hazardous_Metal_Roof()
    Call QuantityCommand
    Worksheets("Adders").Range("D8").Value = Quantity
End Sub

then we can simplify these into just a few lines of code.

Some other questions:

- How will the spreadsheet be used? Will the user type a new value in E268:E277 and press a button to update?

- Why do you need an input box to specify quantity, rather than having the user type the quantity directly into Excel?

- What's special about the range E268:E277? Do you need to check the value of each of these cells? What if the value is different across this range?

- Why are results pasted in D5, D7 and D8. Is this perhaps because A5, B5 or C5 is "Array_Additional Array", A7,B7 or C7 is "Array_Floating Panel" etc?

It will help if you can you provide some screenshots to show what your spreadsheet looks like, and what you want to happen.

Post #2 here shows you one way to attach these: http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

 

[aeddy2008]

E267-277 looks like this
Column E
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Adder to be selected from dropdown(Validated List from Adder Sheet)[/TD]
[TD]Price per unit[/TD]
[TD]quantity[/TD]
[TD]total adder cost[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[aeddy2008]

E267-277 looks like this


Column E Column F
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Adder to be selected from Dropdown(Validated List from Adder Sheet)[/TD]
[TD]each[/TD]
[TD]quantity[/TD]
[TD]total price[/TD]
[/TR]
[TR]
[TD]array additional array[/TD]
[TD]each[/TD]
[TD]4[/TD]
[TD]40.00[/TD]
[/TR]
[TR]
[TD]array floating panel[/TD]
[TD]each[/TD]
[TD]4[/TD]
[TD]8.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

E267-277 looks like this


Column E Column F
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Adder to be selected from Dropdown(Validated List from Adder Sheet)[/TD]
[TD]each[/TD]
[TD]quantity[/TD]
[TD]total price[/TD]
[/TR]
[TR]
[TD]array additional array[/TD]
[TD]each[/TD]
[TD]4[/TD]
[TD]40.00[/TD]
[/TR]
[TR]
[TD]array floating panel[/TD]
[TD]each[/TD]
[TD]4[/TD]
[TD]8.00[/TD]
[/TR]
</tbody>[/TABLE]

So if I understand you correctly, in this example:

1. The user has:

- Selected "array additional array" from the drop down list, and entered quantity 4 directly into the cell, and
- Selected "array floating panel" from the drop down list, and entered quantity 4 directly into the cell.

2. You need the VBA to:

- Enter 4 in the "array additional array" worksheet, and pick up the resulting unit price, and
- Enter 4 in the "array floating panel" worksheet, and pick up the resulting unit price,

and use these unit prices to calculate the price for 4 units of each.

Are the sheets in a standard format? For example, will Quantity always go into cell A1, say, and will the unit price always be available in A2, say.

I am also still not clear why you need input boxes?

And also, the summary results in rows 5,6,7 etc are virtually identical to those in rows 267-277. Why don't you simply have drop downs in these higher rows?