multiply simultaneous users using one user form?

[MrZeal]

So I have just finished a userform for data entry for a department at work. They have now said that they would like to be able to use the same form together, e.g if one person is using it that someone else should be able to open the form and use it at the same time.... I now think that although its taken near on 2 weeks to get right that access would have been a better solution. That said does anyone know whether something like this can be acheived with excel so that I dont have to do a crash course on access and spend more time on the same thing again. We currently use office 2010 however there is scope to move these users to 2016 if that helps in this example?

 

[dmt32]

Hi,

For multi users Access is probably a better solution but Excel can be made to work albeit in a limited way.

If your userform just adds data to a worksheet in your workbook then it is perfectly possible to make that worksheet a master (database) workbook & place this on your network. Each of your users can then have their own copy of your workbook to submit their data & it matters not if they leave their copy open.

You would need to understand though that whilst users need to write data to the master workbook, you cannot have it open read/write mode.

For a Function I created to manage multi-users submitting data to a master workbook Have a look here:VBA Workbooks.open prompting for password if somebody else is using the workbook

#post 2 - I renamed the function from original name OpenDatabase to suit OP I was helping but it was just a name change you can name it anything you like so long as you update function correctly.



Hope Helpful



Dave

 

[MrZeal]

Thank you Dave, can I just check that if I want the users to get past the read only option when opening an already open userform, I paste the function you provided previously in the standard module and that's it?

excuse my ignorance I am still very new to all this

 

[dmt32]

Thank you Dave, can I just check that if I want the users to get past the read only option when opening an already open userform, I paste the function you provided previously in the standard module and that's it?

excuse my ignorance I am still very new to all this

The function is placed in a standard module & you pass to it
- FileName (including filepath)
- Readonly True - open readonly, False- open read/write
- Password (optional) to open.

ReadOnly Parameter was meant to be optional I just omitted to include it.
You would use ReadOnly = True to open master workbook just to return data to userform - this reduces chances of conflicts if another user is about to write data at same time.

Dave

 

[MrZeal]

Im sorry I must be being a bit slow on the uptake here but when I add the code into the module nothing new is happening, I assume I am missing something fundementally here. Its not just a case of copying and pasting the code at the end of my current code is it?

 

[dmt32]

The function is used to Open your database workbook on your network & will warn users if they are trying to open it in read/ write mode at same time as another.

If you are unsure how to configure your workbook - place a copy in a dropbox & provide a link to it here - I will take a further look for you

Dave

 

[MrZeal]

I am trying to make it so that multiple users can enter data at the same time rather than warn them someone is already in it. Is that even a thing? is that possible?

 

[dmt32]

I am trying to make it so that multiple users can enter data at the same time rather than warn them someone is already in it. Is that even a thing? is that possible?

It sounds like you probably need to be looking at a relational database system like Access maybe - You can create "front end" workbooks for your users that connect to a central Database application & if this is what you want to do then best to head to the Access Forum here for advice & guidance.

Dave