Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REPSOT?? Sub Worksheet_Change(ByVal Target As Range) | Excel Discussion (Misc queries) | |||
Multiple values in Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
Control Toolbox and Private Sub Worksheet_Change(ByVal Target | Excel Discussion (Misc queries) | |||
Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Worksheet Functions | |||
Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet | Excel Programming |