Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a code on a workbook?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a code on a workbook?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a code on a workbook?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to change code in a sheet and workbook module | Excel Programming | |||
copy and paste using code from workbook to workbook | Excel Discussion (Misc queries) | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
Code in one workbook to call code in another XL file | Excel Programming | |||
Reference code in another workbook from a calling workbook | Excel Programming |