3

this is my first question here. Using VBA in Excel, I'm directing the user to a specific cell that they missed filling out. But it refuses to select the correct range, instead selecting G10 each time without fail! G10 is an irrelevant, blank, locked cell, with no name definition in the Name Manager. I even have the sheet in question protected with 'select locked cells' turned off. In the code below I show all the methods I tried with comments on each one. "LotSum_Quantity" is defined in the Name Manager as lotws range A6:

lotsize = lotws.Range("LotSum_Quantity").Value   'the named range user must add a number in (cell A6)

'check for entered quantity, IFF not, then instruct, exit, and pre-select the cell they need to fill
If Len(lotsize) = 0 Then
    MsgBox "Before continuing, you must enter a Lot Quantity in the Lot Summary tab.  Excel will now switch to that worksheet for you.  Please enter the required Lot Quantity, then try uploading again."

    lotws.Activate 'this works to switch focus to the sheet in question at least
    lotws.Range("LotSum_Quantity").Select 'I expected this to work.  NOPE, goes to cell G10...
    lotws.Range("LotSum_Quantity").Activate 'no difference, G10 ahoy!
    lotws.Range("A6").Select 'so I tried this, still goes to cell G10...
    lotws.Range("A6").Activate 'still selects G10...
    Application.Goto lotws.Range("A6"), True 'this scrolls to row 6, but still leaves G10 selected!

    GoTo FULLSTOP

This isn't an application breaking problem, the user will just have to manually select the cell they need to fill. But it is frustrating because I don't understand why it isn't working. It should work, and it is a trivially easy task to accomplish normally. I don't typically use activate/select unless absolutely needed to direct the user to a location, and it has always worked in previous applications. Is my workbook showing signs of corruption? I'm still a novice with VBA/Excel. Thanks for your thoughts.

EDIT: cells A6 and B6 are unlocked, they are the only cells I want the user to be able to select. ALSO, it appears to be a problem with my self-defined protection function which loops through each sheet during re-protection. I'll expand on that once I determine root cause. I greatly appreciate your suggestions so far.

EDIT2: Going to post the protection function code where the error seems to take place. I'm not sure I'll be able to determine root cause here, it honestly seems like an excel bug. I've turned back on screen updating to see what happens and debugged enough to know the state variable is working as it should, and that it is in fact protecting/unprotecting all sheets as expected. The cell selection color box does surround A6 as desired on the 4th sheet in the loop, but two sheets later in the loop, the cell selection box around A6 simply disappears when it gets to the 6th worksheet, and then selects G10 when it gets to the 7th worksheet which is the only special case with Log in the name. I hope this makes sense to everyone. For debugging I'm leaving out disallowing the lotws selection of locked cells. Code:

Public Function toggleprot(state As String)
'note: ws defined globally for misc loops throughout project

If state = "off" Then 'toggle protection OFF on all worksheets
    testwb.Unprotect Password:="redacted"     'unprotect workbook
    For Each ws In testwb.Worksheets          'unprotect each sheet
        If ws.ProtectContents = True Then
            ws.Unprotect Password:="redacted"
        End If
    Next ws
End If 'state is OFF

If state = "on" Then 

    If testwb.ProtectWindows Or testwb.ProtectStructure Then
    'if already protected, don't call .Protect again because it acts as A TOGGLE and will 'Unprotect' if called twice..
    Else '(I swear I get intermittent results with If Not method, hence I use the else version)
        testwb.Protect Password:="redacted"  
    End If

    'protect all worksheets, treat the log special still
    For Each ws In testwb.Worksheets
        If ws.ProtectContents = False Then        
            If InStr(ws.Name, "Log") Then
                ws.Protect _
                Password:="redacted", _
                DrawingObjects:=False, _
                contents:=True, _
                Scenarios:=False, _
                userInterfaceOnly:=False, _
                AllowFormattingCells:=False, _
                AllowFormattingColumns:=False, _
                AllowFormattingRows:=False, _
                AllowInsertingColumns:=False, _
                AllowInsertingRows:=False, _
                AllowInsertingHyperlinks:=False, _
                AllowDeletingColumns:=False, _
                AllowDeletingRows:=False, _
                AllowSorting:=True, _
                AllowFiltering:=True, _
                AllowUsingPivotTables:=False
                'additionally, only allow selection of UNLOCKED cells:
                '(cant use hyperlink in locked cell if you cant click the locked cell, FORCED to unlock on the log so that autofilter has chance to work....)
                ws.EnableSelection = xlUnlockedCells

            Else 'for all other worksheets
                ws.Protect Password:="redacted"
            End If 'log VS all others
        End If 'not protected then protect
    Next ws
End If 'state is ON

EDIT3: Here is the code I use to toggle screen updating at the beginning and ending of my routines. For debugging, I commented out the call to this function in order to determine when the cursor disappeared.

