User input in cell

[Guest]

Hi,
How do formulate or set up a cell to take the users info that they enter in
that cell and immediately calculate an answer based on their entry and then
show the answer in that exact cell?
eg. a cell is designated as a cost of an item, but what we want to show in
that cell isnt what they enter, but rather 2 X the cost +.99. is there any
way of doing this without having to use another cell?

 

[Frank Kabel]

Hi
you'll need an event procedure (the worksheet_change event). See:
http://www.cpearson.com/excel/events.htm

 

[crispbd]

This is a method that works more reliably than using the Workshee
Change event. This will allow you to enter a value in a cell, the
double click the cell for the recalculation.


-in the VB editor select the sheet you are using for costing
-from the view menu, select 'Code'
-select 'worksheet' from the dropdown menu, and in the right drop dow
menu
select the 'before double click' event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cance
As
Boolean)

' This is the code that will allow you to double click on the cell fo
a recalculation
' multiplying by 2 and adding 0.99

-*ActiveCell.Value = ActiveCell.Value * 2 + 0.99*-


End Su

 

[Tom Ogilvy]

crispbd showed you how to enter a value, then return to the cell and double
click on it to convert the entry to a calculated entry and leaves you in
edit mode. If that is what you want, your set.

I would interpret your request differently. I understood you to say that if
I entered a value, such as 100, then when I hit enter, that entry would be
changed to =2*100+.99

If so, you would follow crispbd's instructions but instead of the
beforedoubleclick event, you would select the Change event. The code would
look like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Not IsNumeric(Target) Then Exit Sub
If Target.HasFormula Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = "=2*" & Target.Value & "+0.99"
ErrHandler:
Application.EnableEvents = True
End Sub


If you only want this to occur for a specific column, you could add a line
like

' Only behave this way for column C
if Target.Column <> 3 then exit sub