Macro not working for networks other users. "Compile Error: Cannot find project or library"

[andyboles]

Hi All,

I'll start by saying that I'm a bit of a newbie to VBA and macros, this is the first one I've made so I'm hoping that this problem is something pretty simple that I've not picked up on...


I made this macro up for a project in work which was designed to be a spreadsheet that multiple users could access to record sales. I made a macro to create a form so that employees would enter the information into a bax, rather than letting them play about with the data in the spreadsheet which could potentially affect existing data thats recorded.


The macro I made works perfectly fine on whichever computer I log onto but it doesn't work on any of my colleagues. I made the file on in the My Documents then copied and pasted it into a network folder (I dont tknow if this could be causing a problem?)

Anyway, when another user runs the macro, this error message appears:

“Compile error:
Cannot find project or library"


The userform (called frmInputData) is pasted below. The highlighted part with the error message is shown in bold.





Private Sub cmdInput_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Input")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database
ws.Cells(iRow, 1).Value = Date
ws.Cells(iRow, 2).Value = Me.txtManName.Value
ws.Cells(iRow, 3).Value = Me.txtAgentName.Value
ws.Cells(iRow, 4).Value = Me.txtCustNo.Value
ws.Cells(iRow, 5).Value = Me.txtEmailRef.Value
ws.Cells(iRow, 9).Value = Me.txtCHCSales.Value
ws.Cells(iRow, 10).Value = Me.txtPLDRSales.Value
ws.Cells(iRow, 11).Value = Me.txtHECSales.Value
ws.Cells(iRow, 13).Value = Me.txtKACSales.Value


'Product Type

If chkCHC = True Then
ws.Cells(iRow, 6).Value = "CHC"
End If
If chkPDH = True Then
ws.Cells(iRow, 6).Value = "PD&H"
End If
If chkKac = True Then
ws.Cells(iRow, 6).Value = "KAC"
End If




'Sale Recorded - Yes or No?

If optSaleYes = True Then
ws.Cells(iRow, 7).Value = "1"
End If
If optSaleYes = False Then
ws.Cells(iRow, 7).Value = "0"
End If

If optSaleNo = True Then
ws.Cells(iRow, 8).Value = "0"
End If
If optSalesNo = False Then
ws.Cells(iRow, 8).Value = "1"
End If






'clear the data

Me.txtManName.Value = ""
Me.txtAgentName.Value = ""
Me.txtCustNo.Value = ""
Me.txtEmailRef.Value = ""
Me.txtCHCSales.Value = "0"
Me.txtPLDRSales.Value = "0"
Me.txtHECSales.Value = "0"
Me.txtKACSales.Value = "0"
Me.txtManName.SetFocus
End Sub










Any help on getting this running would be massive help!!


Thanks

 

[Richard Schollar]

Hi

Are these other computers where it doesn't work running an earlier version of Excel by any chance?

When the macro fails, try going into the VBE and looking in Tools>References for anything marked as MISSING (and uncheck these if found).

A temporary fix would probably be to amend that line to:

ws.Cells(iRow, 1).Value = VBA.Date

 

[andyboles]

Thanks for your quick reply.


I meant to say in my original post that all computers run off Excel 2003 so there shouldn't be any issues with versions. (Also all are Windows XP).

I did see on another post something asking me to uncheck the MISSING box. Unfortunately it would not let me uncheck this, as this requires administrator rights which I do not have. This would also be unpractical as I intend to have the file working for over 100 staff members in various locations, so I'd like to avoid asking them to alter things to make this work if possible.


I'll try adding in .VBA and let you know how I get on.



Thanks again,
Andy