Macros

[kevs]

Doing my first foray into Excel macros.

I called my macro called "zoom 200"
1)Excel said name is no good, what's the rule with that?

2)Excel defaults shortcut keys to option command, blank, I selected 1, and
then 2, In other words, option, command 1. Excel says:" shortcut keys must
be non-accented roman letter?" What is Excel trying to say?

3) If I chose "personal workbook", will the new macro be applied to all
Excel workbooks? That's my hope.

4) Is there a way to get macros on the toolbar?


Thanks!! any help appreciated.
Kevs

 

[Bernard Rey]

kevs wrote :

Doing my first foray into Excel macros.

I called my macro called "zoom 200"
1)Excel said name is no good, what's the rule with that?

No blanks in names. Name it "Zoom200" or "Zoom_200".

2)Excel defaults shortcut keys to option command, blank, I selected 1, and
then 2, In other words, option, command 1. Excel says:" shortcut keys must
be non-accented roman letter?" What is Excel trying to say?

In this case, clearly that it has to be a LETTER, between A and Z

3) If I chose "personal workbook", will the new macro be applied to all
Excel workbooks? That's my hope.

Personal Macros Workbook is a hidden workbook (no worksheet can normally be
seen) that open up on lauching. The macros in that workbook are always ready
to be used. I have a "protect every sheet" macro there, in order to be able
to protect all the sheets in the active workbook on one click.

4) Is there a way to get macros on the toolbar?

Yes. You can add a button in any toolbar, and then attach a macro to it. To
do that, chose the "Customize" item from the "Display > Toolbars" submenu.
In the pop-up Windows, "Commands" tab, pick the "Macros" line, and there you
should see a Smiley for "Custom button". Drag it to the Toolbar where you'd
like to have it. Now Control-click that button (in the toolbar) in order to
"Attach a Macro..."

 

[Francis Hookham]

kevs wrote :

2)Excel defaults shortcut keys to option command, blank, I selected 1, and
then 2, In other words, option, command 1. Excel says:" shortcut keys must
be non-accented roman letter?" What is Excel trying to say?

and Bernard Rey replied:

In this case, clearly that it has to be a LETTER, between A and Z

Both lower and upper case can be used so:

alt/cmd/A

is different from

alt/cmd/shift/A

The means there are plenty of key commands which can run macros (unless they
have been used elsewhere)

kevs
Be sure to locate your Personal Macro Workbook (Office/Startup/Excel) and
back it up from time to time - you will come to rely on it so much that it
is disastrous if you loose it - the same applies to your Excel Toolbars held
in the System Prefs (Microsoft) folder
Also have fun (and waste hours) editing the buttons so they are meaningful -
it's a whole new wonderful world out there

(Bernard - please can you post the "protect every sheet" macro?) (thanks, my
similar specific problem is now fully sorted - this macro might be of
general interest)

Francis Hookham

 

[Bernard Rey]

Francis Hookham wrote :

kevs
Be sure to locate your Personal Macro Workbook (Office/Startup/Excel) and
back it up from time to time -

A good tip (another Mac-only feature!) is to put the Personal Macro Workbook
in a safer place and put an alias in it's place.

(Bernard - please can you post the "protect every sheet" macro?)

Here it is. Note that I certainly "borrowed" most of it somewhere, but have
no idea where. It's quite some time ago, now. I also pasted the
corresponding "Unprotect" macro, both of them being linked to buttons on my
toolbar


Sub ProtectAll()
Dim MyPassword As String, ws As Worksheet, I%

'MyPassword = "anything" ' Facultative, of course
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
I = I + 1
Sheets(I).Protect MyPassword
Next
Application.ScreenUpdating = True

End Sub


Sub UnProtectAll()
Dim MyPassword As String, ws As Worksheet, I%
On Error GoTo Error

