Double Click Event In Shared WOrkbook

[mrmmickle1]

I have a Shared Workbook that 10 or 15 users are in and out of all day. In Column A on a few sheets I have a Before Double Click Event that launches a userform. The macro works for all users except this one person. I have tried several things:

1. I closed the file and Reopened it, to make sure that the user did not disable macros.
2. I checked the file on other users computers to see if the file was working properly (It was.)
3. I went to Options>Trust Center>Trust Center Settings>Macro Settings and Enabled All Macros on this User's system

None of these actions corrected the issue. Does anyone have any idea would could be happening.

The purpose of the BeforeDoubleClick Event is to store Columns A:E data and then let the user add more information through the form. Once the User fills out the UserForm. A:E is transferred to one of a number of sheets depending on criteria in the UserForm. A:E is transferred along with the new information that the user has entered in the UserForm.

Another symptom is that on other computers when the Before Double Click Event is activated the Userform launches and the cursor does not appear in the cell until after the User Clicks a Command Button to Update Data on the Userform. On this individual's computer the cursor does appear in the cell without the Userform launching....

If anyone has any input or ideas I would love some feedback!

 

[Peter_SSs]

Based on my testing the event is not firing. The msgbox never appeared. What would be the next step in trouble shooting this error?

That would indicate to me that "Events" have become disabled on that machine. Run the following code to report whether events were disabled and, if so, re-enable them.

Sub CheckAndResetEnableEvents()
  Select Case Application.EnableEvents
    Case True
      MsgBox "Events are ENABLED"
    Case False
      Application.EnableEvents = True
      MsgBox "Events were DISABLED but are now ENABLED"
  End Select
End Sub

 

[mrmmickle1]

Peter_SSs,

I have been continually checking this thread. I see that you have provided me a new trouble shooting technique to resolve my issue. I will run this code as soon as the issue reoccurs and report back. As I mentioned the issue is intermittent. I appreciate your assistance on this strange occurrence. I didn't want you to think I have forgot about the problem. It has just, yet to occur this week.

Thanks again for the help. I look forward to getting to the bottom of this!

 

[Peter_SSs]

Thanks for the update.

 

[mrmmickle1]

Peter_SSs,

Good news. The issue occurred again. I was able to test your code on the user's computer. Sure enough you were correct. the MsgBox read:

"Events were DISABLED but are now ENABLED"

I am planning on putting an extra command button in the Shared Workbook in case this happens that will re-enable events. That way the user will be able to fix the issue on their own. I really appreciate the help! This was just what I needed to end the day on Friday! I'll keep you posted if any other strange behavior occurs. Thanks a million.

 

[Peter_SSs]

You should try to track down what is causing the events to be disabled. It is likely to be either ..

1. Some vba code that deliberately disables events and does not re-enable them before exiting the code, or

2. Some vba code that deliberately disables events but crashes (without error handling) before it gets to the part where events would normally be re-enabled.

 

[mrmmickle1]

Peter_SSs,


Today I searched through the entire project for "EnableEvents" using Ctrl + f and then selecting Current Project. The only instance in the entire project this text is found is in the small procedure you provided me.

Other Thoughts Regarding the Issues:
I am not sure what to make out of this. I feel that the problem lies with the workbook being shared. I have multiple procedures that can be launched by shapes, userforms and BeforeDoubleClick events. At any given time 1 to 20 people could be using this workbook. I have also built autosave into some of the procedures in order to prevent overwriting other users changes. I know that the logbook completely crashes at least a few times a week (which I feel is mainly because I have been instructed to make this shared workbook very similar to a database due to my company's limited MS Access licensing). I am also sure that these procedures could be running at the exact same time. I am wondering if this could be creating an issue (Like a criss-crossing of wires...).

I feel the main issue is that the workbook should more or less be an actual database that a large number of people can work in simultaneously. What I have created is a workaround which is leaps and bounds better then what they previously had, but by no means a reasonable solution.

Do you have any other ideas regarding my particular issue?

As always thank you so much for taking the time to look at my posts and to help me. It really means a lot!

 

[Peter_SSs]

Not sure it will help much but I have 2 comments.

1. The issues I mentioned in post #15 would not necessarily need to be in this project. They could be in any project that is open or has been open (& now closed) on that machine.

2. Many of the experienced users in this forum report lots of problems with shared workbooks and avoid them at all costs. To contrast that I use a number of shared workbooks (with vba code) at my workplace on a daily basis and have been doing so for many years with virtually no problems. I guess every case is different.

I'm not sure what else to suggest.

 

[mrmmickle1]

1. Not many of the workbooks around the office have macros in them so I doubt another workbook is disabling the events but, there are a few scattered around.... I will look into this.
2. Just curious: How many users are routinely perusing your shared workbooks?

For now I have added the fix button until I can look into the issue further. Thank you so much for all of your efforts. I will keep you posted if I find the root cause.

 

[Peter_SSs]

1. Not many of the workbooks around the office have macros in them so I doubt another workbook is disabling the events but, there are a few scattered around.... I will look into this.

I've not come across a circumstance where events disable themselves, so something must be doing it. Could you have a "joker" or a disgruntled employee in your workplace who could be playing tricks on your or the other workers?

2. Just curious: How many users are routinely perusing your shared workbooks?

Up to about 15 but generally less than 5 at a time. Also, the workbooks are set up so that it is extremely unlikely that two people would be trying to make changes to the same cell(s) in a worksheet at the same time.


Good luck with your searching for the cause.

 

[mrmmickle1]

Peter SSs,

Unfortunately, I believe this file has exceeded MS Excel's capabilities. It crashed when it reached 11,500 KB.

There were several issues:

1. It was slow to load.
2. On workbook open I had an event that set Iteration to 1. Users commonly received a message that the operation could not be performed.
3. Multiple users saving the file at once was also an issue. (Even though I added an Autosave after Data was Input into a Worksheet to a UserForm)
4. Toward the end users got a message that the Save method of the workbook object would not work
5. There was also an issue with the filing getting "locked" so saves could not be performed
6. Then there was this other locking message documented here: CLICK HERE
7. I feel that 10-15 + Users in the file constantly damaged the files stability

I am now in the process of trying to put this file in MS Access.

Just the same I would love for you to take a look at the file if you have a chance and critique where I may have went wrong.... Is there an acceptable method of this? I would rather not post a link here. Can I send you a Private Message?