unlocking cells

[johnsail]

Hi
Have a worksheet of 40 rows and cols A to K.
Only cols B,C and D are unlocked.
Data is entered along each row.
Within each row
a) If the data entered in cell D = "999" then cell in col E should be
unlocked.
b) If data <> "999" then cell in col J should be unlocked.
c) If D is changed from "999" to something else then contents of E need to
be deleted and the cell locked.
d) If D is changed to "999" from any other value the contents of cell in col
J need to be deleted and replaced with a formula and then locked.(The formula
is held in a hidden cell K on each row).
e) If the contents of cell D are deleted then both (c) and (d) above need to
be actioned.

Is this possible in VB?

Regards
John

 

[Ivyleaf]

Hi
Have a worksheet of 40 rows and cols A to K.
Only cols B,C and D are unlocked.
Data is entered along each row.
Within each row
a) If the data entered in cell D = "999" then cell in col E should be
unlocked.
b) If data <> "999" then cell in col J should be unlocked.
c) If D is changed from "999" to something else then contents of E need to
be deleted and the cell locked.
d) If D is changed to "999" from any other value the contents of cell in col
J need to be deleted and replaced with a formula and then locked.(The formula
is held in a hidden cell K on each row).
e) If the contents of cell D are deleted then both (c) and (d) above need to
be actioned.

Is this possible in VB?

Regards
John

Hi John,

This may not be perfect depending on exactly how you want your
criteria to behave, but it should give you a starting point:

'This is necessary to avoid having to unprotect
'and protect the sheet every time the macro fires
Private Sub Worksheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OldVal, CaseE As Boolean

If Target.Column = 4 Then
OldVal = Target.Offset(0, 100).Value
CaseE = IsEmpty(Target)
If Target = 999 Then
Target.Offset(0, 1).Locked = False
Else
Target.Offset(0, 6).Locked = False
End If
If (OldVal = 999 And Target <> 999) Or CaseE Then
With Target.Offset(0, 1)
.ClearContents
.Locked = True
End With
End If
If Target <> OldVal And Target = 999 Or CaseE Then
With Target.Offset(0, 6)
.Formula = Target.Offset(0, 7).Formula
.Locked = True
End With
End If
Target.Offset(0, 100) = Target.Value
End If
End Sub

It should just be a case of massaging the criteria and their order
until you get what you want. Note that I did have to use a 'helper'
cell at Column CZ to hold the 'old value' of the cell in column D,
since the worksheet doesn't have a 'before change' event. You can
certainly move this closer to your data... I was just playing it safe
by offsetting by 100.

Cheers,
Ivan.

 

[johnsail]

Ivan Hi

First thanks for the quick response.

Have put the formula in col K and the help cell in col L.

So have changed your routine to this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OldVal, CaseE As Boolean

If Target.Column = 4 Then
OldVal = Target.Offset(0, 8).Value
CaseE = IsEmpty(Target)
If Target = "Mileage" Then
Target.Offset(0, 1).Locked = False
Else
Target.Offset(0, 6).Locked = False
End If
If (OldVal = "Mileage" And Target <> "Mileage") Or CaseE Then
With Target.Offset(0, 1)
.ClearContents
.Locked = True
End With
End If
If Target <> OldVal And Target = "Mileage" Or CaseE Then
With Target.Offset(0, 6)
.Formula = Target.Offset(0, 7).Formula
.Locked = True
End With
End If
Target.Offset(0, 8) = Target.Value
End If
End Sub

When running the code it gives the error:
Run Time error 1004
Unable to set Locked property of the Range Class

The debugger highlights the line:
Target.Offset(0, 6).Locked = False

Have I changed things incorrectly?

John

 

[Ivyleaf]

Ivan Hi

First thanks for the quick response.

Have put the formula in col K and the help cell in col L.

So have changed your routine to this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim OldVal, CaseE As Boolean

    If Target.Column = 4 Then
        OldVal = Target.Offset(0, 8).Value
        CaseE = IsEmpty(Target)
        If Target = "Mileage" Then
            Target.Offset(0, 1).Locked = False
            Else
            Target.Offset(0, 6).Locked = False
        End If
        If (OldVal = "Mileage" And Target <> "Mileage") Or CaseEThen
            With Target.Offset(0, 1)
                .ClearContents
                .Locked = True
            End With
        End If
        If Target <> OldVal And Target = "Mileage" Or CaseE Then
            With Target.Offset(0, 6)
                .Formula = Target.Offset(0, 7).Formula
                .Locked = True
            End With
        End If
        Target.Offset(0, 8) = Target.Value
    End If
End Sub

When running the code it gives the error:
Run Time error 1004
Unable to set Locked property of the Range Class

The debugger highlights the line:
Target.Offset(0, 6).Locked = False

