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]

Have you set Cancel = True somewhere in the double click code?

 

[mrmmickle1]

Peter_SSs,

Thank you for the quick response. Here is some of the code that is used with the event. I hope this will help to get to the underlying issue. Here is the event:

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_BeforeDoubleClick([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As[/COLOR] Range, Cancel [COLOR=#0000ff]As Boolean[/COLOR])
[COLOR=#008000]'This event launches the ReprepForm.  [/COLOR]

    [COLOR=#0000ff]If [/COLOR]Target.Column = 1 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]Call[/COLOR] ReprepFormShow(Target)
[COLOR=#0000ff]    End If[/COLOR]


End Sub

[COLOR=#0000ff]Sub [/COLOR]ReprepFormShow([COLOR=#0000ff]ByVal[/COLOR] Target [COLOR=#0000ff]As[/COLOR] Range)    
    cmdButtonCount = 0
    ReprepForm.Tag = Target.Address(, , , 1)
    [COLOR=#0000ff]Call[/COLOR] LoadReprepForm
    cmdButtonCount = [COLOR=#0000ff]Empty[/COLOR]
    [COLOR=#0000ff]Erase[/COLOR] cmdButtons


[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] LoadReprepForm()


    Load ReprepForm
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(0) [COLOR=#008000] 'The AddControInPage Procedure is quite large.  It adds the textboxes, labels and controls into the UserForm[/COLOR]
    [COLOR=#0000ff]Call [/COLOR]AddControlInPage(1)
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(2)
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(3)
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(4)
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(5)
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(6)
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(7, [COLOR=#0000ff]True[/COLOR])
    [COLOR=#0000ff]Call[/COLOR] AddControlInPage(8)
   
    ReprepForm.Show

[COLOR=#0000ff]End Sub[/COLOR]

Please let me know if anything additional would help to nail down the root cause of the issue. I try my hand at this stuff, but I have to confess that this code is way above my head. I got alot of help from a regular board contributer Lalit Mohan Pandey. He is the author of this code.

Thank you for your efforts. I appreciate any feedback you can provide with my issue.

 

[Peter_SSs]

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_BeforeDoubleClick([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As[/COLOR] Range, Cancel [COLOR=#0000ff]As Boolean[/COLOR])
[COLOR=#008000]'This event launches the ReprepForm.  [/COLOR]

    [COLOR=#0000ff]If [/COLOR]Target.Column = 1 [COLOR=#0000ff]Then[/COLOR]
        [B][COLOR="#FF0000"]Cancel = True[/COLOR][/B]
        [COLOR=#0000ff]Call[/COLOR] ReprepFormShow(Target)
[COLOR=#0000ff]    End If[/COLOR]


End Sub

Try making the change shown in red.

 

[mrmmickle1]

Peter_SSs,

This seems to fix the issue. Thank you for your assistance. I appreciate your input!!

On a side note: What does this extra line of code do to help prevent the issue I was experiencing?

 

[Peter_SSs]

Peter_SSs,

This seems to fix the issue. Thank you for your assistance. I appreciate your input!!

Good news.

On a side note: What does this extra line of code do to help prevent the issue I was experiencing?

Excel has a setting that allows or disallows the user to edit the contents of a cell by double clicking that cell rather than having to edit in the formula bar. I suspected your user with the problem has the setting to allow such edits ("On this individual's computer the cursor does appear in the cell without the Userform launching....") while everybody else has the setting to disallow.

The extra line I included tells Excel to ignore the user's standard procedure for a double-click event.

To check the user's setting: File|Options|Advanced|Editing options section|Allow editing directly in cells

 

[mrmmickle1]

Peter_SSs,

Thank you for the assistance. It seems that I may have spoken too soon. Yesterday, when I unshared the workbook and made the changes to the code that you gave me:

[B][COLOR=#ff0000]Cancel = True[/COLOR][/B]

It seems as if my problem had been fixed. However, as soon as I arrived at work this morning I was notified by the same employee that the issue was happening again. I looked on the menu as you specified to check the user's settings:

To check the user's setting: File|Options|Advanced|Editing options section|Allow editing directly in cells

In this section I found that the user's settings where True (Checked). I tried toggling this feature on and off and regardless of what I did the Before_DoubleClick Event would not work.

It seems I am back at square one. Do you know of any other reason this might be occurring on this individual's computer?

I really appreciate you sharing your personal knowledge on the subject matter and assisting me with this issue. Thanks in advance for any additional information you can provide.

 

[Peter_SSs]

Do you know of any other reason this might be occurring on this individual's computer?.

Let's just check if the doubleclick event is firing at all. Unshare the workbook, make the change shown below, then re-share the workbook & test on that person's machine again. Do you get the message box pop up?

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_BeforeDoubleClick([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As[/COLOR] Range, Cancel [COLOR=#0000ff]As Boolean[/COLOR])
[COLOR=#008000]'This event launches the ReprepForm.  [/COLOR]

   [COLOR="#FF0000"] MsgBox "Event fired"[/COLOR]

    [COLOR=#0000ff]If [/COLOR]Target.Column = 1 [COLOR=#0000ff]Then[/COLOR]
        Cancel = True
        [COLOR=#0000ff]Call[/COLOR] ReprepFormShow(Target)
[COLOR=#0000ff]    End If[/COLOR]


End Sub

 

[mrmmickle1]

Peter_SSs,

I will keep you posted on this issue. Unfortunately, the employee that was having the issue works an odd schedule. (3AM-11AM, GMT -5) and had left by the time I had a chance to go and troubleshoot on her computer. I planned to look into this issue first thing this morning but of course, it was working properly again today????

Not sure what is causing this intermittent success/failure. I wish I could drill down to the root cause. I appreciate the help. I'll keep you posted the next time the issue presents itself. Thanks for taking the time to look into this issue!

 

[mrmmickle1]

Peter_SSs,

It seems that it is not my day ahahah The error with the Before_DoubleClick event is occurring again. This time it occurred on a different employee/user's computer. I was able to test the code that you supplied in an effort to see if the event was firing on this specific computer:

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_BeforeDoubleClick([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As[/COLOR] Range, Cancel [COLOR=#0000ff]As Boolean[/COLOR])
[COLOR=#008000]'This event launches the ReprepForm. 
[/COLOR][COLOR=#FF0000]
            MsgBox "Event fired"[/COLOR] 

[COLOR=#0000ff]        If [/COLOR]Target.Column = 1 [COLOR=#0000ff]Then[/COLOR] 
             Cancel = True 
[COLOR=#0000ff]             Call[/COLOR] ReprepFormShow(Target)
[COLOR=#0000ff]       End If

[/COLOR][COLOR=#0000ff]End Sub[/COLOR]

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

Thank you so much for assisting me with this issue.