Can I request user in input variable value within formula?

[R Nelson]

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

 

[Gary''s Student]

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub

 

[R Nelson]

Thanks, that is exactly what I was looking for!!!

 

[R Nelson]

Okay, that didn't work like I first thought. The sub works great, but let me
better explain what I would like to do..

I have a dollar value in A1, and I would like to be able to let the user
spread that value throughout cells B1:H1 (across the row) by percentage.

So it would look like this if the user input 25% in each cell.

A1 B1 C1 D1 E1
100,000 25000 25000 25000 25000

Also, I am doing this same activity with values all down column A (with
different spread values throughout.

And...I need to be able to have the macro run when the user clicks on each
cell.

Is this possible?

 

[Gary''s Student]

If the user inputs 100% we will only fill B1.
If the user inputs 50% we will fill B1 & C1.
If the user inputs 33% we will fill B1 & C1 & D1.

What if the user inputs 60% ???

 

[R Nelson]

That is exactly my problem. B1:E1 are dates, so we are spreading that value
across dates, but not necessary all of them. It could be 60% in C1, 40% in E1
and 0% in B1 and D1.

Basically I'm doing a cash flow spread, which I typically have to do
manually for payment terms of each pay item, but if I can get this idea
working, then it expedites the cost spread process and allows other users to
adjust easily in the future.

 

[Gary''s Student]

Now I get it!

Check back tomorrow....We will let the user double-click cols B thru E (or B
thru anything). The user will supply the % and the cell will receive that
percentage of the col A value. For example, if the user is on row 13 and
double-clicks D13, they will input a percentage ( say 13.538%) and D13 will
get the formula:
=.13538*A1


REMEMBER:
You STILL have to be concerned with the user picking a set of percentages
that do not add up to 100%

 

[R Nelson]

Awesome!

Yes, if I can build in the ability to spread these values across the row (A1
to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to
100% of the value, I'll just have a sum column in there as a check. If the
user in question doesn't know to distribute the full 100% then he has bigger
problems than the spreadsheet!

Thanks so much for this help.

 

[Gary''s Student]

Here is the macro. Double-click anywhere from B1 thru Z100 and the macro
runs. It asks for a percentage, so have the user enter stuff like 45%.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set r = Range("B1:Z100")
If Intersect(Target, r) Is Nothing Then Exit Sub
Cancel = True
rr = Target.Row
x = Application.InputBox(prompt:="Enter a percentage (like 15%)", Type:=2)
Target.Formula = "=A" & rr & "*" & x
End Sub



Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window


To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[R Nelson]

Well, I plugged it in to a tester worksheet as a practice run and I cannot
get it to work. Code is pasted into worksheet as a worksheet event (thanks
for the link to worksheet events tutorial by the way) but no action on double
click. I'll keep working with it to see if I can figure it out, but I don't
know what the problem could be.

 

[R Nelson]

I got it working. Simply an error on my part in transposing to the worksheet.
Thanks again very much!!!

 

[Gary''s Student]

Update this post thread if you need any moe help.