To detect changes in a worksheet

I have defined public arrays to keep in memory user data
entered in Sheet1. I need to have these data erased from
memory as soon as new data is entered in that worksheet. For
that, I can use something like:

public function f()
?
for i=1 to 12
indicator(i)=0
next i
end function

I don't have the knowledge to write the missing statement. I
suppose it involves Worksheet_Change. Can someone help me
with that, please ?
Thanks

Do you want to clear all of the array on every change (probably not), or
what? Do you have a limited size data range that will be affected in this
way?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sinus Log" <tlobbosNOS-PAM@sympatico.ca> wrote in message
news:%23pNIBOj%23FHA.532@TK2MSFTNGP15.phx.gbl...
> I have defined public arrays to keep in memory user data
> entered in Sheet1. I need to have these data erased from
> memory as soon as new data is entered in that worksheet. For
> that, I can use something like:
>
> public function f()
> ?
> for i=1 to 12
> indicator(i)=0
> next i
> end function
>
> I don't have the knowledge to write the missing statement. I
> suppose it involves Worksheet_Change. Can someone help me
> with that, please ?
> Thanks

Bob Phillips wrote:
> Do you want to clear all of the array on every change (probably not), or
> what? Do you have a limited size data range that will be affected in this
> way?
>
There are no formulas in Sheet1, only user data. These data
aren't supposed to change often. But they impact all
formulas in the workbook. The user might want to change the
values of about 25 cells at a time, out of 300, in Sheet1,
on an irregular basis. Could leave them unchanged for years.
But then, if the arrays that hold the data aren't
reinitialized, the results would be wrong.

It's enough to set the array indicator() to 0. That would
reinitialize all the other arrays.

The data in Sheet1 are included in the range a1:r209 .

I've read through your other threads and must admit that I am not sure I
totally follow the use of public arrays in your project.

I think what you are asking here is that you want to monitor the cells
in range A1:A209 on a particular sheet. If any of these cells is changed
by a user set each element of the public array indicator to 0. If this
is correct then right click the sheet in question, select view code and
paste the following change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Not Intersect(Target, Range("A1:R209")) Is Nothing Then
For i = 1 To 12
indicator(i) = 0
Next i
End If
End Sub

Hope this helps

PS I am assuming from your example that you are using Option Base 1 for
your arrays.

Sinus Log wrote:
> Bob Phillips wrote:
>
>> Do you want to clear all of the array on every change (probably not), or
>> what? Do you have a limited size data range that will be affected in this
>> way?
>>
> There are no formulas in Sheet1, only user data. These data aren't
> supposed to change often. But they impact all formulas in the workbook.
> The user might want to change the values of about 25 cells at a time,
> out of 300, in Sheet1, on an irregular basis. Could leave them unchanged
> for years. But then, if the arrays that hold the data aren't
> reinitialized, the results would be wrong.
>
> It's enough to set the array indicator() to 0. That would reinitialize
> all the other arrays.
>
> The data in Sheet1 are included in the range a1:r209 .

Rowan Drummond wrote:
> I've read through your other threads and must admit that I am not sure I
> totally follow the use of public arrays in your project.
>
> I think what you are asking here is that you want to monitor the cells
> in range A1:A209 on a particular sheet. If any of these cells is changed
> by a user set each element of the public array indicator to 0. If this
> is correct then right click the sheet in question, select view code and
> paste the following change event:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim i As Long
> If Not Intersect(Target, Range("A1:R209")) Is Nothing Then
> For i = 1 To 12
> indicator(i) = 0
> Next i
> End If
> End Sub
>
> Hope this helps
>
> PS I am assuming from your example that you are using Option Base 1 for
> your arrays.
>
> Sinus Log wrote:
>
>> Bob Phillips wrote:
>>
>>> Do you want to clear all of the array on every change (probably not), or
>>> what? Do you have a limited size data range that will be affected in
>>> this
>>> way?
>>>
>> There are no formulas in Sheet1, only user data. These data aren't
>> supposed to change often. But they impact all formulas in the
>> workbook. The user might want to change the values of about 25 cells
>> at a time, out of 300, in Sheet1, on an irregular basis. Could leave
>> them unchanged for years. But then, if the arrays that hold the data
>> aren't reinitialized, the results would be wrong.
>>
>> It's enough to set the array indicator() to 0. That would reinitialize
>> all the other arrays.
>>
>> The data in Sheet1 are included in the range a1:r209 .

Fantastic, it's exactly what I needed. Many thanks. And yes,
I'm using Option Base 1.

> I've read through your other threads and must admit that
I am not sure I
> totally follow the use of public arrays in your project.

It's probably because I have not been initiated to Excel,
and I don't use the proper words. I am a self-made
123-veteran, if I may say so. But you got everything I meant
all right. Thanks again.