keep formula after typing in a value in a excel cell

Hi there,

my problem is, that i need something like:

there is a function in a excel-cell (for example '=setvalue("sqlserver",
"DB", "table")').
now i type in a value (for example 100) then the function "setvalue" must
write this value
(100) in the server, db and table from the parameters. after leaving the
cell the formula
is still the same and only the value 100 ist visible for the user. and if i
go back to the
cell than i can see the function in the menubar.

there is a product from applix (TM1) and they did it. the problem is, that
the souce code
is protected ;)

Is there anyone who has an idea or know how to handle it !?

thanks for your help!
Henning


0
hvoitz (3)
2/23/2004 10:58:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
711 Views

Similar Articles

[PageSpeed] 49

Henning
    You would have to use VBA for that.  Also, the formula that was in that
cell will not do anything with the value you entered because the formula is
no longer there.  What you would need is a macro that is triggered when that
cell changes.  This macro would do with the value entered whatever you want
done, then reinstate the formula.  Is this what you mean?
    Post back and include the code that does whatever with the "100".   HTH
Otto
"Henning Voitz" <hvoitz@gmx.de> wrote in message
news:OO%23Jmvf%23DHA.2656@TK2MSFTNGP11.phx.gbl...
> Hi there,
>
> my problem is, that i need something like:
>
> there is a function in a excel-cell (for example '=setvalue("sqlserver",
> "DB", "table")').
> now i type in a value (for example 100) then the function "setvalue" must
> write this value
> (100) in the server, db and table from the parameters. after leaving the
> cell the formula
> is still the same and only the value 100 ist visible for the user. and if
i
> go back to the
> cell than i can see the function in the menubar.
>
> there is a product from applix (TM1) and they did it. the problem is, that
> the souce code
> is protected ;)
>
> Is there anyone who has an idea or know how to handle it !?
>
> thanks for your help!
> Henning
>
>


0
2/23/2004 10:22:11 PM
Hi Otto and thank you for you answer!

YES!!! exacly this is what I need :)) well, i have a excel-add-in and the
function
setvalue is in the add-in.

if i start only the function with the parameters it works! what i need is
now to catch
this event "cell change" or what ever to put the formula back.

first i though i can use the "Workbook_SheetChange" from the add-in but it
didn�t work (maybe i made a mistake)
'#################################################################
'that was a test, if i can get the value from the actie workbook. i put it
in the workbook
'from the add-in cause than i can use it in every excel files without puting
the code in ;)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "this here " & Target.Value & " i typed in ;)"
End Sub
'#################################################################
but it doesn�t work in the add-in only in the active workbook :(

my next problem is to put the formula back to the cell! i could do it with
application.undo :)
but than the value i typed in is gone :)

i will give you the code this evening if its ok?!

thanks a lot for your support otto
henning



"Otto Moehrbach" <ottomocobia97@bellsouth.net> schrieb im Newsbeitrag
news:uNdiBul%23DHA.3184@TK2MSFTNGP09.phx.gbl...
> Henning
>     You would have to use VBA for that.  Also, the formula that was in
that
> cell will not do anything with the value you entered because the formula
is
> no longer there.  What you would need is a macro that is triggered when
that
> cell changes.  This macro would do with the value entered whatever you
want
> done, then reinstate the formula.  Is this what you mean?
>     Post back and include the code that does whatever with the "100".
HTH
> Otto
> "Henning Voitz" <hvoitz@gmx.de> wrote in message
> news:OO%23Jmvf%23DHA.2656@TK2MSFTNGP11.phx.gbl...
> > Hi there,
> >
> > my problem is, that i need something like:
> >
> > there is a function in a excel-cell (for example '=setvalue("sqlserver",
> > "DB", "table")').
> > now i type in a value (for example 100) then the function "setvalue"
must
> > write this value
> > (100) in the server, db and table from the parameters. after leaving the
> > cell the formula
> > is still the same and only the value 100 ist visible for the user. and
if
> i
> > go back to the
> > cell than i can see the function in the menubar.
> >
> > there is a product from applix (TM1) and they did it. the problem is,
that
> > the souce code
> > is protected ;)
> >
> > Is there anyone who has an idea or know how to handle it !?
> >
> > thanks for your help!
> > Henning
> >
> >
>
>


0
hvoitz (3)
2/25/2004 7:21:54 AM
Henning
    As I understand what you want:

You have a formula/function in that cell.

What cell?  I need the address.  Not the sheet name, just the cell address,
as in F5.

I need exactly what you type into that cell when you type in the
formula/function.  Exactly!

Then you want to type something else into that cell and you want Excel to do
something with that entry.  What do you want Excel to do with that entry?
Exactly?

Then you want Excel to re-enter the original formula/function into that
cell.

