I'm writing an Excel (vba) utility for a project at work and I could use
some advice on my approach.

I have a folder with 100 .wav files. Each file has a corresponding
text-based script file with variables in it. Like this:

max_simultaneous_samples = 2;
min_playback_rate = 0.9;
max_playback_rate = 1.1;

I wrote a utility that allows users to add variable names as column
headings. When they click on a READ VALUES button my tool fills the first
column with the appropriate filenames, and then it searches through the
files and extracts the variables. The output looks like this:

FILENAME max_simultaneous_samples min_playback_rate
max_playback_rate
thunk.txt 2
0.9 1.1
clap.txt 2
0.8 1.2
gunshot.txt 2
0.7 1.2


I want users to be able to change those values, and then I want to write the
changed values back to the appropriate files. My current plan is that when
I fill worksheet 1 with the values above, I'll add the same values to the
same cells in worksheet 2. That way I can tell which values are the same
and which have been changed. (I'll probably change the font color of any
value that is different from the initial values. That way people will have
a visual indication of their changes before they click the WRITE VALUES
button.)

Is there a better/easier way to do this?

If my approach is okay, I guess I'll have to use the worksheet_changed event
and use 'Target' to search for the cells that have been changed. Right?

Thanks in advance.

Target will hold a reference to the cell that changed/was edited that
triggered the event.

--
Regards,
Tom Ogilvy

"Cybert" wrote in message
...
I'm writing an Excel (vba) utility for a project at work and I could use
some advice on my approach.

I have a folder with 100 .wav files. Each file has a corresponding
text-based script file with variables in it. Like this:

max_simultaneous_samples = 2;
min_playback_rate = 0.9;
max_playback_rate = 1.1;

I wrote a utility that allows users to add variable names as column
headings. When they click on a READ VALUES button my tool fills the first
column with the appropriate filenames, and then it searches through the
files and extracts the variables. The output looks like this:

FILENAME max_simultaneous_samples min_playback_rate
max_playback_rate
thunk.txt 2
0.9 1.1
clap.txt 2
0.8 1.2
gunshot.txt 2
0.7 1.2


I want users to be able to change those values, and then I want to write
the
changed values back to the appropriate files. My current plan is that
when
I fill worksheet 1 with the values above, I'll add the same values to the
same cells in worksheet 2. That way I can tell which values are the same
and which have been changed. (I'll probably change the font color of any
value that is different from the initial values. That way people will
have
a visual indication of their changes before they click the WRITE VALUES
button.)

Is there a better/easier way to do this?

If my approach is okay, I guess I'll have to use the worksheet_changed
event
and use 'Target' to search for the cells that have been changed. Right?

Thanks in advance.

But Target will lose its reference every time a new event is triggered,
right?

I want users to be able to play around with the values in the worksheet, and
then click the WRITE VALUES button to update the appropriate files with all
of the values that changed since they were added to the sheet.

This would make it really easy for me to add a "REVERT" button, too. (It
would work even if people saved the file as a work-in-progress.)


"Tom Ogilvy" wrote in message
...
Target will hold a reference to the cell that changed/was edited that
triggered the event.

--
Regards,
Tom Ogilvy

"Cybert" wrote in message
...
I'm writing an Excel (vba) utility for a project at work and I could use
some advice on my approach.

I have a folder with 100 .wav files. Each file has a corresponding
text-based script file with variables in it. Like this:

max_simultaneous_samples = 2;
min_playback_rate = 0.9;
max_playback_rate = 1.1;

I wrote a utility that allows users to add variable names as column
headings. When they click on a READ VALUES button my tool fills the
first
column with the appropriate filenames, and then it searches through the
files and extracts the variables. The output looks like this:

FILENAME max_simultaneous_samples min_playback_rate
max_playback_rate
thunk.txt 2
0.9 1.1
clap.txt 2
0.8 1.2
gunshot.txt 2
0.7 1.2


I want users to be able to change those values, and then I want to write
the
changed values back to the appropriate files. My current plan is that
when
I fill worksheet 1 with the values above, I'll add the same values to
the
same cells in worksheet 2. That way I can tell which values are the
same
and which have been changed. (I'll probably change the font color of
any
value that is different from the initial values. That way people will
have
a visual indication of their changes before they click the WRITE VALUES
button.)

