Can anyone tidy this code to work for OpenOffice?

[Mr.Daines]

Hi all, i have written this basic code to place data from a user form into a master database (excel workbook). My problem is that the code works brilliantly on excel however other users of this form do not have excel and only have openoffice and the code doesn't work.... any ideas?

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim wb As Workbook
 
'checks for data entry in each drop down
If Trim(Me.TextBoxDate.Value) = "" Then
  Me.TextBoxDate.SetFocus
  MsgBox "Please enter Date."
  Exit Sub
End If
 
If Trim(Me.TextBoxTime.Value) = "" Then
  Me.TextBoxTime.SetFocus
  MsgBox "Please enter Time."
  Exit Sub
End If
 
If Trim(Me.ComboBoxAdv.Value) = "" Then
  Me.ComboBoxAdv.SetFocus
  MsgBox "Please select an advisor."
  Exit Sub
End If
 
If Trim(Me.ComboBoxTeam.Value) = "" Then
  Me.ComboBoxTeam.SetFocus
  MsgBox "Please select Team."
  Exit Sub
End If
 
If Trim(Me.TextBoxRef.Value) = "" Then
  Me.TextBoxRef.SetFocus
  MsgBox "Please enter reference number."
  Exit Sub
End If
 
If Trim(Me.TextBoxCust.Value) = "" Then
  Me.TextBoxCust.SetFocus
  MsgBox "Please enter Customers Name."
  Exit Sub
End If
 
If Trim(Me.TextBoxPC.Value) = "" Then
  Me.TextBoxPC.SetFocus
  MsgBox "Please enter customers Postcode."
  Exit Sub
End If
 
If Trim(Me.ComboBoxRef.Value) = "" Then
  Me.ComboBoxRef.SetFocus
  MsgBox "Please select who you have referred the lead to."
Exit Sub
 
End If
 
'Checks if master in use
Set wb = Workbooks.Open("S:\STB June Fee Data.xlsx")
While wb.ReadOnly
    wb.Close
    Set wb = Nothing
    Set wb = Workbooks.Open("S:\STB June Fee Data.xlsx")
    DoEvents
Wend
Set ws = wb.Worksheets("Raw Data")
 
'finds first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 1).Row
 
'places data into master call log
ws.Cells(iRow, 1).Value = ThisWorkbook.Name
ws.Cells(iRow, 2).Value = Me.TextBoxDate.Value
ws.Cells(iRow, 3).Value = Me.TextBoxTime.Value
ws.Cells(iRow, 4).Value = Me.ComboBoxAdv.Value
ws.Cells(iRow, 5).Value = Me.ComboBoxTeam.Value
ws.Cells(iRow, 6).Value = Me.TextBoxRef.Value
ws.Cells(iRow, 7).Value = Me.TextBoxCust.Value
ws.Cells(iRow, 8).Value = Me.TextBoxPC.Value
ws.Cells(iRow, 9).Value = Me.ComboBoxRef.Value
ActiveWorkbook.Close SaveChanges:=True
 
'Confirms data logged
MSG1 = MsgBox("Submission successful, do you wish to refer another?", vbYesNo, "Congratulations!")
 
If MSG1 = vbNo Then
    Unload Me
Else
Me.TextBoxDate.Value = ""
Me.TextBoxTime.Value = ""
Me.ComboBoxAdv.Value = ""
Me.ComboBoxTeam.Value = ""
Me.TextBoxRef.Value = ""
Me.TextBoxCust.Value = ""
Me.TextBoxPC.Value = ""
Me.ComboBoxRef.Value = ""
Me.TextBoxDate.SetFocus
End If
End Sub
 
Private Sub cmdReset_Click()
Me.TextBoxDate.Value = ""
Me.TextBoxTime.Value = ""
Me.ComboBoxAdv.Value = ""
Me.ComboBoxTeam.Value = ""
Me.TextBoxRef.Value = ""
Me.TextBoxCust.Value = ""
Me.TextBoxPC.Value = ""
Me.ComboBoxRef.Value = ""
Me.TextBoxDate.SetFocus
End Sub
Private Sub UserForm_Click()
End Sub

 

[Ruddles]

Does the version of OpenOffice they're using support Office Automation and VBA?

Do you need some sort of object library to provide the necessary references?

(I've never used OpenOffice so I don't know the answer to these questions.)

 

[RoryA]

OpenOffice does not support VBA, so you'd need to completely rewrite all of that. I don't use OO much, so can't assist either, I'm afraid.

 

[Mr.Daines]

No problem, thanks for your replies.

As i am super new to VBA and have taught myself everything i was also wondering if any one could tidy the coding for VBA.

The idea is that each individual will have a work book called [firstname] [surname].xls which contains the userform, when person 1 submits a referral it is logged on the master file [STB June fee Data.xls] located in a shared network drive, i have managed to get the submission to wait if the master is in read only but i would also like to code in; person presses submit, code runs as written but in addition the contents of the userform are also emailed to myself.

Any ideas?

 

[Norie]

That isn't really 'tidying' up it's more of a totally new thing as far as I can see.

I'm not even sure the code you have now really needs any changes, especially of it's working fine and it's understandable.

There might be one or two minor things you could change but it's probably not really worth it.

Maybe I've missed something though.

 

[Mr.Daines]

That isn't really 'tidying' up it's more of a totally new thing as far as I can see.

I'm not even sure the code you have now really needs any changes, especially of it's working fine and it's understandable.

There might be one or two minor things you could change but it's probably not really worth it.

Maybe I've missed something though.

Its going to be used by 20+ people at any one time, i need to make sure that this works under such conditions. Originally people used to just email me the details and i would fill out the master file, but when im on holiday it is a nightmare and we lose lots of info due to no one updating the sheet; at least if i give each individual a method of placing there own data onto the sheet in a controlled fashion it should be kept up-to-date (given wetware variables).

Regards,

 

[Norie]

I think it might be worth starting a new thread for that.

Perhaps with details of what you've tried so far to implement this part of your process.

 

[Mr.Daines]

No problem, thanks for your replies.

The idea is that each individual will have a work book called [firstname] [surname].xls which contains the userform, when person 1 submits a referral it is logged on the master file [STB June fee Data.xls] located in a shared network drive, i have managed to get the submission to wait if the master is in read only but i would also like to code in; person presses submit, code runs as written but in addition the contents of the userform are also emailed to myself.

Any ideas?

do you know how to code the userform to also email me the info submitted?