Autorun macro
Hi guys!
I'm new on the ExcelExperts and also new on VBA Excel programming, but I'm trying to do something pretty difficult to me so I came ask if someone have any idea of how I could do that.
Basically I need that a macro runs automatically when a specific cell is filled or considering the entire plan, it should run when the program stop sending information to the plan. For that I already made a researched and found a way using auto_open() using the following code:
Sub auto_open()
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Plan1").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "A1"
' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then Macro
End Sub
And it worked pretty well, the problem is that, I use a different program to fill the information in excel, and the macro active if I change the cell in the excel, but not when the program changes it...
It seems that the program makes a copy and paste to fill the cell, I tried just pasting the information on the marked cell (A1) and the macro really didn't run.
So my question is, does anybody knows if is there a way to make the macro automatically runs also when you paste the information in the cell?
I'm actually studying a way to use a timer including this code: Call Application.OnTime(Now + TimeValue("00:00:01"), "Macro") so the macro should run and check if the cell is filled or not every second, but I still wasn't able to make it work the right way, and even If I can make it work it will probably make my process too heavy (this plan would run all day and it call a really big VBA program).
Well that's is it!
If you guys could bless me with some of your knowledge I would be very grateful.
Thank you for the attention and help,
Best regards!
Never give up
Sorry not explaining all before.
Indeed, I have configure in the software the name of the sheet (like: sheet1) and also configure the cell it will send the data to (if the software have a 10 lines information to send and I configure the cell A1, it will input data into cells A1 to cell A10). I can configure the position it will start and need to set the sheet it will send data to, but I don't have access to main programming of this software.
One more question
In your Worksheet's Change Event:
Private Sub Worksheet_Change(ByVal Target As Range)
'...
End Sub
can you put this line as the very first line after the function definition:
Debug.Print Target.Address
and then see if in the debug window (the one that gets shown/hidden when you hit Ctrl-G in the VBIDE) you get the addresses of the cells being printed out?
Test it by doing manual changes, then if you see $A$1 or similar being printed out then test it with the "real software". If you don't see anything when doing it manually then the function is in the wrong place or something else is wrong. If you see it when doing it by hand but don't see it when the other software is feeding the data then, the other software must be switching off events and maybe the suppliers of the software can tell you if there is a way to stop their software doing this.
As you said
In fact as you mentioned the manual test shows the cell I change in the debug window, but when I send the data to excel by the software it don't shows anything, it may be switching off events or sending the information to excel in a different way that VBA don't recognize.
I'll talk to my supplier again about this (although he said he didn't know when I asked before).
It's probably something that must be made by the software that supply the excel with data, so thank you for all your attention and help!
Best regard!
You're welcome,
Unfortunately, unless the supplier provides a way to trigger some "I changed this" event, or stop not triggering the normal events, the only way i can see it working is with a manual button to press or a timer (whichh could, however, make a real mess of things if it triggers at the wrong moment.
Thanks
Yeah, I also thought on using a timer, but it could trigger the macro in a wrong moment and we wouldn't have any control over it.
I'm using a manual button now, and if the supplier find a way to send a signal that VBA can recognize I'll change into automatic using this signal.
Once again!
Thank you for the help!