Is there a better/easier way to do this?

If my approach is okay, I guess I'll have to use the worksheet_changed
event
and use 'Target' to search for the cells that have been changed. Right?

Thanks in advance.

You said:

If my approach is okay, I guess I'll have to use the worksheet_changed
event
and use 'Target' to search for the cells that have been changed. Right?

In your approach, I don't see any role for Target or the change event.

--
Regards,
Tom Ogilvy


"Cybert" wrote in message
...
But Target will lose its reference every time a new event is triggered,
right?

I want users to be able to play around with the values in the worksheet,
and
then click the WRITE VALUES button to update the appropriate files with
all
of the values that changed since they were added to the sheet.

This would make it really easy for me to add a "REVERT" button, too. (It
would work even if people saved the file as a work-in-progress.)


"Tom Ogilvy" wrote in message
...
Target will hold a reference to the cell that changed/was edited that
triggered the event.

--
Regards,
Tom Ogilvy

"Cybert" wrote in message
...
I'm writing an Excel (vba) utility for a project at work and I could
use
some advice on my approach.

I have a folder with 100 .wav files. Each file has a corresponding
text-based script file with variables in it. Like this:

max_simultaneous_samples = 2;
min_playback_rate = 0.9;
max_playback_rate = 1.1;

I wrote a utility that allows users to add variable names as column
headings. When they click on a READ VALUES button my tool fills the
first
column with the appropriate filenames, and then it searches through
the
files and extracts the variables. The output looks like this:

FILENAME max_simultaneous_samples min_playback_rate
max_playback_rate
thunk.txt 2
0.9 1.1
clap.txt 2
0.8 1.2
gunshot.txt 2
0.7 1.2


I want users to be able to change those values, and then I want to
write
the
changed values back to the appropriate files. My current plan is that
when
I fill worksheet 1 with the values above, I'll add the same values to
the
same cells in worksheet 2. That way I can tell which values are the
same
and which have been changed. (I'll probably change the font color of
any
value that is different from the initial values. That way people will
have
a visual indication of their changes before they click the WRITE
VALUES
button.)

Is there a better/easier way to do this?

If my approach is okay, I guess I'll have to use the worksheet_changed
event
and use 'Target' to search for the cells that have been changed.
Right?

Thanks in advance.

Going back to the original post, I want to use Target and the change event
to change the color of cells that are different from the initial values. So
anytime someone changes one or more values I want to compare the new values
to the original values (stored in the 2nd worksheet). If the changed value
is different I'll change the font color to blue. If the value is the same
I'll change it to black. (Then I'll use that font color to determine which
files I need to load and change.)

I'd use Target and the change event for that, right?

(Thanks for your help on this, Tom. I appreciate it.)


"Tom Ogilvy" wrote in message
...
You said:

If my approach is okay, I guess I'll have to use the worksheet_changed
event
and use 'Target' to search for the cells that have been changed. Right?

In your approach, I don't see any role for Target or the change event.

--
Regards,
Tom Ogilvy


"Cybert" wrote in message
...
But Target will lose its reference every time a new event is triggered,
right?

I want users to be able to play around with the values in the worksheet,
and
then click the WRITE VALUES button to update the appropriate files with
all
of the values that changed since they were added to the sheet.

This would make it really easy for me to add a "REVERT" button, too.
(It
would work even if people saved the file as a work-in-progress.)


"Tom Ogilvy" wrote in message
...
Target will hold a reference to the cell that changed/was edited that
triggered the event.

--
Regards,
Tom Ogilvy

"Cybert" wrote in message
...
I'm writing an Excel (vba) utility for a project at work and I could
use
some advice on my approach.

I have a folder with 100 .wav files. Each file has a corresponding
text-based script file with variables in it. Like this:

max_simultaneous_samples = 2;
min_playback_rate = 0.9;
max_playback_rate = 1.1;

I wrote a utility that allows users to add variable names as column
headings. When they click on a READ VALUES button my tool fills the
first
column with the appropriate filenames, and then it searches through
the
files and extracts the variables. The output looks like this:

