VBA to auto complete sheet based on drop down selection

ewoeckel

New Member
Joined
Mar 7, 2013
Messages
22
I am trying to make a pricing spreadsheet which deals with certain head count, hours, complexity multipliers, etc. In an ideal world, there are 5 "stock" offers which these never change, and then a custom offer which I need the ability to manually choose each variable. I want to use VBA to force cells to a predetermined selection based on a user choice initially, but then allow the user to override the default selection if they need to add more headcount or hours or whatever. So load a predetermined value, then allow an "override".

So I am trying to start the coding aspects, but as you can tell, I am pretty bad at coding as I cannot create a basic IF, ElseIF, Else statement without errors. Ideally the code would look something similar to what I posted below. Where I am getting stuck, is how can I force a cell to a value, then allow the user to modify it without completely breaking the sheet's VBA?? I am needing some super awesome excel VBA people to help me. Thanks in advance!

VBA Code:
If ([J3] = "") Then
{
                                        '// Custom project, no default values
}ElseIf ([J3] = "Design") Then
{
    [E13] = "Design Review Level 1" 
}ElseIf ([J3])= "Engineering") Then
{
    [E3] = "Engineering Review Level 1"
}End If

End Sub


1595263963839.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,103
You have a previous thread that is using the correct code, now this thread you are using something different than VBA

 

ewoeckel

New Member
Joined
Mar 7, 2013
Messages
22
You have a previous thread that is using the correct code, now this thread you are using something different than VBA



Ill see if it can post it together! Will update shortly.
 

ewoeckel

New Member
Joined
Mar 7, 2013
Messages
22
You have a previous thread that is using the correct code, now this thread you are using something different than VBA


Got it working using a different method! Used the following which requires the user to physically push a button to force the project:

Code:
Sub MainCall()
Select Case (Range("J3").Value)
Case "Feasibility Study"
    Call DefaultProj1
Case "Custom"
    Call CustomProj1
End Select
End Sub


Sub DefaultProj1()

[B13] = "2"
[B14] = "16"
[B15] = "4"
[B16] = "2"
[B17] = "2"
[B18] = "1"
[B19] = "0"
[B20] = "10"
[B21] = "12"
[B22] = "8"
[C13] = "0.5"
[C14] = "1"
[C15] = "1"
[C16] = "2"
[C17] = "1"
[C18] = "1"
[C19] = "1"
[C20] = "1"
[C21] = "1"
[C22] = "1"

End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,116
Messages
5,835,476
Members
430,358
Latest member
zzc1128

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
Top