Run a code on a workbook?

[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!

VR/

Lost

 

[marcus]

hey Lostguy

This will do the first part of your question, so you can put this in a
seperate workbook and it will open your original workbook and say
which sheets are protected. Just change the file name and path.

I will have a look into the workbook protection for you.

Take care

Marcus


Sub testopen()
Dim ws As Worksheet
Dim result As String
FName = "C:\open.xls"

Set oldbk = Workbooks.Open(Filename:=FName)
result = ""
For Each ws In Worksheets
result = result & ws.Name & " is " _
& IIf(ws.ProtectContents, "protected", "unprotected") & vbCr
Next ws
MsgBox result
oldbk.Close savechanges:=False

End With

End Sub

 

[marcus]

Ooops

I left an errant line in the code. Sorry about that.

Sub Testopen()
Dim ws As Worksheet
Dim result As String
FName = "C:\open.xls"

Set oldbk = Workbooks.Open(Filename:=FName)
result = ""
For Each ws In Worksheets
result = result & ws.Name & " is " _
& IIf(ws.ProtectContents, "protected", "unprotected") & vbCr
Next ws
MsgBox result
oldbk.Close savechanges:=False

End Sub