Sub Macro vrs Function Macro Auto Start

[Pat]

I have data where an end user put's scores in & this is linked to a workbook
called Team # 1 scores. This updates a sheet "Team Scores Weekly which
updates another sheet in the same workbook "High Game Scores". I created a
macro to copy & sort the High Game Scores from High to Low which works fine
when I click the button. I want this automatically sort as Scores are
entered each week by end user. I changed the macro to a Function. After
reading other questions made a new macro & used the function & macro together
got a value error & excel encounterd a problem & kicked me out. I know its
me. I read about Workbook_open etc from all the other questions. I'm new so
?? Please help.

 

[JLatham]

Pat, I'm a bit confused, so I need to ask you to restate the problem You
make mention of two workbooks. My question is:
*What* do you want to happen in *which workbook* when an end user enters new
scores?

The Workbook_Open() event processor is associated with an individual
workbook. Whatever code you put into it will be executed when the workbook
is opened, each time it is opened.

To have some code run each time someone makes a change to data on a
worksheet you would want to use a the Worksheet_Change() event processor. To
get into the area to use that code, right-click on the sheet's name tab and
choose [View Code] from the list.

There are 2 main differences between a Sub and a Function. A Function
(normally) returns a value and a Function (usually) can be referenced in a
formula on a worksheet by its name. A Sub doesn't have those abilities.

Also, if you publish your code here, we may be able to look at it and
determine why you're getting errors. Don't worry - we've all seen attempts
at coding by novices before (we were all a novice at one time ourselves) that
didn't quite get the job done.

 

[Pat]

