Error varying from System to System

[wilro85]

I'm using the following code and it works on some computers and doesn'
work on others.

The computers that it doesn't work on generates the same error in th
same place. (Bolded). I had it suggested to add the "Dim MyCellValu
As String" into the code and I have tried it in two places (not at th
same time) which are both shown in italics.

Does anyone have any idea what is going on? Why would it work on som
computers but not on all of them?


Sub CommandButton2_Click()
-Dim MyCellValue As String-
If CheckBox2.Value = True And Range("A25").Value = "" O
CheckBox5.Value = True And Range("a43").Value = "" Or _
Worksheets("page1").CheckBox2.Value = True An
Worksheets("page1").Range("a28").Value = "" Or _
Worksheets("page1").CheckBox3.Value = True An
Worksheets("page1").Range("a34").Value = "" Then
MsgBox ("You have indicated a PO number, ACE number, Project Modul
Number, or Customer Estimate" & vbCrLf & _
"exists but did not specify a value. Please correct this mistake.")
ElseIf Range("a49").Value = "" Then MsgBox ("Due Date Required")
Else
-Dim MyCellValue As String-
*MyCellValue =* Sheets("Page1").Range("a19").Value
LResponse = MsgBox("Do you wish to submit the Engineering Request For
for " & MyCellValue & "?", vbYesNo, "Day Wireless System
Engineering")
If LResponse = vbYes Then
Call WBunlock
Sheets("ERFSummary").Visible = True
Sheets("ERFSummary").Range("B39").Value = Range("A43").Value
ActiveWorkbook.SendMail "(e-mail address removed)", _
"Engineering Request Form " & MyCellValue
True
MsgBox "The Form has been Sent", vbOKOnly, "Day Wireles
Systems Engineering"
If CheckBox3.Value = True Then
ActiveWorkbook.FollowHyperlin
Address:="http://sharepoint.dayintranet.com/engineering/Project Forms/Coverage Map Request Form.XLT"
_
NewWindow:=True
End If
Else
MsgBox "The Form was Not Sent", vbOKOnly, "Day Wireless System
Engineering"
End If
End If
End Su

 

[RB Smissaert]

What is the error you get?
Try:
Dim MyCellValue As Variant

RBS

 

[wilro85]

The error I was recieving was...
Compile Error: Can't find Project or Library

I'll give that a try and see what happens

 

[wilro85]

Same error, but this time it highlighted a different spot. See bold.

Sub CommandButton2_Click()
Dim MyCellValue As Variant
If CheckBox2.Value = True And Range("A25").Value = "" O
CheckBox5.Value = True And Range("a43").Value = "" Or _
Worksheets("page1").CheckBox2.Value = True An
Worksheets("page1").Range("a28").Value = "" Or _
Worksheets("page1").CheckBox3.Value = True An
Worksheets("page1").Range("a34").Value = "" Then
MsgBox ("You have indicated a PO number, ACE number, Project Modul
Number, or Customer Estimate" & vbCrLf & _
"exists but did not specify a value. Please correct this mistake.")
ElseIf Range("a49").Value = "" Then MsgBox ("Due Date Required")
Else
MyCellValue = Sheets("Page1").Range("a19").Value
*LResponse =* MsgBox("Do you wish to submit the Engineering Reques
Form for " & MyCellValue & "?", vbYesNo, "Day Wireless System
Engineering")
If LResponse = vbYes Then
Call WBunlock
Sheets("ERFSummary").Visible = True
Sheets("ERFSummary").Range("B39").Value = Range("A43").Value
ActiveWorkbook.SendMail "(e-mail address removed)", _
"Engineering Request Form " & MyCellValue
True
MsgBox "The Form has been Sent", vbOKOnly, "Day Wireles
Systems Engineering"
If CheckBox3.Value = True Then
ActiveWorkbook.FollowHyperlin
Address:="http://sharepoint.dayintranet.com/engineering/Project Forms/Coverage Map Request Form.XLT"
_
NewWindow:=True
End If
Else
MsgBox "The Form was Not Sent", vbOKOnly, "Day Wireless System
Engineering"
End If
End If
End Su

 

[RB Smissaert]

It sound there is a missing reference.
Look in the VBE under Tools, References.
Maybe you need to save the workbook without the reference and add it in code
in the Workbook_Open event.

RBS

 

[NickHK]

Many of us read these posts through Usenet/news reader set to text only.
Consequently, no text is bold or italic, so we have no idea of the location
of the problem.

I assume you initial check is a series of (Condition1 And Condition2) Or
(Condition3 And Condition4).. etc
Not like: (Condition1 And (Condition2 Or Condition3)) And Condition4..
The brackets help make it clear.

As RBS suggested, check the Tools>References in the VB IDE and uncheck any
that are marked "Missing".

NickHK

 

[wilro85]

It sound there is a missing reference.
Look in the VBE under Tools, References.
Maybe you need to save the workbook without the reference and add it in
code
in the Workbook_Open event.

This is correct. It was the referance to Adobe Distiller that is used
in another program that is causing this error. Now I need to know how
to fix it.

A later program that I use turns the sheet into a PDF and then e-mails
it, hence the referance, but I don't have to have that enabled until
after this particular button is pressed. Once this button is pressed,
it is sent to our computers which all have acrobat loaded on them.
How would I go about loading a referance via a macro?

 

[RB Smissaert]

OK, easiest is to do it via the GUID of that library and you can get that
with this code:

Sub GetLibraryGUID()

Dim c As Byte
Dim myCheck As Long
Dim P As Boolean
Dim rng As Range
Dim i As Byte

c = ActiveWorkbook.VBProject.References.Count

On Error Resume Next
Dim Message, Title, Default, T As Single
Message = "NUMBER ?" & Chr(13) & "________"
Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
Default = c
T = InputBox(Message, Title, Default, 3500, 3500)

If Not T Mod 1 = 0 Then
Exit Sub
End If

If T < 1 Or T > c Then
Exit Sub
End If

MsgBox "REFERENCE ( " & T & " ) NAME : " & _
ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _
"MAJOR : " & _
ActiveWorkbook.VBProject.References.Item(T).Major & _
vbCrLf & vbCrLf & "MINOR : " & _
ActiveWorkbook.VBProject.References.Item(T).Minor & _
vbCrLf & vbCrLf & _
"GUID ( " & T & " ) : " & _
ActiveWorkbook.VBProject.References.Item(T).GUID, , _
" REFERENCES GUID : ITEM " & T

myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
vbYesNo, " GetLibraryGUID")

If myCheck = vbNo Then
Exit Sub
End If

If ActiveSheet.ProtectContents = True Then
P = True
ActiveSheet.Unprotect
Else
P = False
End If

Range(Cells(ActiveCell.Row, ActiveCell.Column), _
Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select

For Each rng In Selection.Cells
If Not IsEmpty(rng) Then
i = i + 1
End If
Next

If i > 0 Then
myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
vbYesNo, " GetLibraryGUID")
If myCheck = vbNo Then
Exit Sub
End If
End If

On Error Resume Next
ActiveCell.Value = "NAME :"
ActiveCell.Offset(1, 0).Value = "MAJOR :"
ActiveCell.Offset(2, 0).Value = "MINOR :"
ActiveCell.Offset(3, 0).Value = "GUID :"
ActiveCell.Offset(0, 1).Value = _
ActiveWorkbook.VBProject.References(T).Name
ActiveCell.Offset(1, 1).Value = _
ActiveWorkbook.VBProject.References.Item(T).Major
ActiveCell.Offset(2, 1).Value = _
ActiveWorkbook.VBProject.References.Item(T).Minor
ActiveCell.Offset(3, 1).Value = _
ActiveWorkbook.VBProject.References.Item(T).GUID

If P = True Then
ActiveSheet.Protect
End If

End Sub


Then to add the reference use this function:

Function AddReferenceFromGUID(strGUID As String, _
lMajor As Long, _
lMinor As Long, _
Optional strReference As String = "", _
Optional bRemove As Boolean) As Boolean

Dim R

'no need to carry on if the reference is already there
'-----------------------------------------------------
For Each R In ThisWorkbook.VBProject.References
If R.GUID = strGUID Then
If bRemove Then
ThisWorkbook.VBProject.References.REMOVE R
End If
AddReferenceFromGUID = True
Exit Function
End If
Next

On Error GoTo ERROROUT

'reference wasn't there, so add
'------------------------------
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, _
Major:=lMajor, Minor:=lMinor

AddReferenceFromGUID = True

Exit Function
ERROROUT:

If Len(strReference) > 0 Then
MsgBox "Couldn't add the " & strReference & " reference", , _
"adding references to VB Project"
End If
On Error GoTo 0

End Function


And you would use that like this:

AddReferenceFromGUID "GUIDStringToPutHere", 5, 0

Put the right values in place of the above example values.

Best to keep the last value at 0 or even both at 0, for in case other users
are having a lower version.

Run this in Workbook_Open


RBS