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?

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?

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?

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?

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?