Have I changed things incorrectly?

John













- Show quoted text -

Hi John,

Did you stick this bit in too?

'This is necessary to avoid having to unprotect
'and protect the sheet every time the macro fires
Private Sub Worksheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

As it says in the comment, unless this bit is on the sheet as well,
the macro won't have permission to alter the locked cells in the
sheet. You can get around it by doing a 'Protect' / 'Unprotect', but I
prefer not to if I can avoid it.

If you have this Sub in and you're still having problems, let me know.
I didn't try your modified code, but at a glance it looked OK.

Cheers,
Ivan.

 

[johnsail]

Ivan
Yes that extra bit is in the code - I didn't alter that so I did not bother
to include it in my last reply.

Have done some investigating without the protection being on and it would
seem that the cells are getting protected / unprotected and the clearing of
cell D does wotk and does replace the formula.

However with the sheet protected the code stops with the error quoted.

Consequently the cursor is not allowed on to cell E for data entry (or cell
J if the entry in D is not Mileage)

regards

John

 

[Gord Dibben]

What happens if you unprotect, do the work by code then re-protect.

ActiveSheet.Unprotect Password:="justme"

do your stuff here

ActiveSheet.Protect Password:="justme"


Gord Dibben MS Excel MVP

 

[johnsail]

Hi Gord

Changed routine as you suggested (I commented out the first bit of Ivan's
code - Yes???)

Routine still stops with same error when col D = "Mileage" but does not stop
when col D <> "Mileage".

In both cases the cursor moves to next row Col B.

In both cases if you go back to the previous row the correct cells have been
unlocked and the cursor now moves to them. (It is just as if the unlock
didn't happen quickly enough to allow the cursor on to the newly unlocked
cell OR the next cell to get the focus is held somewhere and that value needs
changing in the code)

The resetting of the values when D changes from "Mileage" to non-"Mileage"
or vice versa does not now work nor does it work if D is deleted. However I
have played about with so many options now that I am beginning to wonder
whether my statement regarding Ivan's original code was in fact true. I am
going back to check on that.

Regards

 

[johnsail]

Hi Ivan and Gord
Have checked Ivan's original code and it does work correctly when the sheet
is unprotected.

 

[johnsail]

Hi Ivan and Gord

I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3.

Could this have anything to do with the problem?

Just a thought.

Regards

 

[Ivyleaf]

Hi Ivan and Gord

I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3.

Could this have anything to do with the problem?

Just a thought.

Regards






- Show quoted text -

Hi John,

I'm running Excel 2003 too, so shouldn't be a versioning issue. If
it's working with the sheet unprotected, then I'd probably suggest
going with Gord's suggestion of sticking the Unprotect at the start
and then Protect at the end. I normally try and avoid that for the
reason that if the code breaks for some obscure reason while the user
is working with it, you end up with an unprotected sheet.

Actually I think I have just clicked why it may not be working for
you! I stuck the:

ActiveSheet.Protect UserInterfaceOnly:=True

line in the Worksheet_Activate event, which should fire every time the
user opens the workbook and switches to that sheet. If you are pasting
in the code then trying to test it straight away, the event will not
have fired, since the sheet has never lost focus - i.e. it has never
become active since it has been active all along. Once you have
pasted, try switching to a different sheet then back and see if it
works.

Let me know.

Cheers,
Ivan.

 

[johnsail]

Hi Ivan

Added a sheet and then moved from that sheet to the problem sheet. It asked
for the sheet to be unprotected. If left protected then code stops on error
1004. If unprotected it works EXCEPT that the cursor does not move to the
newly unprotected cell - it goes to the cell that it would have gone to if no
entry in D was made.

Can you force the focus by program?

Regards

PS Had extreme difficulty replying to your post - is there a limit to the
number of levels in a thread?

 

[Ivyleaf]

Hi Ivan

Added a sheet and then moved from that sheet to the problem sheet. It asked
for the sheet to be unprotected. If left protected then code stops on error
1004. If unprotected it works EXCEPT that the cursor does not move to the
newly unprotected cell - it goes to the cell that it would have gone to ifno
entry in D was made.

Can you force the focus by program?

Regards

PS Had extreme difficulty replying to your post - is there a limit to the
number of levels in a thread?












- Show quoted text -

Hi John,

I didn't know you wanted the cursor to move to that cell. Easy enough
to do though. Just add a line like:

Target.Offset(0,1).select

This will move the cursor to the cell which is on the same row, and
one column across. Just change the 1 to whatever number you need.

Cheers,
Ivan.

 

[johnsail]

Ivan

Thank you so much.
There are a few bits to sort out re where the cursor goes during the change
/ delete bits but now that I know how maybe I will be able to sort by myself.
But please keep watching this space.

Many thanks - I was running out of hair to pull out!!!!!!!