JLatham thanks for your fast response & "Your" assurance that I am not as
dumb as I think. To answer your questions. When a end user enters scores
everything is linked so the other"Team Scores Weekly" workbook is updated &
this updates the High Scores Sheet. The end user will not in most instances
open the workbook Team Scores Weekly. I do need the High Game Scores to
update Weekly as I have another report that will need the data from the High
Game Scores. 1 of my tries to make this work is:
=Personal.xls!SortScoresFile(),Personalxls!Module8.TmSortScores (this returns
a Value Error)
I tried to make a copy of my macro to paste but could not. So I will type it:
Module8 -1
Sub Workbook_open__()
End Sub
Sub TMSORSCORES()
'
'
'TMSORTSCORES Macro
'Macro recorded 06/05/2007 by Pat
'
Range ("a11:d218").Select
Selection. Copy
Range ("f11").Select
Activesheet.paste
Application.cutcopymode = False
Selection.Sort Key1:=Range("i11"), Order 1:=xlDescending,
Key2:=Range("g11'),
Order2:=xlAscending, Header:=xlguess, OrderCustom:=1. MatchCase_:=False,
Orientation:=xlTopToBottom, DataOptionl:=xlSortNormal, _ Data
Option2:=xlSortNormal
End Sub

I hope this answered your questions. "ALL" help is appreciated.

Pat, I'm a bit confused, so I need to ask you to restate the problem You
make mention of two workbooks. My question is:
*What* do you want to happen in *which workbook* when an end user enters new
scores?

The Workbook_Open() event processor is associated with an individual
workbook. Whatever code you put into it will be executed when the workbook
is opened, each time it is opened.

To have some code run each time someone makes a change to data on a
worksheet you would want to use a the Worksheet_Change() event processor. To
get into the area to use that code, right-click on the sheet's name tab and
choose [View Code] from the list.

There are 2 main differences between a Sub and a Function. A Function
(normally) returns a value and a Function (usually) can be referenced in a
formula on a worksheet by its name. A Sub doesn't have those abilities.

Also, if you publish your code here, we may be able to look at it and
determine why you're getting errors. Don't worry - we've all seen attempts
at coding by novices before (we were all a novice at one time ourselves) that
didn't quite get the job done.

I have data where an end user put's scores in & this is linked to a workbook
called Team # 1 scores. This updates a sheet "Team Scores Weekly which
updates another sheet in the same workbook "High Game Scores". I created a
macro to copy & sort the High Game Scores from High to Low which works fine
when I click the button. I want this automatically sort as Scores are
entered each week by end user. I changed the macro to a Function. After
reading other questions made a new macro & used the function & macro together
got a value error & excel encounterd a problem & kicked me out. I know its
me. I read about Workbook_open etc from all the other questions. I'm new so
?? Please help.

 

[Pat]

JLatham after I sent my last response to you to answer your questions I
thought maybe I am going about this wrong and making it harder. What I have
is a sheet that has:
(A) (B) (C) (D)
Week # Date Bowled Game # Score
I want this data to move to (F), (G), (H),(I), on the same sheet with the
Highest Scores 1st down. Is there a formula I can use instead ? I need the
data to remain as is in A,B,C,D so I can get the Weekly Scores, and the data
in F,G,H,I so I can get Season Scores by dates.
Thanks again,

Pat, I'm a bit confused, so I need to ask you to restate the problem You
make mention of two workbooks. My question is:
*What* do you want to happen in *which workbook* when an end user enters new
scores?

The Workbook_Open() event processor is associated with an individual
workbook. Whatever code you put into it will be executed when the workbook
is opened, each time it is opened.

To have some code run each time someone makes a change to data on a
worksheet you would want to use a the Worksheet_Change() event processor. To
get into the area to use that code, right-click on the sheet's name tab and
choose [View Code] from the list.

There are 2 main differences between a Sub and a Function. A Function
(normally) returns a value and a Function (usually) can be referenced in a
formula on a worksheet by its name. A Sub doesn't have those abilities.

Also, if you publish your code here, we may be able to look at it and
determine why you're getting errors. Don't worry - we've all seen attempts
at coding by novices before (we were all a novice at one time ourselves) that
didn't quite get the job done.

I have data where an end user put's scores in & this is linked to a workbook
called Team # 1 scores. This updates a sheet "Team Scores Weekly which
updates another sheet in the same workbook "High Game Scores". I created a
macro to copy & sort the High Game Scores from High to Low which works fine
when I click the button. I want this automatically sort as Scores are
entered each week by end user. I changed the macro to a Function. After
reading other questions made a new macro & used the function & macro together
got a value error & excel encounterd a problem & kicked me out. I know its
me. I read about Workbook_open etc from all the other questions. I'm new so
?? Please help.

 

[Pat]

JLatham - I did respond to your questions and now I am wondering if I did get
them to you since they are not on the discussion page. Maybe they came
directly to you?
Or did I not do something incorrect and they were deleted ? Please advise.
Thanks .

 

[Pat]

JLatham - I responded to your questions within 30 min after you asked and I
did something wrong as it has never posted. I'll try again. When the end
user enters scores in "Weekly Scores" it updates the others. The end user
would in most instances not open the other work book till the end of the
season. I need the High Page Scores to automatically move & sort as I have
another report that will pull its data from the High Game Scores Sheet. I
may be going about this in the wrong way Is it possible to do the same thing
with a "Formula" ?
What I have on the High Games Sheet Is:
(A) (B) (C) (D)
Week # Date Bowled Game # Score

I need this data to move on the same sheet to (F),(G),(H),(I) sorting it
into Score being High to Low. I need to keep the data in A,B,C,D as is
because I need it for a weekly report and the data in F,G,H,I for a Season
Report.
The latest formula I tried is:
=PERSONAL.XLS!SORTSCORESFILE(),PERSONALXLS!MODULE8.TMSORTSCORES which gets me
a "VALUE ERROR".
I tried to copy the macro that I have & did not know how to do it so I am
typing it for you:
Module8 - 1
Sub Workbook_open__()
End Sub
Sub TMSORTSCORES()
'
' TMSORTSCORES Macro
' Macro recorded 6/5/2007 by pat
'

'
Range ("A11218"). Select
Selection.Copy
Range ("F11"). Select
Activesheet.Paste
Application.CutCopyMode= False
Selection.Sort Key1:=Range("I11"), Order1:=x1Decending,
Key2:=Range("G11"),
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False,
Orientation:=xlTopToBottom, DataOptional"=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub
Any help you can give is "APPRECIATED" also Thanks for trying to make me
not feel Dumb --that was very nice of you.
Pat

Pat, I'm a bit confused, so I need to ask you to restate the problem You
make mention of two workbooks. My question is:
*What* do you want to happen in *which workbook* when an end user enters new
scores?

The Workbook_Open() event processor is associated with an individual
workbook. Whatever code you put into it will be executed when the workbook
is opened, each time it is opened.

To have some code run each time someone makes a change to data on a
worksheet you would want to use a the Worksheet_Change() event processor. To
get into the area to use that code, right-click on the sheet's name tab and
choose [View Code] from the list.

There are 2 main differences between a Sub and a Function. A Function
(normally) returns a value and a Function (usually) can be referenced in a
formula on a worksheet by its name. A Sub doesn't have those abilities.

Also, if you publish your code here, we may be able to look at it and
determine why you're getting errors. Don't worry - we've all seen attempts
at coding by novices before (we were all a novice at one time ourselves) that
didn't quite get the job done.

I have data where an end user put's scores in & this is linked to a workbook
called Team # 1 scores. This updates a sheet "Team Scores Weekly which
updates another sheet in the same workbook "High Game Scores". I created a
macro to copy & sort the High Game Scores from High to Low which works fine
when I click the button. I want this automatically sort as Scores are
entered each week by end user. I changed the macro to a Function. After
reading other questions made a new macro & used the function & macro together
got a value error & excel encounterd a problem & kicked me out. I know its
me. I read about Workbook_open etc from all the other questions. I'm new so
?? Please help.

 

[Pat]

I've posted two replies to you, but neither have shown up. They both said
"Your post was successful". Not sure what happened.
Thanks, Pat

Pat, I'm a bit confused, so I need to ask you to restate the problem You
make mention of two workbooks. My question is:
*What* do you want to happen in *which workbook* when an end user enters new
scores?

The Workbook_Open() event processor is associated with an individual
workbook. Whatever code you put into it will be executed when the workbook
is opened, each time it is opened.

To have some code run each time someone makes a change to data on a
worksheet you would want to use a the Worksheet_Change() event processor. To
get into the area to use that code, right-click on the sheet's name tab and
choose [View Code] from the list.

There are 2 main differences between a Sub and a Function. A Function
(normally) returns a value and a Function (usually) can be referenced in a
formula on a worksheet by its name. A Sub doesn't have those abilities.

Also, if you publish your code here, we may be able to look at it and
determine why you're getting errors. Don't worry - we've all seen attempts
at coding by novices before (we were all a novice at one time ourselves) that
didn't quite get the job done.

I have data where an end user put's scores in & this is linked to a workbook
called Team # 1 scores. This updates a sheet "Team Scores Weekly which
updates another sheet in the same workbook "High Game Scores". I created a
macro to copy & sort the High Game Scores from High to Low which works fine
when I click the button. I want this automatically sort as Scores are
entered each week by end user. I changed the macro to a Function. After
reading other questions made a new macro & used the function & macro together
got a value error & excel encounterd a problem & kicked me out. I know its
me. I read about Workbook_open etc from all the other questions. I'm new so
?? Please help.

 

[Pat]

I've been trying 2 days to reply to you. It said it posted, but is not
showing up. I can't find any post ANYWHERE for 6/6/07. Is there a problem
in the Discussion software?

Thanks,
Pat

Pat, I'm a bit confused, so I need to ask you to restate the problem You
make mention of two workbooks. My question is:
*What* do you want to happen in *which workbook* when an end user enters new
scores?

The Workbook_Open() event processor is associated with an individual
workbook. Whatever code you put into it will be executed when the workbook
is opened, each time it is opened.

To have some code run each time someone makes a change to data on a
worksheet you would want to use a the Worksheet_Change() event processor. To
get into the area to use that code, right-click on the sheet's name tab and
choose [View Code] from the list.

There are 2 main differences between a Sub and a Function. A Function
(normally) returns a value and a Function (usually) can be referenced in a
formula on a worksheet by its name. A Sub doesn't have those abilities.

Also, if you publish your code here, we may be able to look at it and
determine why you're getting errors. Don't worry - we've all seen attempts
at coding by novices before (we were all a novice at one time ourselves) that
didn't quite get the job done.

I have data where an end user put's scores in & this is linked to a workbook
called Team # 1 scores. This updates a sheet "Team Scores Weekly which
updates another sheet in the same workbook "High Game Scores". I created a
macro to copy & sort the High Game Scores from High to Low which works fine
when I click the button. I want this automatically sort as Scores are
entered each week by end user. I changed the macro to a Function. After
reading other questions made a new macro & used the function & macro together
got a value error & excel encounterd a problem & kicked me out. I know its
me. I read about Workbook_open etc from all the other questions. I'm new so
?? Please help.