0

I'm trying to change the "selected" cell/range in a worksheet that is currently not the active worksheet after protecting/unprotecting the worksheet. My current solution is:

NonActiveWorksheet.Unprotect
Application.ScreenUpdating = False
Set CurrentWorksheet = ActiveSheet
NonActiveWorksheet.Activate
NonActiveWorksheet.Cells(1, 1).Select
CurrentWorksheet.Activate
Application.ScreenUpdating = True

If I unprotect (.Unprotect) a previously protected worksheet the NonActiveWorksheet stays active (CurrentWorksheet.Activate seems to have no effect). When I debug the code step by step it works. The other way around, protecting (.Protect) a previously unprotected worksheet works fine as well.

Any idea why this is or how to solve it? My preferred solution would be to change the selected/highlighted cell/range on a non-active worksheet without activating it so I don't get into the problem in the first place.

Albin
  • 1,000
  • 1
  • 11
  • 33
  • 1
    If you would share your complete code (from `Sub...` to `... End Sub`) and explain what you are trying to do, we could show you how to achieve it without selecting and activating which severely slows down the code and is prone to errors. Your code does what you are describing but the issue is if `NonActiveWorksheet` is already the active sheet, then both worksheet variables end up referring to the same worksheet. Use e.g. `Debug.Print ActiveSheet.Name` to see in the Immediate window (`Ctrl+G`), which worksheet is active after a line of code. – VBasic2008 Jun 20 '23 at 05:55
  • @VBasic2008 The core of what I'm trying to do is to "select" a cell on a non-active worksheet: `NonActiveWorksheet.Cells(1, 1).Select` which doesn't work unless the sheet is active. Just ignore everything else. – Albin Jun 20 '23 at 07:58
  • You cannot select a cell on a non-active worksheet. You can create a reference to the currently active worksheet, then activate the critical worksheet, select the cell and then activate the initial worksheet again via the reference (variable). You have already done this in your code. The only issue with it is when the critical worksheet is already the active sheet. – VBasic2008 Jun 20 '23 at 08:18
  • You can reference a cell on a non-active worksheet, but not select it. `MyVariable = NonActiveWorksheet.Cells(1, 1).Value` [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Darren Bartrup-Cook Jun 20 '23 at 08:35
  • @DarrenBartrup-Cook I don't care about the content of the cell (that is not why I want to change the "selection") – Albin Jun 20 '23 at 08:39
  • @VBasic2008 Please note: selection in Excel means also tinkering with the active cell, that is not what I mean hence the brackets around "selection". As far as I understand each sheet needs to have at least one selected cell, regardless if it's the active sheet or not. Excel must keep track of the selected cell of the non-active sheets somehow which becomes the active selection once you activate the sheet. That is what I want to change, (and I know I can't do that via `.select`)I hope that makes things clearer. – Albin Jun 20 '23 at 08:45
  • Selecting cells is only needed for changing the cell contents manually, when you type text. When you change the cell content in vba, your program slows down when you select every cell before you change it. I can understand that you want to leave a cell selected by default. That can only happen in an active sheet and you can even select a cell manually and save the workbook. It will be selected when you (or another user) open it again. It is not needed for changing the value of the cell, so I would like to learn why you want to leave certain cells selected – hennep Jun 20 '23 at 13:46
  • @hennep "Simply because" ;) The reason doesn't really matter, but possible reasons might be to prepare manual data entry by "preselecting" the cells in the workbook, moving it out of the viewable area etc. – Albin Jun 20 '23 at 16:24

1 Answers1

0

Scenario: you have a worksheet with two tabs, Sheet1 and Sheet2, each with A1 as the selected cell when the worksheet is activated. Sheet2 is protected, disallowing selection of locked and unlocked cells.

Are you saying you want to set another cell, say B5, as the "selected" cell when the user manually activates Sheet2 after the code runs?

Because this code does that:

Sub test()
    With Application
        .ScreenUpdating = False
        With Sheet2
            .Activate
            .Unprotect
            Range("B5").Select
            .Protect
        End With
        Sheet1.Activate
        .ScreenUpdating = True
    End With
End Sub

When Sheet2 is activated by the user, cell B5 shows as selected after unprotecting it.

If Sheet2 allows unlocked cells to be selected, then Sheet2 does not have to be unprotected if B5 is unlocked.

*** update: playing with protection/locking: If B5 is unlocked and sheet allows selecting unlocked, B5 shows as selected. If sheet does not allow selecting unlocked, nothing shows as selected until the sheet is unprotected, at which time B5 will show as selected.

pdtcaskey
  • 242
  • 1
  • 9
  • Thanks for your answer. This is pretty much the code from my example, or am I mistaken? For VBA code the protection status doesn't really matter if you use `UserInterfaceOnly:=True`. And as far as the "inner working" of Excel goes if I understand correctly "true" selection is linked to activating. I don't think you can call a cell selected if it's not in the active worksheet (that's why I'm using the quotation mark when I talk about selection on non active sheets) – Albin Jun 20 '23 at 16:31
  • I guess, then, I'm not sure what you're looking for. Yes, only the active sheet can have a range truly "selected". You said, though, "I'm trying to change the "selected" cell/range in a worksheet that is currently not the active worksheet" - which cannot happen. What do you hope to do with the cell on the inactive sheet when you say "selected"? – pdtcaskey Jun 20 '23 at 17:44
  • Are you trying to change/read the value or formula in a cell on the inactive sheet? Is that cell the one that "would be" selected if the sheet were active? Being more descriptive of your intention would help get the answer you're looking for... – pdtcaskey Jun 20 '23 at 17:47
  • As I said, in my other comments I don't care about the value of the cells. I just want to change "selected" cells on non-active sheets (e.g. move it out of the visible area). Activating the sheet and using `.select` is the current workaround I'm using, but it comes with side effects (e.g. the one I stated in my question). – Albin Jun 21 '23 at 05:40
  • When I set the ActiveSheet as a worksheet object, activate another sheet, move the selection and then activate the saved worksheet object, it all behaves as expected and as far as I can tell, the way you described what you're trying to do. – pdtcaskey Jun 23 '23 at 22:51
  • Strange, my code as stated above still doesn't work, must be something else then. Thanks for your help anyway. – Albin Jun 24 '23 at 08:27