More users in 1 Excel file , VBA userform. read only solution?

[stevnb]

Well you probably all know the world famous problem , sharing workbooks = corruption

If several people want to enter data on the same workbook , this is problem.
I Googled , and only got negative advices on sharing workbooks.. because errors occur..

BUT

I have made an userform where you enter data and the data will be placed in this excel file.
Is it possible to make an userformcode/macro which transfers the data from the userform to a CLOSED excel file? And doesn't start on workbook open...

With the use of this maybe?? : Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).R
ange(“A1”)


Because if that is possible , i could run the Userform independant and open it at more computers at the same time. And i wont have to make another macro to put all the data from different workbooks into one master workbook. ( saves me some time)

Yours , Steven

[Kyle123]

There are a few of approaches to this:

1. Set up as you have suggested, just open the second workbook long enough to write the data then close it.
2. Use ADO to insert the data into the closed workbook without opening
3. Use a database on the back-end probably access, but any database would do, so the userform connects to the database and writes the info
4. Use a database and forget Excel
5. Use Google docs and forget excel - this is a good option for capturing data, you can create online forms that automatically insert data into a spreadsheet you can access and edit online and download. You can even have multiple people editing a google spreadsheet.

[stevnb]

Hi Kyle!

Number 1 , sounds like something im looking for, only problem is .... The Userform ment to be open all the time , its monkey proof , full screen and cannot be closed without a password.

But , there is a OK button on the userform , would it be possible to program that OKbutton as Open workbook , add data , and close workbook immediately again? If so ... where can i find some tutorial or explanation how to do that.

Number 2 would fix the job 2, i have never heard of ADO before where can i find some information about this?

Number 3,4,5 yes i know there are lots of solutions acces would be something i would have used, but it has to be EXCEL only because of several reasons.

Yours , Steven

[Kyle123]

You can open a workbook, write to it and close it like this:

Please Login or Register  to view this content.

[stevnb]

#1
Does someone know how to open the userform without opening the linked "excel-file"/workbook? ( if this is even possible)
#2To follow up how to program the userform vbacode to open the "excel-file" /workbook load the source for the comboboxes and imidiatly close it again? ( few miliseconds?)
#3 The person enters data on the userfrom (textboxes) when he presses OK the data should add, (Open workbook , add data , save , close workbook) As fast as possible? what's the vba code to do this?

Yours , Steven

[Kyle123]

Have you tried my suggestion?

[stevnb]

Well I'm not sure where to put it ,

I gues UserForm_Initialize()
Then your code?

Also i have several Match functions :
Example:
Row_number = WorksheetFunction.Match(Boot, Workbooks("Voortgangproduktiestart.xls").Worksheets("MAIN").Range("D:D"), 0)

Will this work then? Or is Workbooks("Voortgangproduktiestart.xls").Worksheets("MAIN") not detailed enough?

Steven

[stevnb]

When you start the userform the workbook is open. So what i did was close the file when the userform pops up.
Like this:

Please Login or Register  to view this content.

Then my idea was to open the file everytime someon pushes a button. For instance the OK button. Then what i want it to do is , open it do whatever is asked ( eg : match number in column with combobox value then put value there ) How i thought of opening it like this:

Please Login or Register  to view this content.

And exactly the same for the OK button.

Please Login or Register  to view this content.

But now i get an error:
Runtime error '91'
Object variable or with block variable not set
Any suggestions what migth cause the problem?

Yours , Steven

[stevnb]

Double post my bad.