Allowing Multiple user to enter data from a sinlgle userform

Create an Add-in with the userform in. Distribute this to your users then you can have the form open on a Shared workbook.

Originally Posted by rv02

Dear Neil

Thanks for this. Can I please ask on how to do this , can you explain a bit more on what Add-in do I need.

sorry if I sound silly

Nothing silly about that at all, I would have asked teh same question.

@ Niel, making a suggestion like that without finding out the OP's skill/knowledge level 1st, really does not help at all.
You need to specify, for instance...
what add-in to create
how to create that
where to put it
how to use it
hoe to distribute it
how to set up a shared WB

Ok,
start with a blank workbook. Export your userform by right clicking on it in the project explorer and choosing Export.
Import the user form into your new workbook.
Add a new module to the workbook. Create a subroutine to show the userform, something like:-

Please Login or Register  to view this content.

Save the new workbook as an xlam file, Excel add-in.
Make sure your new add-in is activated in the File>Add-Ins menu.
The Addin will live somewhere like 'C:\Users\myname\AppData\Roaming\Microsoft\AddIns' in a shared network domain. If unsure, you can see the path in the Add-Ins 'Browse' dialogue.
Distribution can be trickier. In an ideal world you would get your network administrator to publish it as a distributed add in, but this is rarely an option. The bigger the network you are a part of, the less likely IS Admin will care about an individual's tinkering.
The simplest way is to email your add in to users. opening the file from the email is usually enough to get the install process started. Again, the user needs to check that the add in is activated in File>Addins
you need a button to launch your userform. With older versions of Excel, this could easily be accomplished. Since the addition of the Ribbon it is much trickier. The simplest method is by hand.

To add a macro to your Quick Access Toolbar:-
click on the drop down of your Quick Access Toolbar menu (very top left of your Excel window)
Click 'More Commands'
Choose 'Macros' from the 'Choose Commands From' combo box
Choose the 'ShowForm' macro you created to launch the userform and click add
Click the modify button and choose a nice icon and friendly name for the macro.

You should now have a pop-up user form to control the Workbook.
You may want to change your code to be specific about the workbook name. i.e. check it is open, and maybe bring it into focus by Activating the sheet the user form pertains to etc. You could even add code to open the workbook.

You have a couple of options with a shared workbook. The legacy method is to go to the Review tab and 'Share Workbook' from there. The options in this dialogue are quite self explanatory. The workbook must be saved on a shared network drive.

As you are on 365, your company may have SharePoint installed. You can save the file to your OneDrive or Team Site and share the workbook from there. I would choose this option as a preference as it enables on-line collaboration.

If you have any problems with creating your add in, attach your original spread sheet minus any sensitive data and I will look at creating one for you.