VBA to auto complete sheet based on drop down selection

ewoeckel

New Member
Joined
Mar 7, 2013
Messages
20
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,026
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
20
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
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,167
Members
410,775
Latest member
alal1030
Top