0

I would like help from someone as I believe this problem requires VB. I basically have a CELL(A4) where people will be entering values to find out the corresponding calculations displayed below.

However I want to add an additional function where popular values are automatically entered into that same cell(A4).

Cells E7 and E8 will receive input and multiply it by 960(E7) or 765(E8) and transfer that value to cell(A4) so the rest of the formulas update properly.

Basically have the user input the bags directly in A4 or enter the number of containers in E7 or E8.

**This is where i have the problem because when I enter a formula on A4 it gets deleted once you enter a value directly on it.

**Also note that the result from E7 and E8 should be displayed (in A4) as I need it as well.

My sheet:

enter image description here

3
  • No, cells can only have one value at a time. If you want input, you could prompt the user for it, but I'm not quite understanding what you want to do. Sep 18, 2014 at 18:56
  • Why does it need to be A4? Why not have two rows for "Bags"; one input, and one output? Sep 18, 2014 at 19:09
  • I don't want to add additional cells or rows to a standard already being used. I want people to just enter a 2 in E8 and have A4 display 1530 and the calculations based on that. what is being done right now is they are using the calculator to multiply it there then enter it manually in A4. I wanted to replace that by adding the E7 & E8 function. It has to be easier and faster than just doing a calculator, otherwise it won't be implemented.
    – Josh
    Sep 18, 2014 at 20:03

4 Answers 4

4

You can use a worksheet change function in vba. Something like

Private Sub Worksheet_Change(ByVal target As Range)

If Intersect(target, Range("e7:e8")) Is Nothing Then Exit sub

Application.enableevents = False
ActiveSheet.range("A4").formula="=if(e7="""",e8*765,e7*960)"
Application.enableevents = True

End Sub

Anytime the sheet changes it checks if the cell changed was e7 or e8. If it was it puts the formula

=if(e7="",e8*765,e7*960)

in cell A4. If you then put a value in cell A4 it will stay until you again change e7 or e8. You may need to adjust to get it to do exactly what you want but the idea is there.

This would go in the worksheet object under microsoft excel objects in vba.

EDIT:

To fit in with comments.

New code, this one will only do something if the cell has a value added or changed, it won't run if you delete a value.

Then it checks if e7 was edited. If it was e8 is deleted. If it wasn't we know e8 was edited so e7 gets deleted. Formula stays the same.

Private Sub Worksheet_Change(ByVal target As Range)

    If Intersect(target, Range("e7:e8")) Is Nothing Or target.Value = "" Then Exit Sub
        Application.EnableEvents = False

        ActiveSheet.Range("A4").Formula = "=if(e7="""",e8*765,e7*960)"

        If Intersect(target, Range("e7")) Is Nothing Then
           ActiveSheet.Range("e7").ClearContents
        Else
            ActiveSheet.Range("e8").ClearContents
        End If

    Application.EnableEvents = True

End Sub
7
  • .............Very nice! Sep 18, 2014 at 21:36
  • GT webb thank you, I will try it, unfortunately I dont understand your last line. "This would go in the worksheet object under microsoft excel objects in vba." I go in Developer tab and click on View code, then a new window opens and I enter the code there? i57.tinypic.com/345k9rb.png
    – Josh
    Sep 19, 2014 at 19:45
  • Yes that's the correct place. Depending on what the macro is used for there are different places that it can be stored. Also note the 4 quotes back to back in the middle of the formula. Because a quote is taken as the end of a text in vba you need to have 4 there to print 2 to the cell. Your image only has double quotes. Also your code will only fire if e8 is changed (which could be data entered or deleted).
    – gtwebb
    Sep 19, 2014 at 20:00
  • Yeah I was just messing around with some of the values, that is great stuff man ;p However the calculation works great as long as either of the 2 cells is blank, if I type a 2 on e7 but then type a 1 on e8 nothing will work, I know they can delete them manually but is there a way to circunvent that? Or have the cell deleted when you enter the value in the other one? so if I enter 2 on E7 then E8 will clear up and then your amazing script will work Automatically.
    – Josh
    Sep 19, 2014 at 20:24
  • I've added additional code to make sure the edit isn't deleting a cell and to do delete the other cell if a value is added. If the answer solves your problem could I ask you to accept the answer so the question will be marked as solved. Thanks.
    – gtwebb
    Sep 19, 2014 at 20:48
