Time Looped Data Acquisition - Command Button

[janczar]

Greetings all,

I posted a few days ago about this problem, with no responses, but I probably wasn't direct enough if my explanation. So I have a working code which sends a signal to a laboratory balance. Then the balance sends back the signal and excel places it into a cell along with a time stamp. The way the program works now is it is initiated by a command button. I'm looking for a way to put the process on a loop so that the command button is automatically clicked every 30 seconds or so. Or just do away with the command button and have the program loop on its own. I've been trying to use Application.OnTime however I'm not exactly sure where in my code to put it. When I put it in the CommandButton1_Click sub, the code seems to want to work (I click the button and it records data, then 30 seconds later it tries to run again but an error message occurs stating "Cannot run the macro ________. The macro may not be available in this workbook or all macros may be turned off." I've been trying alot of different things with no luck. Any help would be greatly appreciated. The first, short section here is under Microsoft Excel Objects/Sheet 1 (Results), and the second portion is under Forms/UserForm1.

----------------------------------------------------------------

Please Login or Register  to view this content.

[JoeFoot]

the OnTim application is very finicky when it comes to permission. you might be having a issue because the macro is tied in to the sheet. Try this:

Please Login or Register  to view this content.

now create a module and insert this code (don't place it in the sheet)

Please Login or Register  to view this content.

the schedule is set to false on the first bit of code just so it runs once to call up the update_timer which will then cycle indefinitely. The key difference is it will be the update_timer macro to cycle, not the private commandbutton1_click

I've worked with OnTime in the past and this thing is really moody. Give this a try and see how it goes.

[janczar]

Well, I gave it a try, placing the suggested code in CommandButton1_Click Sub, and the second bit of code in a module (Module 1). The first data point does not appear, resulting in an error: "Run-time error '1004':
Method 'OnTime' of object'_Application' failed. Any thoughts from here? Should the schtime be alerttime instead? In changing that nothing at all seems to happen when I press the command button.

[JoeFoot]

oh yeah my bad.

replace schtime with alerttime

[JoeFoot]

here it is fixed:

Please Login or Register  to view this content.

now create a module and insert this code (don't place it in the sheet)

Please Login or Register  to view this content.

[JoeFoot]

When you press the commandbutton he should run the userform1.requestbalancedata at least 1 time.

try setting the first schedule :=True instead of false

on a separate note it did seem that your first problem was due to private macro permission.