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 |
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 |
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 |
All times are GMT +1. The time now is 10:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com