Global macro

[kevs]

I made a macro and was so excited about it. It really saves a lot of time.
But now I see a sign come up, it says, "personal macro workbook could not be
found"

The macro was working before on both new and old files. I have not changed
anything about my personal workbook.

Any ideas appreciated. Thanks!
Kevs

 

[kevs]

kevs wrote :


Do you get that message when you launch the macro (possibly attached to a
button or so)? If yes, try to re-link the macro to the button (control-click
on the button). Can you see the "'Personal Macros Workbook'!YourGreatMacro"
in the list?

Bernard:
I control clicked the button and here's what I found:
Assign macros: macros in all open workbooks.
The edit button is grayed out.
The macro name is, hard drive....personal macro workbook.... Then the name
of the specific macro.

And not only does the macro not work it button form, I just tried the
original keyboard shortcut I created, which worked before, now does not work
as well as the button not working.

 

[kevs]

This tends to prove the personal macros WB is open. I suppose you can select
it, but it doesn't run...



If you open the Visual Basic Editor ("Option-F11") you should see the
Personal Macros Workbook in the "Projects" Window. What happens if you run
the macro from here? Do you get any error message? Can you run any other
macro? Create a new one?

By the way, did you make sure the "virus protection" checkbox hasn't been
checked (preventing any macros to run)?

If this still brings you no help, you might post the code of your macro, so
we could check if it works at all?

Bernard,
Thanks.
Virus protection was checked. I unchecked it, but it did not help.

I opened visual basic editor, but did not see the words "personal macros
workbook" anywhere .

How do you post code of macro?

 

[Bernard Rey]

kevs wrote :

I opened visual basic editor, but did not see the words "personal
macros workbook" anywhere .

So you should first check if there is a Personal Macros Xorkbook in
"Applications/Microsoft Office X/Office/Startup/Excel" If there isn't, you
should search your HD to find where it is and place it back there.

How do you post code of macro?

Copy the lines from the code sheet and paste them in your message.

 

[kevs]

kevs wrote :


So you should first check if there is a Personal Macros Xorkbook in
"Applications/Microsoft Office X/Office/Startup/Excel" If there isn't, you
should search your HD to find where it is and place it back there.



Copy the lines from the code sheet and paste them in your message.

Thanks Bernard, yes in startup folder is the file called "workbook" , not
personal macros workbook, I open this and macros no longer work, and I get
the message that it can't find personal macro workbook.

When I select tools, macros, I get a box completely empty and edit is grayed
out. Hence, how, or where to I go to post the code?

Thanks!

 

[kevs]

If there's a "Workbook" there, it may be only a template to your "New"
workbooks. But certainly no "Personal Macros Workbook" which HAS to bear
that name. Search for that name on your hard disk. You may have moved it, so
if you find it, move it back.

If you don't find any, then you may have deleted it (maybe you reinstalled
Office, this is often the case, it happened to me some time too). In that
case... Well you'll have to start a new one from scratch, as you can
imagine, your macro is lost. Let's hope you have a back-up somewhere.



If you don't have the "Personal Macros Workbook" open, you can't find the
corresponding macros. Excel too can't find the macros, and that's why you
get prompted.



In this precise case, there is no need to post the code, as the problem
certainly lies in the missing "Personal Macros Workbook". But il any other
case simply copy and paste the code in a message. Just like this :

Sub MyNewMacro()
Selection.VerticalAlignment = xlCenter
End Sub

That's usually enough to find how it works (or doesn't work)...

Ok Bernard:
Let's talk about this personal macro workbook.

When I made these three macos I saved them to the "personal macro workbook"

Where this is, I have no idea. All I know is that the macros worked great
for about a week or so.

I have not reinstalled anything.

Now I have been backing up "workbook" in startup folder as suggested,
because someone mentioned this is where my macros are.

I also back up preferences, also.

So I don't get it. In other words, Excel, asked me if I'd like to back up
these macos in my personal macro workbook, and I selected yes, and the
macros worked.

Did Excel create a temporary personal macro workbook that times out in a
week?

Should I have created a master personal macro workbook? How does one do
this?

Is there a default personal macro workbook that has vanished? Thanks!

Kevs

 

[Bernard REY]

kevs wrote :

Let's talk about this personal macro workbook.

When I made these three macos I saved them to the "personal macro workbook"

Where this is, I have no idea. All I know is that the macros worked great
for about a week or so.

I have not reinstalled anything.

Now I have been backing up "workbook" in startup folder as suggested,
because someone mentioned this is where my macros are.

No. They are stored in a specific workbook which is (and must be) named
"Personal Macros Workbook". I suppose the "Workbook" you have there must be
a Template, and is used as such when you create a new workbook.

