Macro for blocker from using cell range

[Lenny]

Is there a way to write code that can unlock a worksheet (w/password)
allowing the user access to functionality while relocking (w/password) the
worksheet should the user mouse click or move into a range of cells that
would normally be locked?

Assistance and a learning opportunity is always appreciated...

Regards, Lenny

 

[Gord Dibben]

With "qwerty" as defined name for range =Sheet1!$A$1:$F$12,Day!$A$26:$F$34

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myrange As Range
Set myrange = Me.Range("qwerty")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, myrange) Is Nothing Then
With Me
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
Else
If Intersect(Target, myrange) Is Nothing Then
Me.Unprotect Password:="justme"
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

Paste into Sheet1 code module.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

If you don't want to assign a range name just work off locked or unlocked
cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Locked = True Then
With Me
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
Else
If Target.Locked = False Then
Me.Unprotect Password:="justme"
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord

 

[Lenny]

"Lenny" replied: Gord, my thanks for the reply and assistance. Would it be
an imposition to ask further if you might explain in 'lay' terms what each
line or block of of the code is doing. I know lots of site visitors are
trolling for the code, but I would like to understand what the code is
telling me (this from a newbie). Further explanation would be greatly
appreciated and anyone new to programming might also get a learning
experience. Regards - Lenny

 

[Gord Dibben]

See responses in-line

"Lenny" replied: Gord, my thanks for the reply and assistance. Would it be
an imposition to ask further if you might explain in 'lay' terms what each
line or block of of the code is doing. I know lots of site visitors are
trolling for the code, but I would like to understand what the code is
telling me (this from a newbie). Further explanation would be greatly
appreciated and anyone new to programming might also get a learning
experience. Regards - Lenny

select those cells using CTRL + click then Insert>Name>Define

Selection is whatever cell you click on

see Dim in VBA help

"qwerty" is a defined range
me is parent object......in this case the worksheet

if an error is encountered go to ws_exit which resets enableevents to true

prevent looping

if target(selected cell) is within "myrange" move on next step

protect the sheet

allow selection of any cell

if target is not within "myrange" then unprotect the sheet

re-enable events

If you look at the second set of code I posted you will see the difference.

No named range.........just locked or unlocked cells chosen by yourself
prior to running the code.


Gord

 

[Lenny]

"Lenny" replied: Gord - many, many thanks! Makes so much more sense now.

 

[Lenny]

"lenny" replied: May I ask another question, based on what I need to
accomplish with the code below?

I have been setting up Word templates for years and understand the
relationship between the master or user group template and the saved .doc.
Our IT department sets up each computer so the 'templates' are accessed thru
each of the programs FILE>NEW.

Excel as a vehicle for e-forms is relatively new. It seems a lot of the
coding is different between Word and Excel.. I'm trying to understand if
Excel has the same relationship with it's offspring files as word, especially
in regards to vb programming. My company stores all electronic form templates
(word, excel) on a server. Code, when written or copied directly into the vb
editor of a .xls file, seems to stay put, but I noticed that the custom
toolbar I created in the .xls file
disappeared from the VIEW>TOOLBARS dropdown. I read in an obit that custom
toolbars do not pass with the files created off the master template. Is
there any way to override this? Are key combinations lost also? I recently
discovered that if I create these items directly in a Word (.doc)... they
pass to subsequent files created and was wondering if there was a way to
accomplish this in Excel also. Thank you for taking the time to read my
post.... regards - Lenny

 

[Gord Dibben]

In Excel, customizations to Menus and Toolbars are stored in your
Excelxx.xlb file.

Other users will not have your customized Toolbar because they have their
own *.xlb

I suugest using code in your Template to create the Toolbar.

That code will pass to the workbooks created from the Template.

See Debra Dalgleish's site for code by Dave Peterson to build and destroy a
Toolbar.

http://www.contextures.on.ca/xlToolbar02.html


Gord

 

[Lenny]

"Lenny" replied:
Gord - many thanks for taking the time to see this thread thru. You have
been most helpful.... regards

 

[Gord Dibben]

Thanks and good luck with project.


Gord

"Lenny" replied:
Gord - many thanks for taking the time to see this thread thru. You have
been most helpful.... regards