| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nesting worksheet_Calculate inside worksheet_CHANGE
I am not fluent with VBA and need your help to correct this code please.
I got this code from a forum. It changes the color of a chart based on a condition. It does exactly what I want, but because it uses worksheet_calculate, this code only trigers when I execute the code in the VB tool... it does not change on the fly when a cell is changed. I was told by someone on in a forum that I should nest this code below insode a Worksheet_CHANGE... to that when the cell (AG2) changes that the worksheet_calculate code is executed. I do not know how to nest these. Can you help you please? ---- Private Sub Worksheet_Calculate() Dim chrt As ChartObject Dim i As Integer For i = 1 To Sheets.Count With Sheets(i) For Each chrt In .ChartObjects If IsNumeric(Me.Range("AG1").Value) Then If Me.Range("AG1").Value 0.1 Then chrt.Chart.ChartArea.Interior.ColorIndex = 3 Else chrt.Chart.ChartArea.Interior.ColorIndex _ = xlAutomatic End If End If Next chrt End With Next i End Sub ---- Justin |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nesting worksheet_Calculate inside worksheet_CHANGE
Justin Luyt wrote:
I am not fluent with VBA and need your help to correct this code please. I got this code from a forum. It changes the color of a chart based on a condition. It does exactly what I want, but because it uses worksheet_calculate, this code only trigers when I execute the code in the VB tool... it does not change on the fly when a cell is changed. I was told by someone on in a forum that I should nest this code below insode a Worksheet_CHANGE... to that when the cell (AG2) changes that the worksheet_calculate code is executed. I do not know how to nest these. [cut] Hi Justin, you have just to change the event from Calculate to Change and check what is changed... So your code becomes: Private Sub Worksheet_Change(ByVal Target As Range) Dim chrt As ChartObject Dim i As Integer If Not Intersect (Target, "AG2") is Nothing Then For i = 1 To Sheets.Count With Sheets(i) For Each chrt In .ChartObjects If IsNumeric(Me.Range("AG1").Value) Then If Me.Range("AG1").Value 0.1 Then chrt.Chart.ChartArea.Interior.ColorIndex = 3 Else chrt.Chart.ChartArea.Interior.ColorIndex _ = xlAutomatic End If End If Next chrt End With Next i Else Exit Sub End If End Sub -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nesting worksheet_Calculate inside worksheet_CHANGE
I get compiler errors when i use this code.
"Franz Verga" wrote: Justin Luyt wrote: I am not fluent with VBA and need your help to correct this code please. I got this code from a forum. It changes the color of a chart based on a condition. It does exactly what I want, but because it uses worksheet_calculate, this code only trigers when I execute the code in the VB tool... it does not change on the fly when a cell is changed. I was told by someone on in a forum that I should nest this code below insode a Worksheet_CHANGE... to that when the cell (AG2) changes that the worksheet_calculate code is executed. I do not know how to nest these. [cut] Hi Justin, you have just to change the event from Calculate to Change and check what is changed... So your code becomes: Private Sub Worksheet_Change(ByVal Target As Range) Dim chrt As ChartObject Dim i As Integer If Not Intersect (Target, "AG2") is Nothing Then For i = 1 To Sheets.Count With Sheets(i) For Each chrt In .ChartObjects If IsNumeric(Me.Range("AG1").Value) Then If Me.Range("AG1").Value 0.1 Then chrt.Chart.ChartArea.Interior.ColorIndex = 3 Else chrt.Chart.ChartArea.Interior.ColorIndex _ = xlAutomatic End If End If Next chrt End With Next i Else Exit Sub End If End Sub -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nesting worksheet_Calculate inside worksheet_CHANGE
Justin Luyt wrote:
I get compiler errors when i use this code. If you like you could try to upload your fie to www.rapidshare.de... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nesting worksheet_Calculate inside worksheet_CHANGE
get the file he
http://www2.ibackup.com/qmanager/ser...key=qozde71198 "Justin Luyt" wrote: I am not fluent with VBA and need your help to correct this code please. I got this code from a forum. It changes the color of a chart based on a condition. It does exactly what I want, but because it uses worksheet_calculate, this code only trigers when I execute the code in the VB tool... it does not change on the fly when a cell is changed. I was told by someone on in a forum that I should nest this code below insode a Worksheet_CHANGE... to that when the cell (AG2) changes that the worksheet_calculate code is executed. I do not know how to nest these. Can you help you please? ---- Private Sub Worksheet_Calculate() Dim chrt As ChartObject Dim i As Integer For i = 1 To Sheets.Count With Sheets(i) For Each chrt In .ChartObjects If IsNumeric(Me.Range("AG1").Value) Then If Me.Range("AG1").Value 0.1 Then chrt.Chart.ChartArea.Interior.ColorIndex = 3 Else chrt.Chart.ChartArea.Interior.ColorIndex _ = xlAutomatic End If End If Next chrt End With Next i End Sub ---- Justin |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nesting worksheet_Calculate inside worksheet_CHANGE
Justin Luyt wrote:
get the file he http://www2.ibackup.com/qmanager/ser...key=qozde71198 Hi Justin, I made a mistake in the code, sorry... The right version is this (I assume also that you want to change the chart area color when AG1 change, as stated in your code and not when AG2 change, as you stated in your post...): Private Sub Worksheet_Change(ByVal Target As Range) Dim chrt As ChartObject Dim i As Integer If Not Intersect(Target, Range("AG1")) Is Nothing Then For i = 1 To Sheets.Count With Sheets(i) For Each chrt In .ChartObjects If IsNumeric(Me.Range("AG1").Value) Then If Me.Range("AG1").Value 0.1 Then chrt.Chart.ChartArea.Interior.ColorIndex = 3 Else chrt.Chart.ChartArea.Interior.ColorIndex _ = xlAutomatic End If End If Next chrt End With Next i Else Exit Sub End If End Sub -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy "Justin Luyt" wrote: I am not fluent with VBA and need your help to correct this code please. I got this code from a forum. It changes the color of a chart based on a condition. It does exactly what I want, but because it uses worksheet_calculate, this code only trigers when I execute the code in the VB tool... it does not change on the fly when a cell is changed. I was told by someone on in a forum that I should nest this code below insode a Worksheet_CHANGE... to that when the cell (AG2) changes that the worksheet_calculate code is executed. I do not know how to nest these. Can you help you please? ---- Private Sub Worksheet_Calculate() Dim chrt As ChartObject Dim i As Integer For i = 1 To Sheets.Count With Sheets(i) For Each chrt In .ChartObjects If IsNumeric(Me.Range("AG1").Value) Then If Me.Range("AG1").Value 0.1 Then chrt.Chart.ChartArea.Interior.ColorIndex = 3 Else chrt.Chart.ChartArea.Interior.ColorIndex _ = xlAutomatic End If End If Next chrt End With Next i End Sub ---- Justin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting Networkdays function inside and If function | Excel Worksheet Functions | |||
| Nesting-Don't use Blanks inside formula | Excel Worksheet Functions | |||
| Launch a Sub Worksheet_Change Event from inside a macro | Excel Programming | |||
| worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
| worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
Hybrid Mode