So I don't get it. In other words, Excel, asked me if I'd like to back up
these macos in my personal macro workbook, and I selected yes, and the
macros worked.

Did Excel create a temporary personal macro workbook that times out in a
week?

Excel did create a Personal Macro workbook, but it's not a temporary item
(or shouldn't be). It must have been moved, renamed or deleted.

Should I have created a master personal macro workbook? How does one do
this?

No, you have created it as mentioned just above: recording a new macro, and
asking Excel to save it in the personal Macros Workbook.

Is there a default personal macro workbook that has vanished? Thanks!

It has certainly been deleted or moved or renamed. The first thing to do is
to search you HD for it. In the Finder, type "Command-F". You'll get
prompted and asked for the string the name should contain. Type "personal
macro" (without the quotes), select "On local disks" from the drop down
menu, and see if it comes up.

If it's somewhere, place it back to where it belongs (In the startup folder)
and everything will be fine again If it doesn't show up, you can start
it all from the beginning (and so you'll see how Excel will create a new
"Personal Macros Workbook").

 

[Don Hohler]

Hello,

I have a question concerning macros in conversion from
Excel 2000 into Excel XP 2002. The macros worked fine in
Excel 2000 and for the most part most of my macros are
working perfectly except for one set of macros. I receive
the message of a run-time error '-2147417848 (80010108)':
Method 'Close' of object 'Window' failed. The VBA
debugger is pointing to a line of code highlighted in
yellow as follows:

Activewindow.Close savechanges:=False

Is there any facility to convert macros from Excel 2000
into the current VBA code in Excel XP 2002? Please help.

Best regards,

Don Hohler

 

[Bernard REY]

Don Hohler wrote :

I have a question concerning macros in conversion from
Excel 2000 into Excel XP 2002. The macros worked fine in
Excel 2000 and for the most part most of my macros are
working perfectly except for one set of macros. I receive
the message of a run-time error '-2147417848 (80010108)':
Method 'Close' of object 'Window' failed. The VBA
debugger is pointing to a line of code highlighted in
yellow as follows:

Activewindow.Close savechanges:=False

Is there any facility to convert macros from Excel 2000
into the current VBA code in Excel XP 2002? Please help.

It would probably be interesting to know which is the "activewindow" when
the macro crashed, in order to understand why it won't close. But I think
you should rather post your question to a Windows oriented group: this here
is a Macintosh dedicated group, and the VBA is still version 5.0 in the Mac
versions of Excel. In the latest Windows versions, it's 6.0.

Try microsoft.public.excel.programming

 

[Bob Greenblatt]

Hello,

I have a question concerning macros in conversion from
Excel 2000 into Excel XP 2002. The macros worked fine in
Excel 2000 and for the most part most of my macros are
working perfectly except for one set of macros. I receive
the message of a run-time error '-2147417848 (80010108)':
Method 'Close' of object 'Window' failed. The VBA
debugger is pointing to a line of code highlighted in
yellow as follows:

Activewindow.Close savechanges:=False

Is there any facility to convert macros from Excel 2000
into the current VBA code in Excel XP 2002? Please help.

Best regards,

Don Hohler

Don,

This is not a conversion issue, but instead a macro bug. What you probably
want to do is activeworkbook.close. If the workbook has more than one
window, closing it will not close the workbook, thus causing the macro
error.

 

[kevs]

Don,

This is not a conversion issue, but instead a macro bug. What you probably
want to do is activeworkbook.close. If the workbook has more than one
window, closing it will not close the workbook, thus causing the macro
error.

Ok Bernard:
I just created new macro, saved to personal macro workbook. I quit Excel and
relaunched, but I don't see a personal macro workbook in the startup folder.
Only the template "workbook" is there.

BTW, on the subject of my template, when I open it creates a copy, Hence,
how can one change the template????? Thanks!!!!

Thanks
Kevs

 

[Bernard Rey]

kevs wrote :

I just created new macro, saved to personal macro workbook. I quit Excel and
relaunched, but I don't see a personal macro workbook in the startup folder.
Only the template "workbook" is there.

I guess that either you didn't save it (you usually get prompted when
closing Excel) or you are not looking at the right folder. Search your HD,
it must be somewhere. If you did save it, it should be in the Microsoft
Office/Office/Startup/Excel folder.

BTW, on the subject of my template, when I open it creates a copy, Hence,
how can one change the template????? Thanks!!!!

Just the usual way: "Save as..." > "Template" format and browse to your
Startup/Excel folder. There you simply replace the previous template (don't
forget: the name must be "Workbook", with no ".xls" extension)