Converting Private Sub to Public Function

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,270
Office Version
2013
Platform
Windows
@ctackett6407 - You already had all the elements of code that you needed to do what you want. All you needed to do was move your 'Run All' button code to a public code module, remove the 'Private' from the macro name (and for cosmetic purposes, maybe remove the '_Click'), then put the macro name into the Windows scheduler without the parenteses.. Your button code will serve as a batch file to initiate the other macros that do the various tasks.
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
@JLGWhiz let me give that a try. I'm literally learning as I go and reading anything online that I think fits into what I'm trying to do. Thanks for all the help folks as I try to get this working.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
771
Office Version
2013
Platform
Windows
@ctackett6407 put the macro name into the Windows scheduler without the parenteses.
Just an observation... how would Windows Task Scheduler know what file to open and how would WTS be able to launch the desired "public" macro?
WTS accepts almost everything you type in without an error message, but if it cannot be executed then nothing will happen.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
I'm going to have to reread this thread and regroup, I got myself lost in what I'm doing. lol

but I do thank people for the help.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,270
Office Version
2013
Platform
Windows
Just an observation... how would Windows Task Scheduler know what file to open and how would WTS be able to launch the desired "public" macro?
WTS accepts almost everything you type in without an error message, but if it cannot be executed then nothing will happen.
If you go back to the beginning of this thread, the OP states that the button macro then calls other macros which do all the work. So the only code the scheduler needs to call is the button macro, converted to a public procedure in a public module. That macro will then call the other necessary procedures to do the tasks.. The problem the OP is having is getting the right procedure name in the scheduler and having that procedure in the correct code module so the scheduler can find it. There is not a problem with the code, the problem is getting the scheduler to call the code. The OP did not understand what comprises a procedure name and the nuances of _Click events, etc. But once they get that straightened out, it should work.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
771
Office Version
2013
Platform
Windows
I understand what the OP wants, but you simply cannot enter a macro name in WTS. You can (because you can type anything you want) but the macro will never be executed. However, you can schedule a VB script in WTS and run the macro using this VB script.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Example of running an access VBA function using WTS:

See Post 6.

Other ways of using WTS are of course possible. This could probably be adapted to run a macro pretty easily (my example runs a VBA Sub or Function in a public module).
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,270
Office Version
2013
Platform
Windows
I understand what the OP wants, but you simply cannot enter a macro name in WTS. You can (because you can type anything you want) but the macro will never be executed. However, you can schedule a VB script in WTS and run the macro using this VB script.
Well, I won't get hung up on semantics. If the user follows the menu to create a task, then the task should run when scheduled. There are tutorials on the web to show how to create a task. And that is the part the OP is having difficulty with. I am sure he would appreciate any help along those lines.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,270
Office Version
2013
Platform
Windows
This is a cut down version of the turtorial for adding a VBA macro to the scheduler. You can try it and see if it takes.

1. Get your macro into the correct code module and correctely named.
2. Open task scheduler (Control Panel>Administrative Tools>Task Scheduler)
3. Select Create Basic Task and type a name for the task (not macro name)
4. Click Next button
5. You can now select an exact time for the macro to run or "When I Log On"
a.Select 'Daily'
6. Click Next button
7. Enter the date and time to start running the macro, It will then run every day there after.
8. Click Next button
9. Select 'Start a program'
10. Click Next button
11 Enter the macro name enclosed in quotation marks ( "macroname") in the Program box.
a. In the Add arguments box enter the full path to the file holding the macro in quotation marks.( "C:\lev1\lev2\myFile.xlsm")
12. Click OK
You won't be able to locate the macro using the browse button at step ll because it only goes to the file folder level for Excel files. You haveto type in the macro name.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Thank you for this, I'm revisiting it today as it got myself confused and I had to step away and work on something else. I really thank you all for replying.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,013
Messages
5,508,788
Members
408,694
Latest member
LightBright

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top