Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter a value in a cell
I've tried this macro and it works:
Sub temp() Range("Comm!f7").Value = 0 End Sub But what I need is a function. This one doesn't work, I get #VALUE!: Function temp1() Range("Comm!f7").Value = 0 End Function And this doesn't work either: Function temp1() temp End Function There could be another way, a better one, but much too difficult for me. What I am trying to do is to erase from memory all the values pertaining to Sheet1, as soon as any of its cells is modified by the user (the values are preserved in memory as public arrays). By searching Google, I found references to Worksheet_Change. Can someone help me with that too, please ? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter a value in a cell
You can't change the value of other cells using a User Defined Function
(UDF) in the worksheet. You can not change the worksheet environment either (no formatting). You can only return a value to the cell containing the function. You can reference other cells to use them for calculations, but you can not alter them. -- Regards, Tom Ogilvy "Sinus Log" wrote in message ... I've tried this macro and it works: Sub temp() Range("Comm!f7").Value = 0 End Sub But what I need is a function. This one doesn't work, I get #VALUE!: Function temp1() Range("Comm!f7").Value = 0 End Function And this doesn't work either: Function temp1() temp End Function There could be another way, a better one, but much too difficult for me. What I am trying to do is to erase from memory all the values pertaining to Sheet1, as soon as any of its cells is modified by the user (the values are preserved in memory as public arrays). By searching Google, I found references to Worksheet_Change. Can someone help me with that too, please ? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter a value in a cell
Hi
Sheets("Comm").Range("F7").Value=0 -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Sinus Log" wrote in message ... I've tried this macro and it works: Sub temp() Range("Comm!f7").Value = 0 End Sub But what I need is a function. This one doesn't work, I get #VALUE!: Function temp1() Range("Comm!f7").Value = 0 End Function And this doesn't work either: Function temp1() temp End Function There could be another way, a better one, but much too difficult for me. What I am trying to do is to erase from memory all the values pertaining to Sheet1, as soon as any of its cells is modified by the user (the values are preserved in memory as public arrays). By searching Google, I found references to Worksheet_Change. Can someone help me with that too, please ? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter a value in a cell
Oops! I throughly missed this point - I concentrated on single row only :-(
-- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Tom Ogilvy" wrote in message ... You can't change the value of other cells using a User Defined Function (UDF) in the worksheet. You can not change the worksheet environment either (no formatting). You can only return a value to the cell containing the function. You can reference other cells to use them for calculations, but you can not alter them. -- Regards, Tom Ogilvy "Sinus Log" wrote in message ... I've tried this macro and it works: Sub temp() Range("Comm!f7").Value = 0 End Sub But what I need is a function. This one doesn't work, I get #VALUE!: Function temp1() Range("Comm!f7").Value = 0 End Function And this doesn't work either: Function temp1() temp End Function There could be another way, a better one, but much too difficult for me. What I am trying to do is to erase from memory all the values pertaining to Sheet1, as soon as any of its cells is modified by the user (the values are preserved in memory as public arrays). By searching Google, I found references to Worksheet_Change. Can someone help me with that too, please ? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter a value in a cell
Arvi Laanemets wrote:
Hi Sheets("Comm").Range("F7").Value=0 It doesn't work either, Arvi. Tom is certainly right. Thanks to both of you. I'll have to try to follow the other path. For that, I think it's better to open a new thread with the proper title: To detect changes in a worksheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup if #N/A then enter enter data in cell I4 | Excel Discussion (Misc queries) | |||
Enter multiple numbers in a cell so total shows when enter keypres | Excel Worksheet Functions | |||
Auto enter date when data in enter in another cell | Excel Worksheet Functions | |||
How to force <enter to behave like <alt + <enter within a cell | Excel Programming | |||
how do I enter a RETURN (Enter) in a cell in EXCEL2003 | Excel Discussion (Misc queries) |