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!
This isn't about auto_open autorun macro
The OnEntry Event is not really available anymore, that's why you have to add it that way the Auto_open macro. Use the current and supported Event:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Debug.Print Target.Address, "Your Code Goes Here"
Application.EnableEvents = True
End Sub
and put your code there. This event fires by any change on the worksheet. This does however mean that if you change anything on the worksheet in the macro, the macro gets called again, that is why I put the Application.EnableEvents lines in to stop a possible infinte loop.
andycr, thank you very much
andycr, thank you very much for your help!
About what you said that the OnEntry Event, even if you say that it's not available anymore, the macro was working pretty well. We more specifically it's working! My only problem is that it only work when I change the cell I set in the macro in a normal way (entry information direct on the cell) and I need it to active the macro on hidden entry (copy and paste), so I really don't get how the event could not be available.
And I'm sorry, but I really wasn't able to use the command you suggested.
You set a variable Target, the command Target.Address and "Your Code Goes Here".
I didn't understand how to organize this macro.
I tried to make it:
Private Sub Worksheet_Change()
Application.EnableEvents = False
Debug.Print ThisWorkbook.Worksheets("DMP"), DidCellsChange
Application.EnableEvents = True
End Sub
Where "DidCellsChange" is my macro that call the Range "A1"...
I also tried:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Debug.Print Target.Address, "DidCellsChange"
Application.EnableEvents = True
End Sub
Well basically I didn't understand how it work... sorry, I would really appreciate if you could explain a little better how can I use it.
I forgot one thing...
I forgot to answer your Question about the OnEntry Routine/Event/Setting. Up until some ancient version of Excel this was the routine to use. It has since been superceeded by the "Change" possibility. The "OnEntry" is normally hidden, which is why you had to set it in the way you did with the .OnEntry = "..." line. The "OnEntry" is however only there for "Backward Compatibility" which means that excel only still suppports it so that older code in older files will still work without having to be changed.
Oh, where do I start?
There is a lot of the stuff I showed you gets done automatically by Excel.
The Worksheet_Change(...) gets created automatically. You obviously know how to get into the VBA Editor (I'll call it by its proper name now, VBIDE) and once in the VBIDE you seem to know your way around the Project Explorer.
So, in the Project explorer, you will find the "ThisWorkbook" Object where you created part of the code you have. Under this Object you will find one or more Objects which represent the code behind the Sheets you have in the workbook. In your case one of them will be called "DMP". Double click on this object to open up the code.
Under the Titlebar you will find two ComboBoxes, left and right, and they will probably say "General" and "Declarations", if they don't, don't worry about it. In the left one select "WorkSheet" and this will automatically create the following lines:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Which we don't need but, never mind. In the combobox on the right find and select "Change" and this will create the so called "Change" Event for your worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
It is into this routine you will copy the code which should react to the cell change. Either copy the code from the routine you have or simply insert a call to your routine:
Private Sub Worksheet_Change(ByVal Target As Range)
DidCellsChange
End Sub
oK?
Thank you very much
andycr, thank you very much!
It really helped a lot!
I was actually inputing the code in a module, that's probably the reason why it wasn't working. Didn't knew that I had to put it directly on worksheet command line.
It worked perfectly well. I still need a few lower adjustments to my code to work completely, but I can probably handle that by myself.
To make the code run just when a specific cell changes I just included a simple "If" command and the macro worked exactly as I needed it to do.
Once more, I really appreciate your help! Hope I can pay this favor anytime soon! Don't hesitate to ask If I can help with anything ^^
Best regards!
You're welcome,
You're welcome, I'm actually between contracts right now so I'm just keeping my skills sharp when I need a break from prospecting :D
So, keep me in mind if you need someone to do VBA/Access/Excel/SQL/... work in Germany ;-)
One more problem...
Hi andycr!
If you don't mind I would like to ask your advice once more.
It took me some time to be able to test in the real system (was trying just coping a random cell to test), but now that I'm was able to make the test with the real software and this software send the data to the excel file, but for some reason the excel don't recognize this input as something to activate the Change for the macro...
If you have any suggestion it would be great ^^
Thank you very much for the attention!
Best regards!
test with the real software
Hi, this might end up a bit disapointing...
You write "test with the real software" and "this software send the data to the excel file" which makes me suspect that "this software" might not actually be running Excel but manipulating the Data directly in the file. Or, it might take a precaution and turn off events if it does work through excel.
Could you explain the setup a bit moe detailed? Does "the real software" actually open the file in excel and then change the values? Do you have any control over "the real software" and how it works its magic?
Yours,
Hi, andycr! You are probably
Hi, andycr!
You are probably right about the way the software handle the excel data.
The software itself don't open the excel file, I need to open the excel file and "connect" the software with the excel sheet.
As for the way it send the data from the software to the excel I'm really not sure (magic probably...), and I have no control of the main system of the software.
I was afraid that it could be not possible, but asked anyway, maybe there could be a different way to program an auto activation that I didn't tried.
Well, I really appreciate your help!
Thank you for sharing your knowledge.
Best regard!
Don't give up
Now that you have described the way it works, I think you are still in with a chance.
So, You open the Excel file and then the "real software" pumps data into the cell(s). Is that what you are seeing?
Do you have to have a particular sheet open or a particular sheet name already there or do you configure the "real software" to say which cell it should write into?