Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-run a script?
I have written an excel program that is used for ordering clothing for my
company. When we receive a bill I have to tabulate the purchase totals by employee in order to deduct the proper amount from paycheck. I have macros to do this automatically but in order to run them I have to go through what seems like 2 minutes of keystrokes and clicks, just seems like there should be a way to do it automatically. As of right now, to run my macros the way I need to, I have to go into my "billing" worksheet, click on the first cell in my form (A5), and run a macro "moveuniquenames". then I have to click another cell halfway down the form (A25) for embroidery items, and run another macro, "moveuniquenamesemb". If I make any changes to the order once it is billed (sometimes there are errors or un-announced pricing changes from our supplier that have to get corrected after-the-fact), I have to re-run the macros. What would I have to do so that every time I click on the "billing" tab to open the workseet, these macros are automatically run, where they need to be, so I am looking at current info every time I view the form? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-run a script?
the workbook has a sheetactivate event.
go to the IDE in the project view, double click the 'ThisWorkbook' object to open the code page. In the code page select the 'Workbook' object and in the methods, select the SheetActivate method. The IDE will create Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub for you. add a call to your "macro" within this....eg Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call MyMacro End Sub note that the CALL word is NOT required - i left it there for this to show an example "sycsummit" wrote: I have written an excel program that is used for ordering clothing for my company. When we receive a bill I have to tabulate the purchase totals by employee in order to deduct the proper amount from paycheck. I have macros to do this automatically but in order to run them I have to go through what seems like 2 minutes of keystrokes and clicks, just seems like there should be a way to do it automatically. As of right now, to run my macros the way I need to, I have to go into my "billing" worksheet, click on the first cell in my form (A5), and run a macro "moveuniquenames". then I have to click another cell halfway down the form (A25) for embroidery items, and run another macro, "moveuniquenamesemb". If I make any changes to the order once it is billed (sometimes there are errors or un-announced pricing changes from our supplier that have to get corrected after-the-fact), I have to re-run the macros. What would I have to do so that every time I click on the "billing" tab to open the workseet, these macros are automatically run, where they need to be, so I am looking at current info every time I view the form? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-run a script?
forgive my rudimentary understanding of this; I don't follow.
-I don't know what IDE is. -I can find the code window for the billing worksheet (right click the tab - view code) and I found the 'Workbook' object and the SheetActivate method in the Object Browser, but not sure how to create the code snippet you described. I just copied and pasted: "Private Sub Workbook_SheetActivate(ByVal Sh As Object) MoveUniqueNames MoveUniqueNamesEmb End Sub" (with the names of my 2 macros replacing your "Call MyMacro"), and nothing happens. so that's where I'm at... could you get me from here to there? "Patrick Molloy" wrote: the workbook has a sheetactivate event. go to the IDE in the project view, double click the 'ThisWorkbook' object to open the code page. In the code page select the 'Workbook' object and in the methods, select the SheetActivate method. The IDE will create Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub for you. add a call to your "macro" within this....eg Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call MyMacro End Sub note that the CALL word is NOT required - i left it there for this to show an example "sycsummit" wrote: I have written an excel program that is used for ordering clothing for my company. When we receive a bill I have to tabulate the purchase totals by employee in order to deduct the proper amount from paycheck. I have macros to do this automatically but in order to run them I have to go through what seems like 2 minutes of keystrokes and clicks, just seems like there should be a way to do it automatically. As of right now, to run my macros the way I need to, I have to go into my "billing" worksheet, click on the first cell in my form (A5), and run a macro "moveuniquenames". then I have to click another cell halfway down the form (A25) for embroidery items, and run another macro, "moveuniquenamesemb". If I make any changes to the order once it is billed (sometimes there are errors or un-announced pricing changes from our supplier that have to get corrected after-the-fact), I have to re-run the macros. What would I have to do so that every time I click on the "billing" tab to open the workseet, these macros are automatically run, where they need to be, so I am looking at current info every time I view the form? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-run a script?
the IDE is the development environment...ALF+F11 will also open it.
can you see the project view? if not, use the menu 'VIEW' and select 'Project Explorer' you'll see your workbook as a project. under its Excel Objects folder, you'll see a node called 'ThisWorkbook'. Double clicking this oprn its code window, or you can right-click and select 'Code' "sycsummit" wrote: forgive my rudimentary understanding of this; I don't follow. -I don't know what IDE is. -I can find the code window for the billing worksheet (right click the tab - view code) and I found the 'Workbook' object and the SheetActivate method in the Object Browser, but not sure how to create the code snippet you described. I just copied and pasted: "Private Sub Workbook_SheetActivate(ByVal Sh As Object) MoveUniqueNames MoveUniqueNamesEmb End Sub" (with the names of my 2 macros replacing your "Call MyMacro"), and nothing happens. so that's where I'm at... could you get me from here to there? "Patrick Molloy" wrote: the workbook has a sheetactivate event. go to the IDE in the project view, double click the 'ThisWorkbook' object to open the code page. In the code page select the 'Workbook' object and in the methods, select the SheetActivate method. The IDE will create Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub for you. add a call to your "macro" within this....eg Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call MyMacro End Sub note that the CALL word is NOT required - i left it there for this to show an example "sycsummit" wrote: I have written an excel program that is used for ordering clothing for my company. When we receive a bill I have to tabulate the purchase totals by employee in order to deduct the proper amount from paycheck. I have macros to do this automatically but in order to run them I have to go through what seems like 2 minutes of keystrokes and clicks, just seems like there should be a way to do it automatically. As of right now, to run my macros the way I need to, I have to go into my "billing" worksheet, click on the first cell in my form (A5), and run a macro "moveuniquenames". then I have to click another cell halfway down the form (A25) for embroidery items, and run another macro, "moveuniquenamesemb". If I make any changes to the order once it is billed (sometimes there are errors or un-announced pricing changes from our supplier that have to get corrected after-the-fact), I have to re-run the macros. What would I have to do so that every time I click on the "billing" tab to open the workseet, these macros are automatically run, where they need to be, so I am looking at current info every time I view the form? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto-run a script?
Got it! Thanks!
"Patrick Molloy" wrote: the IDE is the development environment...ALF+F11 will also open it. can you see the project view? if not, use the menu 'VIEW' and select 'Project Explorer' you'll see your workbook as a project. under its Excel Objects folder, you'll see a node called 'ThisWorkbook'. Double clicking this oprn its code window, or you can right-click and select 'Code' "sycsummit" wrote: forgive my rudimentary understanding of this; I don't follow. -I don't know what IDE is. -I can find the code window for the billing worksheet (right click the tab - view code) and I found the 'Workbook' object and the SheetActivate method in the Object Browser, but not sure how to create the code snippet you described. I just copied and pasted: "Private Sub Workbook_SheetActivate(ByVal Sh As Object) MoveUniqueNames MoveUniqueNamesEmb End Sub" (with the names of my 2 macros replacing your "Call MyMacro"), and nothing happens. so that's where I'm at... could you get me from here to there? "Patrick Molloy" wrote: the workbook has a sheetactivate event. go to the IDE in the project view, double click the 'ThisWorkbook' object to open the code page. In the code page select the 'Workbook' object and in the methods, select the SheetActivate method. The IDE will create Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub for you. add a call to your "macro" within this....eg Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call MyMacro End Sub note that the CALL word is NOT required - i left it there for this to show an example "sycsummit" wrote: I have written an excel program that is used for ordering clothing for my company. When we receive a bill I have to tabulate the purchase totals by employee in order to deduct the proper amount from paycheck. I have macros to do this automatically but in order to run them I have to go through what seems like 2 minutes of keystrokes and clicks, just seems like there should be a way to do it automatically. As of right now, to run my macros the way I need to, I have to go into my "billing" worksheet, click on the first cell in my form (A5), and run a macro "moveuniquenames". then I have to click another cell halfway down the form (A25) for embroidery items, and run another macro, "moveuniquenamesemb". If I make any changes to the order once it is billed (sometimes there are errors or un-announced pricing changes from our supplier that have to get corrected after-the-fact), I have to re-run the macros. What would I have to do so that every time I click on the "billing" tab to open the workseet, these macros are automatically run, where they need to be, so I am looking at current info every time I view the form? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Script to Auto pick next | Excel Worksheet Functions | |||
Auto refresh for VB script to take effect. | Excel Programming | |||
Auto Date Script.. | Excel Discussion (Misc queries) | |||
auto date script for 2 columns? have 1.. | Excel Discussion (Misc queries) | |||
Making a auto website login script | Excel Programming |