Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
!
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. Lostguy wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
!
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? "Lostguy" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
!
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 "Charlie" wrote in message ... 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? "Lostguy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|