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...)