0

I have a worksheet that is supposed to do a handful of things depending on what cells are changed. The code works perfectly for me, but when I send the document to someone else, the code inside the worksheet isn't activating at all. I tested by putting breaks in at the top of the code and it just doesn't call the code on the new computer.

If I hit a button below (creates a new sheet) and then go back, it starts working. If the user gets it working (using the button method above), saves the document, and goes back in, it still works. The issue is getting the code behind the sheet to start running immediately.

Things I've tried: 1. I added a setup/instructions tab that required a checkbox and button press to unhide the setup tab. Did not fix this problem. 2. I hid everything on the setup tab except for a single "begin" button that unhides all of the objects and text on the form. Did not work.

Some of the code sitting in the Setup tab:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect

If Not Intersect(Target, Range("D2")) Is Nothing Then

Call UpdateName

End If

Again, the code works perfectly for me, and if the user experiencing this issue uses one of the bigger macros, it fixes the problem.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Reece
  • 79
  • 2
  • 11
  • Have you checked the trust center macro settings for the other machine? It may be when the user runs the other macro, that they enable macros on being prompted – cybernetic.nomad Jan 08 '19 at 17:22
  • If macros are prevented from running, then no macro will run, there are different settings, it's the first thing to check if a macro will run on one machine but not on another. – cybernetic.nomad Jan 08 '19 at 17:29
  • But other macros are working. I've also opened the document with him and made sure that he's Enabling Content on the banner when he first opens the document. – Reece Jan 08 '19 at 17:29
  • Make sure you have `Application.EnableEvents` set to `True` - try making a new macro with just `Application.EnableEvents = True` and running that, then see if you can get the change event to work. – dwirony Jan 08 '19 at 17:33
  • That's a good idea. Unfortunately I have that in the code for the unlock button that did not work. – Reece Jan 08 '19 at 17:38
  • Not the problem but `ActiveSheet.Unprotect` should probably be `Me.Unprotect` and that line should probably be inside the If block – Tim Williams Jan 08 '19 at 18:52
  • Have you tried Application.Run("ModuleName.MacroName") ? This works for private subs as well as public. – Cyril Jan 08 '19 at 18:53
  • Regarding toggling Application.EnableEvents, I recommend executing the command `Application.EnableEvents = True` in the immediate window instead of making a new subroutine just for that purpose. You can also check the status of EnableEvents that way (`print Application.EnableEvents`) without having to start up the program. – MBB70 Jan 08 '19 at 20:37

1 Answers1

0

While Worksheet_Change would not run any code, Worksheet_Activate did, and it seems to have done the trick. I added a simple Range("D2").Select and it started working. No idea what caused this, and it was only one of the five users who were testing the document, but it's working now.

Reece
  • 79
  • 2
  • 11
  • `Worksheet_Change` fires correctly for a sheet where changes occurred, regardless of whether that sheet is active. Your code inside the `Worksheet_Change` handler appears to assume that the current sheet is active when the handler runs; that is not necessarily the case. Use `Me` instead of `ActiveSheet` in the handler, and prepend [all `Range` calls](https://stackoverflow.com/q/17733541/11683) with `Me.` too. – GSerg Jan 08 '19 at 21:11