0

I have a workbook with some sheets where the first sheet contains some buttons which perform modifications on the other sheets.

I noticed that when I press those buttons Excel automatically activate the specified sheet. Is it possible to prevent this mechanism?

This mechanism is triggered for EVERY operation (cell modifications, styling, color changes, creation of new sheets and so on...)

Example of modification applied (they all follow the same template):

Worksheets("SHEETNAME1").Unprotect
Worksheets("SHEETNAME1").Cells(2,1).value = "cellvalue"
Akinn
  • 1,896
  • 4
  • 23
  • 36
  • 2
    I believe you know we need to see some actual code to help you, but without seeing it I bet you can use this [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to help you out. – Damian Sep 10 '19 at 09:51
  • Is it possible to prevent this mechanism? - Yes – jivko Sep 10 '19 at 09:52
  • I know I should post the code, but I have no way to retrieve it (it is in remote). I don't use any select. I'll update the question with an example of modification I apply (they all use the same template) – Akinn Sep 10 '19 at 10:02
  • 1
    @AndreaGiordano there is a `.Select` or `.Activate` on the code, that's what are they intended to... to activate or select sheets, if there were none, that wouldn't happen. Unless.... the code creates temporal sheets and then deletes them in which case it will selet the previous sheet. – Damian Sep 10 '19 at 10:05
  • I can assure there is no .Select and no .Activate in the whole project. Just looked for them to be sure. At least not directly called. – Akinn Sep 10 '19 at 10:06
  • 1
    it's not likely to be the best solution but if nothing else works you can apply Application.ScreenUpdating = False in the start of the code, and Application.ScreenUpdating = True with a button sheet activate in the end to avoid the appearance of changing sheets – Andreas N. Sep 10 '19 at 10:09
  • that's strange... even using ScreenUpdating it switches pages. – Akinn Sep 10 '19 at 11:50

1 Answers1

0

To avoid activating the edited sheet you have to pass the password in your code, change your code to :

Worksheets("SHEETNAME1").Unprotect("sheet_password")
U7765660
  • 91
  • 6
  • Why assume the existence of a password? – John Coleman Sep 10 '19 at 12:07
  • @JohnColeman, because only with a protected sheet, the issue will be encountered.At least this was my experience. – U7765660 Sep 10 '19 at 12:26
  • there is no password – Akinn Sep 10 '19 at 14:52
  • @AndreaGiordano, if there is no password, why are you unprotecting the sheet? using your code extract, I tried it in 365 excel, only when the sheet is protected by a password, sheet gets activated. Maybe there is something else in your code which is activating the sheet. – U7765660 Sep 11 '19 at 08:18