Is all the above correct?

I think it would be quicker for you  contact me direct.  Just email me your
responses to the above questions.  Remove "cobia97" from my email address.
Otto

"Henning Voitz" <hvoitz@gmx.de> wrote in message
news:%23SVkzA3%23DHA.3220@TK2MSFTNGP10.phx.gbl...
> Hi Otto and thank you for you answer!
>
> YES!!! exacly this is what I need :)) well, i have a excel-add-in and the
> function
> setvalue is in the add-in.
>
> if i start only the function with the parameters it works! what i need is
> now to catch
> this event "cell change" or what ever to put the formula back.
>
> first i though i can use the "Workbook_SheetChange" from the add-in but it
> didn�t work (maybe i made a mistake)
> '#################################################################
> 'that was a test, if i can get the value from the actie workbook. i put it
> in the workbook
> 'from the add-in cause than i can use it in every excel files without
puting
> the code in ;)
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
>     MsgBox "this here " & Target.Value & " i typed in ;)"
> End Sub
> '#################################################################
> but it doesn�t work in the add-in only in the active workbook :(
>
> my next problem is to put the formula back to the cell! i could do it with
> application.undo :)
> but than the value i typed in is gone :)
>
> i will give you the code this evening if its ok?!
>
> thanks a lot for your support otto
> henning
>
>
>
> "Otto Moehrbach" <ottomocobia97@bellsouth.net> schrieb im Newsbeitrag
> news:uNdiBul%23DHA.3184@TK2MSFTNGP09.phx.gbl...
> > Henning
> >     You would have to use VBA for that.  Also, the formula that was in
> that
> > cell will not do anything with the value you entered because the formula
> is
> > no longer there.  What you would need is a macro that is triggered when
> that
> > cell changes.  This macro would do with the value entered whatever you
> want
> > done, then reinstate the formula.  Is this what you mean?
> >     Post back and include the code that does whatever with the "100".
> HTH
> > Otto
> > "Henning Voitz" <hvoitz@gmx.de> wrote in message
> > news:OO%23Jmvf%23DHA.2656@TK2MSFTNGP11.phx.gbl...
> > > Hi there,
> > >
> > > my problem is, that i need something like:
> > >
> > > there is a function in a excel-cell (for example
'=setvalue("sqlserver",
> > > "DB", "table")').
> > > now i type in a value (for example 100) then the function "setvalue"
> must
> > > write this value
> > > (100) in the server, db and table from the parameters. after leaving
the
> > > cell the formula
> > > is still the same and only the value 100 ist visible for the user. and
> if
> > i
> > > go back to the
> > > cell than i can see the function in the menubar.
> > >
> > > there is a product from applix (TM1) and they did it. the problem is,
> that
> > > the souce code
> > > is protected ;)
> > >
> > > Is there anyone who has an idea or know how to handle it !?
> > >
> > > thanks for your help!
> > > Henning
> > >
> > >
> >
> >
>
>


0
2/25/2004 1:31:03 PM
Reply:

Similar Artilces:

Outlook keeps prompting for logon..
User is on Outlook 2k3, latest service packs, updates, etc. Mailbox lives on Exchange 2007 (SP1). Everytime the user starts Outlook they're prompted to logon. I've disabled the logon prompt at startup option (and enabled it and then disabled it again), but it's still happening. I've tried it with cached mode on and cached mode off. I've tried it with offline mode enabled and disabled. I've tried deleting his profile and recreating it. I've tried switching to an RPC/HTTPS connection. Finally, i completely uninstalled Outlook 2k3 and installed Outlook 2k7. Not...

Excel 97 not saving
I have a user on WinXP / Office 97. User states that sporatically, she is unable to save an Excel file on either her hard drive or the network drive. These files have macros in them, and up until she was upgraded to XP and began using macros, she had no problems saving. The only error being reported is "Document could not be saved." In her latest email to me she writes: Please note that the problem is intermittent and I do not know what is triggering the problem other than it has only happened since I started using XP and files with Macros. I have seen documentation that a...

Formula to Value
Hi there I have just updated to from 2000 to 2003, and cannot find a toolbar button for formula to value. I can copy and paste special in the same cell, but this is mildly irritating. Am I being dumb? TGHCOGO --- Message posted from http://www.ExcelForum.com/ I don't think it has ever been there without help at least. Do as follows: click view>toolbars>customize, select commands, select edit in the left pane and scroll down until you see something that looks like an old time almanac date that you would tear off every day |12| it has a 12 in it, paste values is the name, now s...

Duplicate records in Excel
How do I highliht duplicate records in Excel? I created a data base in Excel, and discovered that there are duplicate tag numbers from the old paper work I'm pulling data off of. So I'm looking for a way to get Excel to find those duplicate records and highlight the rows they are in. Sheidsa, have a look here http://cpearson.com/excel/duplicat.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Sheidsa" <She...

