VBA Shared workbook

[lickrob]

Firstly, Yes, i know Shared Workbooks are no good, however i am unable to use access (or any other Database) in any form so im stuck with a shared workbook.

Basically i have a front end that will add the contents of a userform to a shared workbook.


Code to add to shared WB:

Dim LastCell As String
Dim lol As String
Dim msgerr As String
Dim lolff As Range
If tbCust.Text = "" Or tbCons.Text = "" Or tbTo.Text = "" Or tbFrm.Text = "" Then
MsgBox ("All Fields Must Be Completed")
Else
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'activate new wb could get rid of this maybe if change cells below
Workbooks("NewWB").Activate
Application.DisplayAlerts = False
On Error GoTo err
If Workbooks("NewWB.xls").MultiUserEditing Then
Workbooks("NewWB.xls").AcceptAllChanges
Workbooks("NewWB.xls").Save
End If
Set lolff = Workbooks("NewWB").Sheets(1).Range("A35536").End(xlUp).Offset(1, 0)
LastCell = lolff.Row
'creates the if formula with correct cells to check dates and save in lol string
lol = "=IF(AND(TODAY()>=C" & LastCell & ",TODAY()<=D" & LastCell & "),""Yes"",""No"")"
Application.AlertBeforeOverwriting = False
Cells(LastCell, 1) = tbCust.Text
Cells(LastCell, 2) = lol 'if formula
Cells(LastCell, 3) = CDate(Me.tbFrm.Value)
Cells(LastCell, 4) = CDate(Me.tbTo.Value)
Cells(LastCell, 5) = tbCons.Text
Workbooks("LOA Search").Activate
'save and close NewWB
'Workbooks("NewWB.xls").AcceptAllChanges
Workbooks("NewWB.xls").Save
Workbooks("NewWB.xls").Close
Application.DisplayAlerts = True
'close UserForm
Unload Me
Application.ScreenUpdating = True
End If
End
err:
Application.DisplayAlerts = False
Workbooks("NewWB.xls").Saved = True
Workbooks("NewWB.xls").Close
Application.DisplayAlerts = True
msgerr = MsgBox("An Error Has Occoured" & vbCrLf & "Please Try Again", vbCritical, "Error:")
Unload Me
Application.ScreenUpdating = True



the shared workbook is opened when the userform loads due to the fact that there is an autocomplete on one of the textbox's which requires the shared workbook to be open.

My Problem is when this workbook is opened by 2 poeple at the same time. the second person to save the workbook will get the conflict resoultion popup and have to choose to accept other or their own changes. i do not want this to happen.

Any ideas?

Thanks
Rob

[Jan Karel Pieterse]

Use an Access database as the backend and communicate with it using ADO. You do not need Access to work this way. Just to create the database itself. And even that can be done without Access, you can create an mdb database and tables from VBA entirely.

Of course even then you will have to handle resolving of conflicts yourself!

[lickrob]

Its not a matter of having access to the software. its the fact that i simply cant use a database in any shape or form.
I do actually have microsoft access on my computer, I just cant use it. trust me its really frustrating.

In all fairness its not the biggest problem in the world. Its just sloppy programming.

I take it from that there inst a solution to my problem?

Rob

[Jan Karel Pieterse]

Just curious: What do you mean by can't use Access: you're not allowed to, or does something else get in the way?

[lickrob]

Not allowed, I have tried explaining the benefits of databases however my managers see databases as big scary things. :)

[Jan Karel Pieterse]

:roll: In Dutch we say "not hindred by any subject matter knowledge"

[lickrob]

:roll: In Dutch we say "not hindred by any subject matter knowledge"

haha thats a good saying.

[GTO]

Greetings both,

Mr. Pieterse, I have no example, but am very interested and curious about what may well be the worst idea ever...

In my case, I just have not had time to "absorb" Access to any successful/useful degree. Not any excuse, but given the OP's dilemma, has this been tried(?):

A 'Parent' wb to retain the data.

'Child' wb's (or a shared file) that collects the data and updates the parent when available.

That is, in essence, to keep checking when an exclusive file is available, and when so, open/update/save/close.

Just a thought of course, and in quite rudimentary state at the moment, but an interest for myself as well - as due to some "damn... network seems to be falling over a lot..." issues of late, we recently switched from a shared wb to a number of exclusive wb's drawn via formula (worksheet functions) to a master.

To at least excuse myself from being an utterly shiftless (three words I won't put here), I spent quit a bit of time time tonight researching (to no avail) how to see if a shared wb is open (by another user on a network).

Hope the idea (even if terrible) makes sense.

Mark Stump

[Jan Karel Pieterse]

I get the idea and of course this is doable too. Even with this method your code will have to check for possible in-between updates to the record(s) you would be updating.

[lickrob]

I see what you are saying. using locally stored WB to store the data temporaly and then using that to write to the master wb once all changes have been made / no one else is in the shared wb.

kind of like a dataset when using databases.

it could work, ill have a play arround and see....

[GTO]

I get the idea and of course this is doable too. Even with this method your code will have to check for possible in-between updates to the record(s) you would be updating.

I utterly must hit the rack, it is past 0600 here (and I work at 1400). I'll try my best to check back shortly, as the part about "possible in-between updates" is not making sense to my (admittedly well over-tired) self at the moment. If the 'Parent' or 'Master' file is exclusive, and the 'child' wb's just keep checking when 'they' (ea one individually) could open (lock) the parent, this would block any in-between updates. Am I making sense at all? Apologies if not of course, and thank you for the reply.

I see what you are saying. using locally stored WB to store the data temporaly and then using that to write to the master wb once all changes have been made / no one else is in the shared wb.

No sir. Just as to the 'master' being shared. I was thinking the master woiuld be exclusive, and thus, easily "checkable" as to updating (and saving)

kind of like a dataset when using databases.

LOL... If I really understood that part, I would be developing stuff with an Access back-end. So much to learn, so little time...

Still, while occassionally frustraing, a lot of fun!

[lickrob]

I came up with a solution off the back of GTO's ideas

The problem is arising because the master WB is opened when the userform that allows for adding information is called. It then stays open until all the fields have been completed and the information is added with the ok button. This is because some of the fields on the userform autofill from the master WB.

So if more than one person has the userform (masterWB) open at once they will all try and write to the first blank cell which will be the same for them all causing a conflict resolution.

So I thought why not deligate cells.

Using the


Environ("USERNAME")

Function when a user calls the userform to add information it will write theit username to the first available cell and save the master WB.

Then once they cave completed the userform the following loop will place there information in the cell containing their username.


uName = Environ("USERNAME")

Set lolff = Workbooks("NewWB").Sheets(1).Range("A35536").End(xlUp) '.Offset(1, 0)
iRow = lolff.Row

myCell = Workbooks("NewWB").Sheets(1).Cells(iRow, 1).Value

Do Until myCell = uName
iRow = iRow - 1
myCell = Workbooks("NewWB").Sheets(1).Cells(iRow, 1).Value
Loop



This reduces the chances of a conflict drastically. It hasn’t completely removed the possibility, if 2 users where to call the userform at the same time this will happen. However the chances of that are so low it shouldn’t matter.

I mean its just a thrown together idea so it will need testing .


Anyway any Ideas / Thoughts?

[Jan Karel Pieterse]

I guess that would work.
GTO: I was referring to a situation where two users both load data, make changes and then both try to save. If User A saves before user B, the data user B loaded in the beginning is no (possibly) longer the same data, since user A has saved his changes. Both may even have made changes to the same record(s).