Excel VBA .... Worksheet_Change(ByVal Target As Range) question
 

I currently have an Excel worksheet with data on it. I would like to b
able to change/edit the value of a particular cell and have the cel
background turn from white to, say, blue. I am aware of the idea o
creating a Private sub Worksheet_Change(ByVal Target as Range)
routine and placing code in this program that will assign the desire
color to the cell in question. The problem is that if I create a su
routine such as:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.interior.colorindex=5

End sub

...as soon as I open or select the worksheet the above code is alread
in affect. If I change a value in a data cell it turns to the desire
color. I would like instead to have the sheet only honor the abov
sub routine when I choose to use it. Perhaps by using a button linke
to this sub routine/macro I could click on.

The problem I am seeking an answer for is how, or can one, cause th
worksheet to at one moment in time turn/utilize this "change the colo
code" on and at another time turn it off. Ultimately I would like t
bring data into a vacant worksheet and not have it turn to a differen
color right away. After I hit a button, or some other means o
telling Excel it is time to cause cell backgrounds to change color
then any change to a cell or range of cells would turn to the desire
color.

Any help you could give me on this subject or means to control when
particular worksheet goes into change mode would be greatl
appreciated. Joseph Donnell

--
Message posted from http://www.ExcelForum.com

Excel VBA .... Worksheet_Change(ByVal Target As Range) question
 

Hi Joseph,

I think you have answered your own question. Can you simply add a
CheckBox control to the worksheet (assume added at design time and named
"CheckBox1".

Then you simply check the value in your Change event handler:


Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.OLEObjects("CheckBox1").Object.Value = True Then
Target.interior.colorindex=5
End If
End Sub

Is this what you want?

-TJ

On Mon, 17 May 2004 13:40:27 -0500, wrote:

I currently have an Excel worksheet with data on it. I would like to be
able to change/edit the value of a particular cell and have the cell
background turn from white to, say, blue. I am aware of the idea of
creating a Private sub Worksheet_Change(ByVal Target as Range)
routine and placing code in this program that will assign the desired
color to the cell in question. The problem is that if I create a sub
routine such as:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.interior.colorindex=5

End sub

..as soon as I open or select the worksheet the above code is already
in affect. If I change a value in a data cell it turns to the desired
color. I would like instead to have the sheet only honor the above
sub routine when I choose to use it. Perhaps by using a button linked
to this sub routine/macro I could click on.

The problem I am seeking an answer for is how, or can one, cause the
worksheet to at one moment in time turn/utilize this "change the color
code" on and at another time turn it off. Ultimately I would like to
bring data into a vacant worksheet and not have it turn to a different
color right away. After I hit a button, or some other means of
telling Excel it is time to cause cell backgrounds to change color,
then any change to a cell or range of cells would turn to the desired
color.

Any help you could give me on this subject or means to control when a
particular worksheet goes into change mode would be greatly
appreciated. Joseph Donnelly


---
Message posted from http://www.ExcelForum.com/

Excel VBA .... Worksheet_Change(ByVal Target As Range) question
 

Add a control toolbox button on that sheet and use code something like

Option Explicit

Dim fChange As Boolean

Private Sub CommandButton1_Click()
fChange = Not fChange
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If fChange Then
'your code
'end if
End Sub

The button then acst as a switch turn the formatting on or off

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Joseph Donnelly " wrote in
message ...
I currently have an Excel worksheet with data on it. I would like to be
able to change/edit the value of a particular cell and have the cell
background turn from white to, say, blue. I am aware of the idea of
creating a Private sub Worksheet_Change(ByVal Target as Range)
routine and placing code in this program that will assign the desired
color to the cell in question. The problem is that if I create a sub
routine such as:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.interior.colorindex=5

End sub

..as soon as I open or select the worksheet the above code is already
in affect. If I change a value in a data cell it turns to the desired
color. I would like instead to have the sheet only honor the above
sub routine when I choose to use it. Perhaps by using a button linked
to this sub routine/macro I could click on.

The problem I am seeking an answer for is how, or can one, cause the
worksheet to at one moment in time turn/utilize this "change the color
code" on and at another time turn it off. Ultimately I would like to
bring data into a vacant worksheet and not have it turn to a different
color right away. After I hit a button, or some other means of
telling Excel it is time to cause cell backgrounds to change color,
then any change to a cell or range of cells would turn to the desired
color.

Any help you could give me on this subject or means to control when a
particular worksheet goes into change mode would be greatly
appreciated. Joseph Donnelly


---
Message posted from http://www.ExcelForum.com/