Macros Help Needed, Thanks in Advance

  • Thread starter Thread starter chris felix
  • Start date Start date
C

chris felix

I have an Excel workbook with approximately 50 worksheets; I have been able
to enter into the VBA the following a) automatically name worksheets, b) sort
worksheets by name,and c) utilizing uppercase characters in specified areas.

My problem is with the first worksheet, which is a master inventory sheet, I
created a macro and appropriately used the shortcut "Ctrl A"; the Macro works
perfectly when the sheet is unprotected; the problems begin when I lock all
the sheets, for obvious reasons, then I get a message error.

Is there a way to by-pass this; I cannot leave the workbook unprotected, too
many issues will be created.

Thank you for all of your thoughts.
 
The first thing your macro should do it protect all the sheets with the
parameter "userinterfaceonly" set to true. That way, you're marking the
sheets as protected, but NOT for VBA stuff.
Alternatively, you can have the macro unprotect before it starts &
reprotects when it's done.
Bob Umlas
Excel MVP
 
I would build in language to unprotect the worksheet, then perform the rest
of your macro, then reprotect the worksheet.

Languange like:

Activesheet.protect

and

Activesheet.unprotect

You can get more specific about the types of protection, the VB help will
explain the details.

Tom
 
Good call, Tom!!
I use this procedure in some of my applications where I have to unprotect,
do what I need to do, then protect. I even assign a password when I protect
:)
 
Bob:

This is new to me. I checked it out, and I probably should have been using
this option for some of my applications. Seems like there is always
something to learn!!!
 
Appreciate your time, a little new at all of this so apologize in advance for
questions. When I run the macro I get the error, if I "debug" from there, is
this where I input the language you propose and if so where do I put the
password in.

I have copied my macro for you to look at and advise and again I thank you
for sharing your expertise.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/22/2009 by
'
' Keyboard Shortcut: Ctrl+b
'
Range("A5:B107").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

2. As long as I am here, can you explain why some of my VBA programs will
not work in my workbook when I have another workbook open; I placed similar
VBA info into each? And is there a way to separate this.
 
How would I go about marking my sheets set to true for "userinterfaceonly" ?
I appreciate your time as well and thank you for your expertise.

Chris
 
Back
Top