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

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

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.

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

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

Dim wb As Workbook

Application.ScreenUpdating = False

Set wb = Workbooks.Open("\\SomServer\MyWorkbook.xlsb")

With wb.Sheets("sheet1")
    .Range("a1").Value = "sdfsdf"
    .Range("b1").Value = "sdfgdg"
End With

wb.Close True

Application.ScreenUpdating = True

#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

Have you tried my suggestion?

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

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

Private Sub UserForm_Initialize()
 Dim wb As Workbook
 
 wb.Close True

Application.ScreenUpdating = True

With Application

        .WindowState = xlMaximized

        Zoom = Int(.Width / Me.Width * 85)

        Width = .Width

        Height = .Height

    End With
End Sub

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:

Private Sub Comboboxx1_Change()
 Dim wb As Workbook

Application.ScreenUpdating = False


Set wb = Workbooks.Open("Z:\VBA gekloot\Userform\Voortgangproduktiestart.xls")
With wb.Worksheets("MAIN")
 Dim Boot As Double
    Dim Boot2 As Integer
    
    Boot = Comboboxx1
   
    Row_Num = WorksheetFunction.Match(Boot, wb.Worksheets("MAIN").Range("D:D"), 0)
    Boot2 = 31
    Col = Boot2

    
    Label6.Caption = (wb.Worksheets("MAIN").Cells(Row_Num, Col).Value)

Dim Rex As Double
Dim Rex1 As Integer

Rex = Comboboxx1

Row_Num1 = WorksheetFunction.Match(Rex, wb.Worksheets("MAIN").Range("D:D"), 0)

Rex1 = 32

Label7.Caption = (wb.Worksheets("MAIN").Cells(Row_Num1, Rex1).Value)

wb.Close True

Application.ScreenUpdating = True
End With

And exactly the same for the OK button.

Private Sub CommandButton1_Click()
Dim wb As Workbook

Application.ScreenUpdating = False


Set wb = Workbooks.Open("Z:\VBA gekloot\Userform\Voortgangproduktiestart.xls")
With wb.Worksheets("MAIN")

If Not IsNumeric(TextBoxx1.Text) Then
TextBoxx1.Text = ""
MsgBox " Alleen cijfers invullen bij aantalstuks.", vbInformation
Else
    
Dim Steven2 As String
Dim Steven As Double
Dim Steven4 As String
Dim Steven5 As String






Steven = Comboboxx1
Steven2 = ComboBoxx2
Steven3 = TextBoxx1
Steven4 = ComboBoxx3
Steven5 = Round((Steven3 / Label6.Caption), 2)
Col = WorksheetFunction.Match(Steven2, wb.Worksheets("MAIN").Range("3:3"), 0)
Row_Num = WorksheetFunction.Match(Steven, wb.Worksheets("MAIN").Range("D:D"), 0)

If wb.Worksheets("MAIN").Cells(Row_Num, Col) > 0 Then MsgBox ("Er is al een aantalstuks ingevuld voor Ordernummer " + Comboboxx1 + " en de ploeg van " + ComboBoxx2)


If wb.Worksheets("MAIN").Cells(Row_Num, Col) = "" Then


Dim YesOrNoAnswerToMessageBox As String


YesOrNoAnswerToMessageBox = MsgBox("Je hebt de volgende data ingevoerd    Ordernummer:" + Comboboxx1 + "  Dienst: " + ComboBoxx2 + " en aantal stuks " + Steven3 + " is deze data correct?", vbYesNo, "Controle")
If YesOrNoAnswerToMessageBox = vbNo Then MsgBox (" De opdracht is gecancelled vul opnieuw de gegevens in.")
If YesOrNoAnswerToMessageBox = vbYes Then

Dim OKAnswerToMessageBox As String

OKAnswerToMessageBox = MsgBox("De data is toegevoegd.")

If OKAnswerToMessageBox = vbOK Then wb.Worksheets("MAIN").Cells(Row_Num, Col) = Steven3
wb.Worksheets("Ronnie controle").Cells(Row_Num, Col) = (Steven3 & " " & ComboBoxx3 & " " & Steven5 & " " & "Uur")



End If



End If
  

End If

wb.Close True

Application.ScreenUpdating = True
End With


    

End Sub

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

Double post my bad.