FILENAME max_simultaneous_samples min_playback_rate
max_playback_rate
thunk.txt 2
0.9 1.1
clap.txt 2
0.8 1.2
gunshot.txt 2
0.7 1.2


I want users to be able to change those values, and then I want to
write
the
changed values back to the appropriate files. My current plan is
that
when
I fill worksheet 1 with the values above, I'll add the same values
to
the
same cells in worksheet 2. That way I can tell which values are the
same
and which have been changed. (I'll probably change the font color
of
any
value that is different from the initial values. That way people
will
have
a visual indication of their changes before they click the WRITE
VALUES
button.)

Is there a better/easier way to do this?

If my approach is okay, I guess I'll have to use the
worksheet_changed
event
and use 'Target' to search for the cells that have been changed.
Right?

Thanks in advance.

Yes. Target (in the change event) will hold a reference to the cell edited.
You can then compare the value to your second sheet and change the color if
different.

--
Regards,
Tom Ogilvy

"Cybert" wrote in message
...
Going back to the original post, I want to use Target and the change event
to change the color of cells that are different from the initial values.
So
anytime someone changes one or more values I want to compare the new
values
to the original values (stored in the 2nd worksheet). If the changed
value
is different I'll change the font color to blue. If the value is the same
I'll change it to black. (Then I'll use that font color to determine
which
files I need to load and change.)

I'd use Target and the change event for that, right?

(Thanks for your help on this, Tom. I appreciate it.)


"Tom Ogilvy" wrote in message
...
You said:

If my approach is okay, I guess I'll have to use the worksheet_changed
event
and use 'Target' to search for the cells that have been changed.
Right?

In your approach, I don't see any role for Target or the change event.

--
Regards,
Tom Ogilvy


"Cybert" wrote in message
...
But Target will lose its reference every time a new event is
triggered,
right?

I want users to be able to play around with the values in the
worksheet,
and
then click the WRITE VALUES button to update the appropriate files
with
all
of the values that changed since they were added to the sheet.

This would make it really easy for me to add a "REVERT" button, too.
(It
would work even if people saved the file as a work-in-progress.)


"Tom Ogilvy" wrote in message
...
Target will hold a reference to the cell that changed/was edited
that
triggered the event.

--
Regards,
Tom Ogilvy

"Cybert" wrote in message
...
I'm writing an Excel (vba) utility for a project at work and I
could
use
some advice on my approach.

I have a folder with 100 .wav files. Each file has a
corresponding
text-based script file with variables in it. Like this:

max_simultaneous_samples = 2;
min_playback_rate = 0.9;
max_playback_rate = 1.1;

I wrote a utility that allows users to add variable names as
column
headings. When they click on a READ VALUES button my tool fills
the
first
column with the appropriate filenames, and then it searches
through
the
files and extracts the variables. The output looks like this:

FILENAME max_simultaneous_samples min_playback_rate
max_playback_rate
thunk.txt 2
0.9 1.1
clap.txt 2
0.8 1.2
gunshot.txt 2
0.7 1.2


I want users to be able to change those values, and then I want to
write
the
changed values back to the appropriate files. My current plan is
that
when
I fill worksheet 1 with the values above, I'll add the same values
to
the
same cells in worksheet 2. That way I can tell which values are
the
same
and which have been changed. (I'll probably change the font color
of
any
value that is different from the initial values. That way people
will
have
a visual indication of their changes before they click the WRITE
VALUES
button.)

Is there a better/easier way to do this?

If my approach is okay, I guess I'll have to use the
worksheet_changed
event
and use 'Target' to search for the cells that have been changed.
Right?

Thanks in advance.

Okay, final question.

I added some code so that if someone changes one or more values outside of
the data entry area (based on the values in worksheet 2) then I set those
values back to vbNullString. Unfortunately, this particular feature takes
nearly a second per line. Why is it so slow??? I must be doing something
wrong, but I'm not sure what.



For Each cell In Target
If cell.Value = Sheet2.Cells(cell.Row, cell.Column) Then
With cell
.Font.ColorIndex = 0
End With
Else
With cell
.Font.ColorIndex = 3
End With
End If
If Sheet2.Cells(cell.Row, cell.Column) = vbNullString Then
cell.Value = vbNullString
End If
Next cell