Lock Script misses first Range

[SeliM]

Hello
Thanks for reading my enquiry.
I'm putting a Sudoku puzzle generator together for grandkids and have a functioning model nearly complete.

I have script that locks a generated puzzle and "locks" cells that are not empty as in "".
The range covers all 9 games, ( of 9 rows and 9 columns) - $B3:$J$11

Script works for the eight rows but does not lock the filled cells on row 3.
Any advice welcomed.

Script below:
Sub LocktheGame()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Sudoku")
For Each Rng In ActiveSheet.Range("$B$3:$J$11")
'To test if cell is empty
If Rng.Value = "" Then
'If empty lock cell
Rng.Locked = False
Else
'If cell not emty
Rng.Locked = True

End If

Next Rng

Worksheets("Sudoku").Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

ActiveSheet.Protect "pass"
screenupdate = True
Sheets("Givens Management").Select
ActiveWindow.SelectedSheets.Visible = False
Range("B3").Select

End Sub

Many thanks
Mel

 

[Micron]

Did you step through the code (F8) and watch the flow and check variables to see if they contain what you expect? Quite possibly something in row 3 has a value but you can't see it.

Please post code within vba code tags to maintain indentation and enhance readability.

 

[Micron]

I tested your code and it seems to work for me although I had to change a couple of things but that should have no effect. Tested like this:

Sub LocktheGame()
'declare a variable
Dim ws As Worksheet
Dim rng As Range

''Set ws = Worksheets("Sudoku")
Set ws = Worksheets("Sheet2")
For Each rng In ActiveSheet.Range("$B$3:$J$11")
    'To test if cell is empty
    If rng.Value = "" Then
        'If empty lock cell
        rng.Locked = False
    Else
        'If cell not emty
        rng.Locked = True
    End If
Next rng

''Worksheets("Sudoku").Activate
Worksheets("Sheet2").Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

ActiveSheet.Protect "pass"
''screenupdate = True 'is this an undeclared variable?
''Sheets("Givens Management").Select
''ActiveWindow.SelectedSheets.Visible = False
Range("B3").Select

End Sub

I would change some things. For one, you seldom have to select anything to do anything with it. It's often just fluff code. Also, as long as you set a variable to a sheet name you might as well use it thereafter, instead of writing out the sheet name again. You don't really need the If block either. I'm guessing this code won't/can't run if it already has run? I say that because once it's locked you're going to raise an error when trying to lock/unlock a cell on a protected sheet (at least I think so). Lastly, why try to select B3 when that might be locked?

 

[SeliM]

Many Thanks, guess the code structure indicates my novice ranking

I have run the debug and get the following message at Rng.Locked = True - Run time error 1004 Application defined or Object defined error which is beyond my understanding.

Could it be I have to define the range ($B$3:$J$11) as a variable?

The model works on a setting of a sudoku problem by predetermining a given set of numbers in each of the 9 games, locking those cells so the user can't inadvertently change the setup. Each cell in the game has a formula that enters numbers selected in the "Givens Management" (that allows me to setup a puzzle) and the LocktheGame() script locks down the puzzle supposedly only allowing the cells with formula result null unlocked. Yes can only be used once until the script "New Game?" is ran by macro button.

On the question "why try to select B3 when that might be locked?" B3 is designed to be locked when I have selected a number (On "Givens Management" WS) for that cell in setting up the sudoku problem and remain unlocked if left blank so the user can enter their solution option. The problem is that some cells that have a predetermined number remain unlocked instead of locked. All are within the range $B$3:$J$11.

The workbook has the following WS -
Givens Management - where the given numbers 1-9 are entered to set up the challenge
Numbers Management - designed to ensure only numbers available are presented by way of dropdown list for user selection
Errors Detection - designed to prompt messages when an error is detected (count of each number 1-9 greater than 1)

Sorry this has become more complicated than I thought it would. Appreciate any advice.

Hope this helps define the problem better.
Mel

 

[Micron]

