Protect via Code Q

S

Sean

To password protect a sheet I use a very simple piece of code, as
below, but how do I incorporate code to ensure that the "Select
Unlocked Cells" is UNCHECKED when protecting


Sub Protect()

Sheets("Sheet1").Select
ActiveSheet.Protect Password:="1234"

Thanks
 
C

Chip Pearson

Sean,
Try something like

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
WS.EnableSelection = xlNoSelection
WS.Protect Password:="1234"

I'll give you a piece of advice that will serve you well as you get deeper
in to Excel and VBA: Get out of the habit now, not later, of using Select in
VBA. It is (almost) never necessary to Select anything. Instead, reference a
worksheet or workbook or range or whatever directly. Don't Select anything.
Your code will be cleaner, faster, and easier to maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
S

Sean

Thanks Guys

Chip I assume then if I ever want to "select" something again I simple
preface my code with

Dim WS As Worksheet
Set WS = Worksheets("'whatever")
WS. "whatever"

Is there any good sites for novice vba writers? Particularly a listing
of what things relate to (from what I see on Excel) eg. xlNoSelection
etc or do I just record a macro and work it out that way. I can get
things working eventually with some guidance, but most of how the code
works pass right over me.
 
C

Chip Pearson

Sean,
Set WS = Worksheets("'whatever")
WS. "whatever"

No. The WS variable already refers to the sheet named "whatever". That what
the Set statement does. Once have the WS variable pointing to the
appropriate sheet, use WS where you would normally use ActiveSheet or
Worksheets("Whatever"). E.g.,

Set WS = Worksheets("'whatever")
WS.Range("A1").Value = 1234

This puts the value 1234 in cell A1 of whatever sheet WS is refering to.

Recording a macro is the easiest way to see what properties and methods are
used, but the code it produces is not pretty bad (mainly because it cannot
anticipate what you are going to do in the next step). It uses Select and
Selection. Use the methods (e..g, Sort) or properties (e.g, Bold), but
create variables for the objects in question. E.g., rather than

Range("A1").Select
Selection.Font.Bold = True

use code like

Range("A1").Font.Bold = True

or

Dim Rng As Range
Set Rng = Range("A1")
Rng.Font.Bold = True



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top