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

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

"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" wrote:

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.

See responses in-line

On Tue, 23 Mar 2010 10:50:06 -0700, Lenny
wrote:

"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" wrote:

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

select those cells using CTRL + click then InsertNameDefine

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection is whatever cell you click on

Dim myrange As Range

see Dim in VBA help

Set myrange = Me.Range("qwerty")

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

On Error GoTo ws_exit:

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

Application.EnableEvents = False

prevent looping

If Not Intersect(Target, myrange) Is Nothing Then

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

With Me

protect the sheet

.Protect Password:="justme"
.EnableSelection = xlNoRestrictions

allow selection of any cell

End With
Else
If Intersect(Target, myrange) Is Nothing Then

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

Me.Unprotect Password:="justme"
End If
End If
ws_exit:
Application.EnableEvents = True

re-enable events

End Sub

Paste into Sheet1 code module.


Gord Dibben MS Excel MVP

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



On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.

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

"Gord Dibben" wrote:

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.

"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 FILENEW.

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 VIEWTOOLBARS 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" wrote:

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.

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

On Tue, 23 Mar 2010 12:58:01 -0700, Lenny
wrote:

"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 FILENEW.

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 VIEWTOOLBARS 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" wrote:

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.

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

"Gord Dibben" wrote:

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

On Tue, 23 Mar 2010 12:58:01 -0700, Lenny
wrote:

"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 FILENEW.

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 VIEWTOOLBARS 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" wrote:

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.


.

Thanks and good luck with project.


Gord

On Wed, 24 Mar 2010 10:30:02 -0700, Lenny
wrote:

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

"Gord Dibben" wrote:

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

On Tue, 23 Mar 2010 12:58:01 -0700, Lenny
wrote:

"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 FILENEW.

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 VIEWTOOLBARS 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" wrote:

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

On Tue, 23 Mar 2010 09:10:16 -0700, Lenny
wrote:

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

.


.