Could it be I have to define the range ($B$3:$J$11) as a variable?

If you mean assign that range to a variable then no. If you mean declare rng as a range variable then perhaps. I had to because all my code modules use Option Explicit at the very top. IMHO, if I don't use that I deserve all the grief I get. Also, if you run the code when the sheet is already protected you'll raise an error as I already mentioned. 1004 error number is used for a multitude of messages . I get "Unable to set the locked property of the range class" if the sheet is already locked. I think your code should unprotect the sheet first, unless you plan to take care of that in a reliable fashion somehow.

This part seems a bit confusing but won't have anything to do with that error:

'If empty lock cell
rng.Locked = False

The comment implies to lock the cell if it's empty yet the next line says don't lock it. Not sure if that means you don't get the result you want (when/if there's no error).

RE: B3 - I get that it might or might not have a value in it. If it does, it's locked, yes? So why select it in that case? I just learned that you can select a locked cell with vba but can't with the keyboard or mouse so I guess it's not a problem.

As I mentioned, your code works for me but on my workbook. If you want to upload a copy of yours to a file share I should be able to look at it, and perhaps figure that out. Or make sure the sheet is not protected when you run the code and see if that's the issue. Here's my version of your code, which also works:

Sub LocktheGame()
Dim ws As Worksheet
Dim rng As Range

Set ws = Worksheets("Sheet2")
ws.Unprotect
For Each rng In ws.Range("$B$3:$J$11")
    rng.Locked = (rng <> "")
Next rng

With ws
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    .EnableSelection = xlUnlockedCells
    .Protect "pass"
End With

''screenupdate = True 'What is "screenupdate", an undeclared variable?
''Sheets("Givens Management").Visible = False
Range("B3").Select

End Sub

Note that I had to comment out 2 lines near the end and substitute the sheet name to make it work. I've added the sheet unprotect part, which you can remove if you want. Also note the use of ws rather than repeating the sheet name or using ActiveSheet.

EDIT - that code works as I mentioned, so I can unprotect the sheet with that code or by right clicking on the tab. So it seems your password isn't set. I'm not familiar with that and would have to look it up. The reason is that I'm more familiar with Access vba than Excel.

 

[SeliM]

Thank you very much Micron

We're nearly there!
It seems that if the cell B3 has a number assigned it will allow that number to be changed unless another cell is selected. when that happens B3 becomes locked.

I sort of understand what it's doing - I wonder if there is script that asks excel to select the first cell available that = "" otherwise with your edits and changes it works well (and the script seems much simpler)

Many thanks

Mel

 

[SeliM]

Hi

I tried this piece of script " Selection.SpecialCells(xlCellTypeBlanks).Select"
Unfortunately, I get a 1004 error

It is so close - very frustrating
But good education

Mel

 

[SeliM]

Sorry error Runtime 400

 

[Micron]

Sorry, don't understand what post 6 means. I have a value in every row of the range. Then I run the code; sheet locks; B3 is selected. I cannot change the value in any of the cells with data in that range.

I don't know or have forgotten how you're making this code run so maybe that has something to do with it.
In post 7 you're trying to select a selection. I doubt that a selection has a selection method. BTW, error messages are important too. After thousands of posts here and elsewhere, I seldom will look them up any more and there's way too many to remember what message goes with what number.

So at this point I don't have a suggestion because I don't understand what the issue is now.

 

[SeliM]

In setting up the Sudoku puzzle the first cell B3 may or may not have a value assigned to it.
If it does have a value assigned it should be locked so that it cannot be changed by the user

When I originally ran the script the "select B3" functions but it can be changed until the cursor is moved off it - then it locks and cannot be changed

After some experimenting I stumbled on a solution, actually the original attempt:

ActiveSheet.Unprotect
Selection.SpecialCells(xlCellTypeBlanks).Select

I simply had it in the wrong spot!!

I think it's ready for grandkids now

Testing it a few times to see if I can get it to fail.

Many thanks for help
Merry Christmas

Mel