Public Function toggleupdate() 'toggle screen updating 
If Application.ScreenUpdating = False Then
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
Else
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
End If
End Function  'TOGGLE SCREEN UPDATE

(Here is a picture of the sheet in question showing the infamous G10 selected...) G10 Problem

ionizing
  • 68
  • 7
  • 1
    If you temporarily turn off your password protection on that tab so it's wide open, does your code work then? Also as a second troubleshooting idea, add a breakline after setting `lotsize` (set your cursor on that `if` statement and press `F9`, then run this macro). Is the value of that variable (in your `locals` pane, or just hovering over the variable) what you expect from cell `A6`? – JNevill Dec 20 '19 at 16:50
  • Thanks for the suggestion and explaining exactly how to use the breakline. I've done that now and: lotsize=Empty which is what I expect to trigger the IF (the IF is triggering and showing msgbox and activating lotws.) EDIT: it IS something with protections. I thought I had both sheet unprotected when I wrote my response to you just now, went back and tried twice to be sure. With WB and WS fully unprotected, the code DOES work... So I will analyze my protection function. – ionizing Dec 20 '19 at 17:00
  • In the immediate window please type `?lotws.name` and then `Enter` What is returned? – SmileyFtW Dec 20 '19 at 17:03
  • Sorry SmilerFtW, not sure which window to type that in. It appears to be functioning as long as all my protections are turned off, so I will spend time analyzing my user-defined protection Function and report back when I have found the culprit. – ionizing Dec 20 '19 at 17:08
  • Is the "off" in `'select locked cells' turned off` a typo? Should be "on" right? That option should be clicked. – BigBen Dec 20 '19 at 17:47
  • @BigBen That part is not a typo, I only want the two cells A6 and B6 to be selectable, they are the only unlocked cells on the Sheet. I turned screen updating back on so I can watch what happens during my self-defined protection function which loops through all sheets in the wb and re-protects them. It appears the proper cell A6 IS selected until it gets a couple sheets further in the loop, then suddenly the cursor disappears entirely.. I think I'm getting closer to the root cause and will update once there. edit: Sorry BigBen, I simply forgot to include that fact above. I'll edit that in. – ionizing Dec 20 '19 at 18:16
  • Ok so `A6` is unlocked, that was the part I was missing. – BigBen Dec 20 '19 at 18:17
  • In the main VBIDE window: View>Immediate Window (Ctl-G will also bring it up). After your updated comments to @BigBen it would seem that you've at least narrowed it down to protection. – SmileyFtW Dec 20 '19 at 18:28
  • @SmileyFtW Thank you for explaining where to go for Immediate window. It is definitely bugging out during the loop where protections are turned back on. It's like excel can't handle more than 6 worksheets in the loop before acting strange. Thanks for your interest in helping solve it. – ionizing Dec 20 '19 at 19:22
  • Seems like you need to step through your code and identify on which line specifically the selected cell changes from A6 to G10 and in what part of your loop (i.e. which sheet is being worked on). In your selection procedure try putting a `Debug.Print lotws.Range("LotSum_Quantity").Address` either above or below the `lotws.Range("LotSum_Quantity").Select` Then inspect the Immediate window to see if it is indeed A6. – SmileyFtW Dec 20 '19 at 19:40
  • @SmileyFtW OK I've done as you suggested and it shows ```$A$6``` as we want. I need to learn more about using the debugger to step through, cause I get to the msgbox and it won't let me step past that without just full continuing. I go on week vacation in 30 minutes so I apologize but I don't think I'll be solving this today unfortunately. It definitely jumps from ```$A$6``` to ```G10``` during the protection function loop. I'll learn more about using the debugger now, this is the first time I've run into a problem that actually requires it. Thanks for your suggestions and help! – ionizing Dec 20 '19 at 19:58
  • @SmileyFtW In the few minutes I had left today I inserted debug statements in the protect loop and determined it isn't the number of sheets causing the problem of course, it is the only sheet with special protection needed (it is the Log sheet referenced in the protection function posted above.) So now I have a place to start looking when I get back to this in a week, and I already learned a lot about debug.print and the immediate window just from your suggestions today, so thank you for explaining! – ionizing Dec 20 '19 at 20:26
  • It occurs to me later in the evening that I could be less lazy about setting protections, no need to toggle protection on sheets not being acted upon in that routine. That should at least get around the problem. I still intend to figure out why the protection on the specific Log sheet is causing the observed symptoms. – ionizing Dec 21 '19 at 01:34
  • I would also comment that sheet protection isn't going to gain you much in the way of real protection. It can help keep users from fiddling with things on the sheet, but breaking sheet protection is notoriously easy to do. – SmileyFtW Dec 21 '19 at 20:14

0 Answers0