1

In business, it is common to want to use a formula to calculate a value, but still allow the user to override that formula for special cases.

Say A4 represent Total Cost and contains:

=D8*G8

where D8 is the unit cost and G8 is the quantity.

But for special customers we want to be able to quote a discounted cost. We put the discounted cost in H8 and modify the formula thusly:

=IF(H8<>"",H8,F8*G8)

So if we want the override, it goes in H8; otherwise we leave H8 blank.

3
  • Something like that would happen behind the scenes no? I also don't want to have additional input cells tho.
    – Josh
    Sep 18, 2014 at 19:54
  • @Josh Although having additional cells seems a little messy, it is commonly done. People are afraid of ruining the formulas; if fact, some people protect formula cells from being edited. Sep 18, 2014 at 19:57
  • Yeah but if it doesn't look simple enough and without many alterations. People won't change what they have been doing for years which is multipliying in a calculator and then entering the result on A4 :(
    – Josh
    Sep 18, 2014 at 20:07
0

You can use VB to change any value during calculation stage.

But doing so means that the macro will change the value in A4, and you'll have to add something to know that the value should not be updated on a second go.

So in theory it is possible, but it is highly impractical to do so.

If you purely want to have a dropdown list with popular values, you do not need to go fancy VB. Excel has this function built in. You can do this through data validation.

5
  • Drop down won't work and it's too cumbersome, values in E7 or E8 could range from 1-1000. Also I know it's kinda fancy but I dont think is impractical at all; in fact my goal is for it to be practical and intuitive since rest of the people using it aren't computer proficient.
    – Josh
    Sep 18, 2014 at 19:55
  • The impractical part is to creating this, and making it work well for the user.
    – LPChip
    Sep 18, 2014 at 20:03
  • I just wanted them to enter a value on my new fields E7 or E8 and have the rest calculated, trust me I tried other options but I'm not a programmer god do I have turned to the community for help ;)
    – Josh
    Sep 18, 2014 at 20:06
  • Is it possible to make A4 not a field the user would change? Perhaps place that at E6, and make A4 have the formula you want it to have, taking note of the value in E6. As pointed out by others, that IS the way people do it, because that is the only way it is maintainable. VB is going to make it really hard for you if you haven't touched it before. Its not a few lines of code and it works, so that basically rules out the usage of VB.
    – LPChip
    Sep 18, 2014 at 20:08
  • Also, if it is your job for people to play with the number in A4 to find the value by comparing all outputs, you're doing it wrong. Excel is doing the opposite. You specify the target, and it calculates the input.
    – LPChip
    Sep 18, 2014 at 20:11
0

The solution i use where you have only a few cells that i want both calculations and the abillity to enter values to override the formula or delete what they entered and have the formula work is.

Setup your worksheet as you want.. Then for the cell you want to override setup an identical cell in a column off screen, ie column az and cut and paste your ell there In this special enter, alter your formula but with an if to test if a value has been keyed in the orginal cell.

Then copy this cell , select your orginal cell and paste special a linked picture.

Now select the picture and add a hyperlink to the cell directly below it, use a ranbe name if you think it may move due to rows being inserted.

Now you should find the cell looks like it is displaying a value from the formula, when you click the cell the hyperlink jumps to the cell underneath enter the value and the display changes, delete it and the formula value displays.

Also consider adding a conditional format on the formula cell that changes if there is a value in the displayed cell, this helps remind you a manual value has been entered.

Hope that helps setup forms that look simple and perform without macros.

Terry

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .