keep formula after typing in a value in a excel cell

  • Thread starter Thread starter Henning Voitz
  • Start date Start date
H

Henning Voitz

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
 
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
 
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
 
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
 
Back
Top