Controls (Control Toolbox) : Change event Code .Trigger Macr

One way would be to use the Worksheet_Change event of the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
If Target = "True" Then
'run code here
End If
End Sub

However, this will fire evertime there is a change to the worksheet, so you would have to code if you do not want it fire each time a change is made and only when A1 is initially changed from FALSE to TRUE.

Also try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") Then ' as A1 contains True / False no need for explicit checking
    'your code
End If
End Sub

If your checkbox is from the Forms toolbar, then when it changes the value of A1 the Change event does not fire. If you use the checkbox from the Controls toolbar, then you can assign code to it that will do whatever you want. For example,

Private Sub CheckBox1_Click()
    Range("A1") = CheckBox1.Value
    If CheckBox1.Value Then
        MsgBox "Just checked it"
        'replace with your code
    End If
    ActiveCell.Select 'leave checkbox
End Sub

bnix:
I hope it is ok for me to step in here. I have a macro I want to fire ONLY when the cell is TRUE and not when other action takes place on the worksheet. Am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
If Target = "True" Then
FYChange
End If
End Sub

"However, this will fire evertime there is a change to the worksheet, so you would have to code if you do not want it fire each time a change is made and only when A1 is initially changed from FALSE to TRUE."

What should my code be?

Thanks in advance.

Colin,
If my macro Private Sub CheckBox1_Click() isn't firing when you click your checkbox then either

1) The macro isn't on the sheet module where the checkbox is, or
2) The macro isn't assigned to the checkbox, or
3) You are still in the design mode, or
4) Something weird is happening

RHerb,
The problem with your code is you are always checking A1 no matter what the target was when the change macro was called. Try
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
On Error GoTo Leave 'in case A1 is text
If Target.Cells(1) Then FYChange
Leave:
End Sub

Derk:
Thank you.
New at this not sure of the answer with the Intersect:

Situation:
There will be either "False" or "True" in the cell as a result of a previous query. A new query will then fire, and return and my formula will return another result (False or True). If it is "False", I want to ignore, if the result is "True", run the "FYChange".

Questions:
In the line:
1)
If Intersect(Target,Range("A1")) is Nothing Then Exit Sub

"A1" will be either "True" or "False", so it will always be "Nothing" ??????

2)
If Target.Cells(1) Then FYChange

Does "(1)" represent "True" or a numeric value?

3) Result?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is False Then Exit Sub
On Error GoTo Leave 'in case A1 is text
If Target.Cells(True) Then FYChange
Leave:
End Sub

???

RHerb:
1) if the target that was changed doesn't have something in common with cell A1 then nothing happens. This keeps the routine from checking A1 when there hasn't been a change there. If the target and A1 have nothing in common then the intersection is Nothing.
2) the cells(1) is a precaution in case changes are made to A1 and other cells at the same time (while holding down the control key when entering). The cells(1) means take the first cell in the target range. Strictly speaking that precaution should also have been in the Intersect line.
3) The line If Target.Cells(1) Then FYChange
probably should have been written
If Range("A1") the FYChange
if the value in A1 is true (or any non zero number) then FYChange will be called.

This all assumes that when your query fires it triggers a change in A1. Does the macro work?

My formula worked, I get the "TRUE" but the macro "FYChange" didn't fire. There are several queries that run BEFORE this feature can be fired.

Edited: I ran it a couple of more times, the macro does not fire at all, but I get my "True" or "False".

The previous code worked but it would fire on every change to the worksheet, as you mentioned.
Here is my actual code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L20")) Is Nothing Then Exit Sub
On Error GoTo Leave 'in case L20 is text
If Range("L20") Then FYChange
Leave:
End Sub

Ok, watching the action more closely, as the queries run, the cell (L20) may start out "False" or "True". Due to the queries running, L20 may change a couple of times BEFORE all the queries are finished. Because they come from different web sites, they can't (or I don't think they can ... never say can't) run at the same time. Which means the first query, which is in my formula, may change the cell to "True", but when the next query runs just after that, it may change the cell to "False", and visa versa. It is when this final result is settled in that I want to run the code.

Rather confusing, I know.

I suspect your queries are not triggering the Change event. What is running the queries? If it's a macro then you can just add a line at the end to check L20 and take the necessary action.

Actually, the queries are triggered by a cell event at "B2". If that cell id changes, the queries fire and download tables from 4 web sites. Once this information is presented in the tables on the sheet, I check two "FY" fields in two tables to see if they are the same (have to use "substitute" and "trim" formulae to determine this because they are of different formats). If they are the same (TRUE), I then run the FYChange macro (which changes the fields so that they are formatted exactly the same).

Example: " FY(9/04)" is the same as "FY (09/04)" I trim and substitute to determine if they are equal. If so, FYChange copies the "FY (09/04)" to the cell that has " FY(9/04)", making them equal so that the rest of my formulae work.

What is happening is:
One query will update the first FY field, which makes the True/False cell change ... that fires the macro. Then another query will update a second table which will cause the True/False cell to change again. That fires the macro again.

With my first code, I fired with each event, as you mentioned earlier.

I have looked into putting a delay on the code but all I succeeded in doing was creating a long running macro that I had to C-A-Del out of.

Was hoping to be able to streamline it a bit and stop the blinking of the affected cells and, perhaps, run a bit faster.

Quote

Originally posted by RHerb
Was hoping to be able to streamline it a bit and stop the blinking of the affected cells and, perhaps, run a bit faster.

Try posting up your code if you want help with the "streamlining"

As for speeding it up, setting calculation to manual & turninf off screenupdating generally helps

Sub Speedystuff()


With Application
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With


'your code


With Application
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With


End Sub

Hope this helps,

WillR:
Will give what you posted a try. I have cleaned a lot of the code and then ran "CodeCleaner". I think the majority of the problem comes from the embedded queries and not being able to control the firing sequence. I had thought about setting them iup n a module and setting time delays, but jumped away from that notion.

The real problem (minor) I face right now has been the in the posts below. Would like to run the FYChange macro AFTER the queries are complete and not fire during the query posting.

Thanks

Maybe just put a step in after the queryrefresh, such as

'queryrefresh code


Sheets(1).[A1].Activate
'call FYChange macro

Any good, or am I missing the point?

I don't have a query refresh code. The queries refresh on a cell change and are "embedded". When that cell changes, there are 4 queries that fire and change tables on this sheet. When two of the tables are complete, I check two cells to see if they are the same (with a "Substitute" formula). If they are the same, I post the result "TRUE" in a cell. This action fires the "FYChange" macro.

I had a macro button to manually fire the "FYChange" and it worked just great. I then created VBA code so that the macro would fire on the cell change to "TRUE". The problem is that it fires several times because the tables from the queries hit the sheet at different times causing the cell to go from "TRUE" to "FALSE", To "TRUE".

I may be firing the "Substitute" formula too soon and perhaps I should put an "IF" statemtent in there stating that IF a certain event hasn't occurred, don't fire. Or something of that nature.

As I said before, these queries are not VBA (in a module) where I can sequentially fire them off so I can't use the last code you posted. (I don't think ...)

This has been quite the challenge for me right now.

If the queries refresh on a cell change, but the refresh call is not in VBA code how does the cell change trigger the queries? What does "embedded" mean?

Quote

As I said before, these queries are not VBA (in a module) where I can sequentially fire them off so I can't use the last code you posted. (I don't think ...)

Are they MSQuery Queries ?