Excel Type Rounding
Hello, Am using A2k. Want to round numbers to thousands. With Excel I use Round(number, -3) for this. However, dont know how to do it with Access standard Round() function. The same syntax produces an error with Access. Pls Help For example, I want 125,232 to be displayed as 125,000 and 125,832 to be displayed as 126,000 TIA Anand Anand - Try this: Round([AmountToRound]/1000,0)*1000 -- Daryl S "Anand" wrote: > Hello, > Am using A2k. Want to round numbers to thousands. With Excel I use > Round(number, -3) for this. However, dont know ho...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Excel File Opening Via Hyperlink
What do I need to do within an Excel document to have it open in print preview mode when the Intranet link to this file is clicked? Thank you. Put this one line Macro in the ThisWorkbook code area: Private Sub Workbook_Open() ActiveWindow.SelectedSheets.PrintPreview End Sub -- Gary''s Student - gsnu200723 ...

Help Old addresses keep coming up
When I go to send an email to some people their old email addresses keep popping up together with their current ones. I do not have them listed in contacts or in an address book. It is a minor annoyance. I am using Outlook 2003. It is a minor annoyance. How do I delete these old addresses? Where they show up is where I am entering the e-mail address on the item being e-mailed. Thanks You did not specify how you were entering the addresses. Presumably you are using autocompletion and they are...

Cell color change referencing the date in designated cell
HI, How can I have fill color of cell change according to due date. In example, red overdue, green - on time, black complete. Thanks, K Use Conditional Formatting. You'll need to determine what is Overdue, On-time and Complete Read up on the help and come back for specific help Regards Trevor "Klonja" <plunti@yahoo.com> wrote in message news:a18370dd.0401251510.2f990e0f@posting.google.com... > HI, > > How can I have fill color of cell change according to due date. In > example, red overdue, green - on time, black complete. > > Thanks, K Hi Cl...

Use a cell's value as row number for another cell in Excel
Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the reference for the row value for a different series of cells (D2:F4). For example: Column A B C D E F Row 1 1000 30 170 1 2 3 2 1500 60 190 3 1200 20 175 I want to get the following cells to equal: D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1); E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2); F2=1200 (In other words: F2 = A3); F3=20 ...

Formula error since 10.5.6 update ...
Hi, I have been working with a spreadsheet for years without any problem of this sort. Since my recent 10.5.6 upgrade a part of a formula does not return a correct result. My problem seems to be related to the incorrect result of this part of my formula: TEXTE($N$5;"dd-mm-yy"). In this example, N5 is equal to "31-10-09". This formula should return : 31-10-09 It returns "dd-10- yy". Funny, the "31" is replaced by "dd" and "09" by "yy", while the month number is correct. Any idea with this is all about ? ...

Access 2 Excel
I exported an annual worklist from an Access table to Excel. There is column for tasks, one for frequency (TD, D, TW, W, TM, M, Q, SA, A twice daily, daily, twice monthly, etc) and a column for each month. The month columns have the same coded data as the frequency colum depending on when the task is done. The problem is that I tried to make a pivot table to count how man tasks (by frequency type) are done each month. Excel is counting th empty cells as if they had data. Search and replace doesn't work. even tried replacing the blank data in Access with ~ and re-exportin it. Ex...

how to enter a value, and have it SUMMED with the existing cell co
An Excel cell contains, say= 4. How do you enter, say=2, and have the result = 6? (not COPYING and Paste As ...) ...

Confirmation of receipt keeps trying to send and fails
Help! I received an email message that requested a confirmation that it had been received. I told it to send confirmation, but it failed when attemtping to send it and produced an error. I tried deleting the email message and everything in the draft items and Outbox, but every time I switch on Outlook the darn thing tries to send this email confirmation. It seems there is nothing I can do to stop it, even though I cannot see it! I have tried uninstalling Office 2003 and reinstalling it, but all of my settings are remembered as are all the old emails. I just cannot seem to get rid of it! ...

Returning a lookup value from multiple sources
I have 4 worksheets - WIP, Orders, Invoicing, Costs. In the WIP sheet, I summarise data from Invoicing & Costs by Orde number. Some of the Orders will be invoiced, some have costs, som both. Some order numbers will appear more than once in each sheet, som not in either. Summarising the data is easy, once I have each order using SUMIF, bu how do I get the unique order numbers from the other sheets into th WIP sheet in the first place?? This is driving me mad!! -- Message posted from http://www.ExcelForum.com Hi I assume that your 'Orders' sheet should store all order numbers....

