Ian,
It is certainly possible to use code to modify other code or other
attributes of the
VB Project of a workbook. See
http://www.cpearson.com/Excel/vbe.aspx for details and examples.
You could distribute a workbook to the end users that would fix any
troublesome workbooks the user may have. Put the following code in a code
module of a workbook, link it to a button on Sheet1, and send this workbook
out to the users. They should have this workbook and the workbook with the
bad reference open at the same time, and when they click on the button on
Sheet1 of this workbook, the code will run and will either delete and
recreate the reference to RefEdit.dll, remove and not recreate the reference
to RefEdit, or do nothing at all. The action (or lack thereof) is user
choice. This workbook requires a reference to the "Microsoft Visual Basic
For Applications Extensibility 5.3 Library" (in VBA, Tools menu, Reference,
check this entry in the list).
The code will look for RefEdit.DLL in Application.Path. If it is not found
there, the user is given the choice of quitting the operation or searching
for RefEdit.dll themselves.
Sub FixRefEdit()
Dim FName As Variant
Dim OldDir As String
Dim VBProj As VBIDE.VBProject
Dim Ref As VBIDE.Reference
Dim WB As Workbook
Dim Res As VbMsgBoxResult
Dim RefEditFileName As String
Dim WBName As String
WBName = InputBox("Enter the name of the workbook to update.")
If WBName = vbNullString Then
MsgBox "No workbook name entered. Cancelling operation", vbOKOnly
Exit Sub
End If
On Error Resume Next
Set WB = Workbooks(WBName)
On Error GoTo 0
If WB Is Nothing Then
MsgBox "Cannot find workbook:" & vbCrLf & _
WBName, vbOKOnly
Exit Sub
End If
RefEditFileName = Application.Path & "\RefEdit.dll"
If Dir(RefEditFileName, vbNormal) = vbNullString Then
Res = MsgBox("The RefEdit file was not found in the expected location:"
& vbCrLf & _
RefEditFileName & vbCrLf & _
"Do you want to search for it yourself?" & vbCrLf & _
"Click 'Yes' to search for the file." & vbCrLf & _
"Click 'No' to terminate this operation", vbYesNo)
If Res = vbNo Then
Exit Sub
End If
OldDir = CurDir
ChDrive Application.Path
ChDir Application.Path
FName = Application.GetOpenFilename("DLL Files,*.dll", , "Search For
RefEdit.dll")
ChDrive OldDir
ChDir OldDir
If FName = False Then
Exit Sub
End If
If InStr(1, FName, "Refedit", vbTextCompare) = 0 Then
' are we sure that the user choose RefEdit.DLL?
Res = MsgBox("The selected file:" & vbCrLf & _
FName & vbCrLf & _
"does not appeat to be the correct file. Are you sure you" &
vbCrLf & _
"want to use this file?", vbYesNo)
If Res = vbNo Then
Exit Sub
End If
End If
RefEditFileName = FName
End If
Set VBProj = WB.VBProject
If VBProj.Protection = vbext_pp_none Then
On Error Resume Next
Set Ref = VBProj.References("REFEDIT")
On Error GoTo 0
If Not Ref Is Nothing Then
Res = MsgBox("RefEdit reference found. Do you want to update it?" &
vbCrLf & _
"Click 'Yes' to remove and recreate the reference to RefEdit." &
vbCrLf & _
"Click 'No' to remove the reference to RefEdit." & vbCrLf & _
"Click 'Cancel' to do nothing with the reference to RefEdit.",
vbYesNoCancel)
Select Case Res
Case vbYes
On Error Resume Next
VBProj.References.Remove Ref
VBProj.References.AddFromFile RefEditFileName
On Error GoTo 0
Case vbNo
VBProj.References.Remove Ref
Case Else
' do nothing
End Select
End If
End If
If WB.Saved = False Then
WB.Save
End If
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Ian" wrote in message
...
Thanks Chip and Tom.
For some reason the workbook was looking for Ref Edit Control
(REFEDIT.DLL). As I have already deployed this workbook to 20+ other
people, is it going to be a case of replacing the entire workbook, or can
I use code in another sheet to update this?
I already use this method to apply periodic modifications to workbooks,
but that is limited to replacing ranges within the sheet, not modifying
the code (or in this case references) in the workbook. Indeed, I don't
even know if it is possible to do either of these.
Many thanks.
Ian
"Chip Pearson" wrote in message
...
It sounds like one or more of the VBA Type Library references got screwed
up. See www.cpearson.com/Excel/MissingReferences.aspx for a description
of the problem and several remedies to try to fix the problem.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Ian" wrote in message
...
I have created a number of spreadsheets for use on numerous laptops,
currently running Office 2000. These spreadsheets work fine on my laptop
and previous versions ran OK on all the remote laptops. The latest issue
one of these sheets appears to be causing problems on other systems, but
works fine here.
I've tried this sheet on 2 local PCs (running Office 2k & Office XP) and
see these problems. There are 2 manifestations of what seems to be the
same problem.
Initially an input form runs. This requires certain inputs before the OK
button is enabled. At any stage a Cancel button is available.
1. No inputs are made and the Cancel button is clicked.
The code stops with a compile error stating Can't find project or
library.
The code in question is:
With Worksheets("Lookup")
Set Early_8000_23 = .Range("Early_8000_23") '\
End With
There are many other "Set" statements following this, but however many I
comment out, the first one available produces the same error. The
section of the line highlighted is the first "Early_8000_23".
2. All required inputs except one are made and CommandButton3 is
clicked. This should enter the current date into the TextBox2. The code
causing problems is:
Private Sub CommandButton3_Click()
TextBox2.Value = Format(Date, "dd/mm/yyyy")
End Sub
The reference highlighted here is "Date"
CommandButton3 has been clicked as part of the input process and has
entered the current date correctly in TextBox2.
The real questions a
1. Why should this be happening now, when a previous issue of these
sheets worked fine (these particular parts of the code haven't changed)
2. Why should this particular workbook be affected when the others
(using exactly the same Date code and very similar Set statements) work
fine.
There's obviously something specific to this workbook, but I can't
figure out what. It would appear that it isn't specifically related to
the code highlighted. I checked the add-ins on my laptop and there
aren't any selected, so that's not the issue.
The only thing I can see is that my laptop is running VB6.5 (Office 2k
SP3) whereas my other Office 2k is only VB6.0 (not even SP1 on this
machine) and Office XP is VB6.3 (SP3). This seems odd as office XP is
newer than Office 2000 so I would have thought XP SP3 would have
included at least VB6.5.