running macro automatically in Excel

[longzoo]

I have a PLC (programmable logic controller) linked to my PC and I'
using excel as an HMI. I have no education in VB but have managed t
figure enough out with a book and examples. now my problem... I have
cell in a workbook that changes from "true" to "false" every coupl
seconds. I also have a macro written that writes data from a cell to
memory location on the PLC. Heres where it falls apart in sheet
under Microsoft Excel Objects I have the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Cells(14, 7) = True Then 'checks for flag (changes every
seconds)
Call Write_time
Else
Cells(8, 5).Interior.ColorIndex = 7 'for testing not needed!!
End If
End Sub

I put the Else in there to see if the macro was being run at all. Th
macro Write_time will only be called if I am entering text in th
worksheet and hit enter while cell (14,7) is true. If I save the macr
and watch the worksheet the cell (8,5) will not turn purple until
enter text and hit enter?!? I have been all over the web and throug
the "VB in 24 hours" book to no avail. ANY shove in the right directio
would be greatly appreciated

 

[mudraker]

longzoo


One way


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$G$7" Then
If Target.Value = True Then
Call Write_time
End If
End If
End Su

 

[Frank Kabel]

Hi
as a workaround try the following (problem is your cell is feeded).:
- create a helper cell which references your target cell with a formula
(e.g. =G14*2)
- use the worksheet_calculate event to get a trigger

See http://www.cpearson.com/excel/events.htm
for more information

 

[longzoo]

this has the same result it only runs the macro if I am activel
entering data on the worksheet. Thanks for the suggestion though

 

[longzoo]

I have tried the helper cell as you suggested to no avail. The link yo
sent has some good info, It does explain why the macro will run when
physically change a cell in the workbook (since the call command i
under sub Worksheet_change) but I need to look further into it to se
how to make it automatic, ie without human intervention of a cell.
Thanks

Hi
as a workaround try the following (problem is your cell is feeded).:
- create a helper cell which references your target cell with
formula
(e.g. =G14*2)
- use the worksheet_calculate event to get a trigger

See http://www.cpearson.com/excel/events.htm
for more information

 

[Frank Kabel]

Hi
to give you an example.
- Say cell A1 contains your automatic feeded value
- cell B1 contains a formula like =A1+1

Use the following code in your worksheet module:

Private Sub Worksheet_Calculate()
If Cells(14, 7).value = True Then
application.enableevents=false
Call Write_time
application.enableevents=true
end if
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

I have tried the helper cell as you suggested to no avail. The link you
sent has some good info, It does explain why the macro will run when I
physically change a cell in the workbook (since the call command is
under sub Worksheet_change) but I need to look further into it to see
how to make it automatic, ie without human intervention of a cell.
Thanks

Hi
as a workaround try the following (problem is your cell is feeded).:
- create a helper cell which references your target cell with a
formula
(e.g. =G14*2)
- use the worksheet_calculate event to get a trigger

See http://www.cpearson.com/excel/events.htm
for more information

 

[longzoo]

Mr. Kabel you are a lifesaver!! The piece that I was missing wa
Worksheet_Calculate() once I changed Worksheet_Change() t
Worksheet_Calculate() it worked flawlessly! This is for my senio
project that is due in 2 weeks and I have been dancing all around thi
problem for a week now. My advisor was unable to give me any positiv
direction and I was about start all over and make my HMI in Labview :


Thanks so much,
Dave

Hi
to give you an example.
- Say cell A1 contains your automatic feeded value
- cell B1 contains a formula like =A1+1

Use the following code in your worksheet module:

Private Sub Worksheet_Calculate()
If Cells(14, 7).value = True Then
application.enableevents=false
Call Write_time
application.enableevents=true
end if
End Sub

 

[Frank Kabel]

Hi Dave
thanks for the feedback