Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input in cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input in cell
Hi
you'll need an event procedure (the worksheet_change event). See: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany Sean wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input in cell
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 -- crispb ----------------------------------------------------------------------- crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088 View this thread: http://www.excelforum.com/showthread.php?threadid=27366 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User input in cell
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 -- Regards, Tom Ogilvy "Sean" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to have User input data for cell | Excel Discussion (Misc queries) | |||
refilling data in same cell after user input in another cell | Excel Discussion (Misc queries) | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Changing Cell Contents Based Upon User Input | Excel Programming |