!

[Lostguy]

Hello!

I am trying to create an Excel file which is macro-free (many of the
users click No on the "macros may cause viruses"warning.)

But there are so many great bell-and-whistles available that are only
doable via VBA.

Anyway, I found somebody's great code on the Internet:

Sub Test()
Dim ws As Worksheet
Dim result As String


result = ""


For Each ws In Worksheets
result = result & ws.Name & " is " & IIf(ws.ProtectContents,
"protected", "unprotected") & vbCr
Next ws


MsgBox result
End Sub

It loops through all the sheets in your workbook and gives you a mini-
printout of which sheets are protected and which aren't.

My workbook has 45 sheets and to edit them, I have to unprotect-edit-
then reprotect, so this is a good macro to make sure that I
reprotected everything before I send it out to my users.

It is possible to put the code outside of the workbook somewhere and
then run it on the workbook? I don't want to open the workbook and add
a module (because then you get the macro warning), so I would like to
put the macro somewhere (a second workbook?) and then run it on the
closed workbook to make sure that all the sheets are protected. If the
code also had a line about whether or not the workbook itself was
protected, that would be the bomb.

Any help appreciated

 

[Dave Peterson]

Yep.

In fact, lots of people put this kind of routine in a workbook named
personal.xls (or .xla or .xlsm or ...).

Then they store this macro workbook in their XLStart folder. Then each time you
open excel, excel will open this workbook. In fact, they usually hide this
workbook (window|Hide in xl2003 menus), then close excel so that they can save
the workbook in that hidden state.

Then you can hit alt-f8 (to see the run macro dialog) to run your macro.

If you plan on sharing your code with others, then don't use the personal.*
name. Since excel can only open one workbook with that name at a time, you'll
be making your users decide which utilities they really need.

Instead, you can name your utility macro workbook:
LostGuyUtils.xls

It can still go into your XLStart folder and the macros will be available all
the time.

If you decide to create an addin, you'll notice that the procedures aren't
visible in the alt-f8 dialog.

Instead, I'd create an addin and tell the users to open the addin when they need
the macro.

So you'll have to give the users a way to run the macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

 

[Charlie]

The easiest solution is to train your users to trust worksbooks from you and
to enable your macros! I mean, they did hire you to create and/or modify
their workbooks did they not?

 

[Tim Williams]

This will operate on the active workbook, so you can put it in (eg)
"personal.xls" and run it on any other workbook:

For Each ws In ActiveWorkbook.Worksheets
result = result & ws.Name & " is " & _
IIf(ws.ProtectContents, "protected", "unprotected") & vbCr
Next ws

Tim