How to fix run time error 400 which occurs only in shared mode of excel via VBA code

Question

I really don't know what causes error 400. Below code runs perfectly fine in normal mode but as soon as i enable my excel in sharing mode and tries to user form, it gives me VBA 400.

What i am trying to do here is to change shape's text and disable its OnAction event, once user form is shown to user. so that another user accessing same file will come to know that someone is using "User Form" to enter data.

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
    If shp.TextEffect.Text = "Sort Customer" Then
        shp.OnAction = ""
        shp.TextEffect.Text = "Wait!!!"
    End If
Next

Q. Is there any way to publish changes made by any user in shared excel automatically.

Possibly of some interest: [Excel drop down in shared mode](https://stackoverflow.com/questions/27324566/excel-drop-down-in-shared-mode) — T.M., Aug 22 '19 at 16:23
Depending on where your entered data is published in the sheet, you might be able to restructure your code to make sure people can work on it at the same time without clashes. Changes by other uses are automatically updated upon saving the workbook, so my code has the structure: 1. Enter data on userform. 2. Save workbook while retaining data. 3. Determine available blank space to paste data. 4. Save data. 5. Save workbook. Any clashes retain data and restart from step 2. This would negate the need to block people out while it's being worked on by others. — Plutian, Aug 27 '19 at 08:31

score 0 · Answer 1 · edited Aug 27 '19 at 10:58

0

I suspect that your code falls in one of the numerous limitations of Excel shared mode, described here (see unsupported features), including

Using a data form to add new data
Using drawing tools
Inserting or changing pictures or other objects

(Please note that, due to its format, I could not easily copy that list of unsupported features in my answer.)

edited Aug 27 '19 at 10:58

Mikku

6,538
3
15
38

answered Aug 27 '19 at 08:06

iDevlop

24,841
11
90
149

score 0 · Answer 2 · answered Aug 27 '19 at 22:39

As far as I know, in order to keep the changes you should choose if the first one who introduces the data rules or you will choose in case of conflict. As you are looking for an "automatic" way, you should chose the first one.

You can find a good explanation described here

At Review > Share Workbook , Advanced Tab. At "Conflicting changes between users", you should chose "The changes being saved win". So as the data are introduced and saved, they are reflected.

Hope it helps.

Andreas · Answer 3 · 2019-09-02T12:13:24.483

Create a vba function in the sheet (NOT A MODULE) where users can activate the user form:

insert the following function there:

Function HyperlinkClick()
    'source: https://stackoverflow.com/a/33114213/11971785

    Set HyperlinkClick = Range("B2")

    If HyperlinkClick.Value = "Sort Customer" Then
        'sets info on WAIT
        HyperlinkClick.Value = "WAIT!!!"
        'shows userform
        UserForm1.Show
    Else
        'sets info back to normal value
        HyperlinkClick.Value = "Sort Customer"
    End If

End Function

In the user form you can add an userform_terminate Event, which automatically changes the value in B2 back (I guess you could also do that for an workbook Close Event be on the safe side).

Private Sub userform_terminate()

    'Code goes here
    Range("B2").Value = "Sort Customer"
End Sub

In Excel now create a "Frontend" such as this:

and add the formula:

=HYPERLINK("#HyperlinkClick()";"Click")

to the cell where a user needs to click to open the UserForm (in this case to D2).

If you now share the workbook and click on "Click" in D2 an Event is triggered and the VBA Function "HyperlinkClick()" is called. In this function you can essentially do anything now.

Explaination: Instead of using a graphic, button etc. which will not work correctly in shared mode, we can simply use links (which work) to trigger an Event. Instead of "creating" and "deleting" Hyperlinks (which also does not work in shared mode) we simply build dynamic links which Point to userform.show or to nothing, depending of the situation.

Error 400 Problem: Should be solved by skipping the modify object part of the code.
Multiple User Problem: Should be solved, since only one user can activate the userform.
Is there any way to publish changes made by any user in shared excel automatically.: I guess so, please provide more information on what exactly you want to achive (incl. example).

Tip: In General you might want to check out MS Access since it has as default feature multi-user Access and users there can use the same form at the same time, since the users only get exclusive Access for specific datapoints not the whole table/workbook or file.

How to fix run time error 400 which occurs only in shared mode of excel via VBA code

3 Answers3

Linked