'MyPassword = "anything" ' Facultative too, of course
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
I = I + 1
Sheets(I).Unprotect MyPassword
Next
Application.ScreenUpdating = True
Exit Sub

Error:
Application.ScreenUpdating = True
Sheets(I).Select
MsgBox "Invalid Password! This sheet and the next couldn't be
unprotected.", vbInformation
End Sub

 

[kevs]

Francis Hookham wrote :


A good tip (another Mac-only feature!) is to put the Personal Macro Workbook
in a safer place and put an alias in it's place.



Here it is. Note that I certainly "borrowed" most of it somewhere, but have
no idea where. It's quite some time ago, now. I also pasted the
corresponding "Unprotect" macro, both of them being linked to buttons on my
toolbar


Sub ProtectAll()
Dim MyPassword As String, ws As Worksheet, I%

'MyPassword = "anything" ' Facultative, of course
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
I = I + 1
Sheets(I).Protect MyPassword
Next
Application.ScreenUpdating = True

End Sub


Sub UnProtectAll()
Dim MyPassword As String, ws As Worksheet, I%
On Error GoTo Error

'MyPassword = "anything" ' Facultative too, of course
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
I = I + 1
Sheets(I).Unprotect MyPassword
Next
Application.ScreenUpdating = True
Exit Sub

Error:
Application.ScreenUpdating = True
Sheets(I).Select
MsgBox "Invalid Password! This sheet and the next couldn't be
unprotected.", vbInformation
End Sub

thanks:
Went over comments and everything is going great. Couple quick questions.

1) what is protect every sheet. I have no idea what that could be.

2) I got the macro on toolbar, but had to select text only -- unless you
want to see a smiley face for all your macros.

3) I guess the personal workbook would be the same as "normal" in word --
which I was backing up regularly. Thanks for tip. But what is in this
workbook that is so important? It seems most of my hard work ends up on the
toolbar.

4) you put the personal workbook where? And then you put an alias to that in
startup folder?

Thanks!!
Kevs

 

[Bernard Rey]

kevs wrote :

1) what is protect every sheet. I have no idea what that could be.

It's a macro that protects every sheet in the active workbook. It's just
much faster than going through the "Tools > Protect" process on each
worksheet, especially when there is plenty of them

2) I got the macro on toolbar, but had to select text only -- unless you
want to see a smiley face for all your macros.

You can Control-click on the Smiley, and pick the "Properties" line. There
you'll find you can choose (from a dropdown menu) other pictures for your
button. You can even Copy the picture, then Edit it with some third part
software (ie GraphicConverter) and then paste it back. I personally
preferred the way this was to be done in Excel:2001, but that's the way to
customize your buttons.

3) I guess the personal workbook would be the same as "normal" in word --
which I was backing up regularly. Thanks for tip. But what is in this
workbook that is so important? It seems most of my hard work ends up on
the toolbar.

It's not as important as the "Normal.doc" may be in Word. This is only
useful if you have any macros that you want to have ready to use on any
sheet. If you don't need such general purpose macros, you don't need the
"Personal Macros Workbook". Toolbar customization is something different an
it's saved in the "Excel Toolbars (10)" preference file, which should be
backed up as mentioned by Francis.

4) you put the personal workbook where? And then you put an alias to that
in startup folder?

I've placed it in a folder that I regularly (and nearly automatically) back
up. So I don't have to click trough the Application folders. But it's just
to make it easier (and therefore safer). I have an alias to that in the
startup folder instead and it works fine.

 

[Bernard Rey]

kevs wrote :

How would you copy a button into Photoshop and then paste back in. There is
a nice square button, I'd love to put some text on it, but I have no idea
how to get it into Photoshop and back into Excel. Thanks!

From the dropdown list where you picked the nice square button, click on the
"Copy Button Picture". Then paste it in Photoshop, modify it to your whish
and then copy it, Control-click your button in the toolbar, pick the
"Properties" line and, from the dropdown list again, "Paste Button Picture".