Excel VBA Project Has Protection?
I'm trying to find a way to programmatically check if a workbooks VBA project has protection. I know ActiveWorkbook.VBProject.Protection will tell you if it is currently locked but that doesn't help if the project has already been unprotected ie by a user (or another bit of code) has unprotected the project. So what i am trying to do is if a VBA project doesn't have any protection, my code will ask the user for a password and then set the password by using sendkeys. If they already have the VBA project protected, it will ask them for their password, then unlock the pr...

Formula Help 2 QUESTIONS
I am trying to do the following: QUESTION 1: I have a spreadsheet that contains hundreds of records and most if not all of them are duplicated. I have tried an advanced auto filter but it doesn't seem to be working correctly. Is there a formula I can use to only pull one record into a new spreadsheet ----------------------------------------------------------------------------------------------- QUESTION 2 I am trying to compare information in spreadsheet ONE to info in spreadsheet TWO and return data into spreadsheet THREE. All three spreadsheet are saved separetly, how would I wri...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

copying cells #2
In cell A1 sheet1 I have a number which lets say represents a code for a specific shape that i have allready drawn in cell B1 in sheet2.Can you please tell me what kind of instruction i can give to cell B1,sheet1,so that i can get that shape from cell B1 sheet2 into B1 sheet1? How about putting those shapes on Sheet1 and hide them. Then you could have a macro unhide the one you want. J.E. McGimpsey shares this in a macro at his site: http://www.mcgimpsey.com/excel/lookuppics.html If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcrit...

Regression Leverage Formula (Jerry W. Lewis or Mike Middleton) already have DFITS formula
I have been trying to figure out the leverage formula for a set of X Y values X Y 75 16 83 20 85 25 85 27 92 32 97 48 99 48 Using the data analysis add-in and adding residuals then running regression gives me Predicted Y, Residuals and Standard Residuals, If my data was in A2:B8 (X and Y) and C2 housed this formula =SLOPE($B $2:$B$8,$A$2:$A$8)*A2+INTERCEPT($B$2:$B$8,$A$2:$A$8) which gives me my predicted y and D2 housed B2-C2 which gives me my residuals then E2 housed =D2*D2 which gives me my residuals squared. What I am looking for in cell F2 is HI1(the weight...

Why is the bool type 4 bytes it's a wast of memory..
Hi! Is it anyone that might have a good explanation why the designor of .NET made a bool 4 bytes. I mean it's just a wast of memory. //Tony Tony Johansson wrote: > Hi! > > Is it anyone that might have a good explanation why the designor of .NET > made a bool 4 bytes. > I mean it's just a wast of memory. For a variety of reasons. I would guess that the most important is that there's no compelling reason for it _not_ to be 4 bytes, and making it 4 bytes keeps it consistent with other data types. While 4 bytes obviously takes up more ro...

How do i set default column type as text in excel?
I am opening a text file using Excel.The text file has data 16E102,which appears as 1.60E+103 in the excel sheet since it takes the data type as scientific by default. However i want it to appear as 16E102 itself.What can be done for this? Changing the cell format to text does not help,because it keeps the data already present in it as it is,it will help only if new data is entered in the similar manner. Is there any way to set the default column type as text? Using Windows Explorer, rename the text file so that it has a 'txt' extension. For example, change 'MyFile.csv'...

How to do this kind of chart in excel?
I wonder if this is possible. I have two gaussians represented by points and joined by a smooth line in excel. however, ideally I want the area under the curves to be shaded, and best of all, if possible, be semi transparent so that the overlap between the two curves is highlighted a bit darker than the area under the individual curves. x values are wavelength, y values are intensity is this possible? t.i.a Lee - Excel doesn't do transparent shading of area charts; otherwise that would be the ideal approach. You can get semitransparent column charts using an autoshape as an area...

opening Excel and then closing it
I have a file that is passed to me in XLS format, but I would much prefer to work with it in CSV. So I did this... ' open the downloaded file in Excel and copy it over to L in csv format Workbooks.Open "O:\Downloads\D00100001554_0.xls" Workbooks("D00100001554_0.xls").ConflictResolution = xlLocalSessionChanges Workbooks("D00100001554_0.xls").SaveAs "L:\Activity.csv", xlCSV Workbooks("Activity.csv").Close False This works fine except it leaves Excel open in the background. The only thing to do is fine it in Processes a...

update subform from value in another form
I have a mainform and two subforms liked through member number. I want to enter data in the subform for every quarter for each member. I have an unbound combo box in the main form to select the quarter. How do I store the quarter information for each member in the subforms based on the combo box. I don’t want to store the quarter information in the main form. SeekAns - It sounds like you are adding new records in the subform. If so, set the default value of the control in the subform to be the value selected in the combo box on the main form. -- Daryl S "See...