Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
After the error has occured, go into the VBE and make sure your workbook is
the activeproject (selected in the project explorer). Then do Tools=References (you may need to hit reset first). You should see at least one reference marked as MISSING. If so, this is the source of your problem and needs to be removed (if not needed) or fixed by finding the correct reference. References are specific to each workbook, so this is consistent with your assessment that it is associated with the particular workbook. -- Regards, Tom Ogilvy "Ian" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
Thanks, Chip. As usual, a very informative response.
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. A very useful article, though the warning about anti-virus raises concerns. It may be necessary for the end users to temporarily disable the AV on their laptops. I'll have to look into that. 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 I think I'll programmatically open the problem workbook and automatically remove the reference. I need to make it as easy as possible as some of the users are not particularly PC literate. When I distribute the fix, it will be with a batch file to copy the workbook to a specific location and open the workbook. The code will run on opening. 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). Is this a standard library on all PCs (or at least, all PCs runningMS Office)? I don't want to end up with any more problems with missing references. As an aside, can you think how the problem reference came to be set? I certainly haven't done it manually, particularly in view of the fact that, once I remove the reference on my own laptop, it disappears from the list of available references. Ian |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
Hi Chip
I've had a chance to try your posted code on another machine with the erroneous reference. I get a run-time error 1004 on the line Set VBProj = WB.VBProject The code is in a new workbook and the reference to Microsoft Visual Basic For Applications Extensibility 5.3 Library is set in this book. The project in the problem workbook is not locked. I tried to output the value of WB with Debug.Print, but nothing was shown. Is this normal? Stepping through the code, after the line Set WB = Workbooks(WBName) I hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of object '_Workbook' failed. Am I missing something obvious? Ian "Ian" wrote in message ... Thanks, Chip. As usual, a very informative response. 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. A very useful article, though the warning about anti-virus raises concerns. It may be necessary for the end users to temporarily disable the AV on their laptops. I'll have to look into that. 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 I think I'll programmatically open the problem workbook and automatically remove the reference. I need to make it as easy as possible as some of the users are not particularly PC literate. When I distribute the fix, it will be with a batch file to copy the workbook to a specific location and open the workbook. The code will run on opening. 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). Is this a standard library on all PCs (or at least, all PCs runningMS Office)? I don't want to end up with any more problems with missing references. As an aside, can you think how the problem reference came to be set? I certainly haven't done it manually, particularly in view of the fact that, once I remove the reference on my own laptop, it disappears from the list of available references. Ian |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
Just a thought. The file I'm trying to fix is a template (xlt), not xls.
Does this make any difference? Also, I've opened the problem file without macros running. Does this matter? Ian "Ian" wrote in message ... Hi Chip I've had a chance to try your posted code on another machine with the erroneous reference. I get a run-time error 1004 on the line Set VBProj = WB.VBProject The code is in a new workbook and the reference to Microsoft Visual Basic For Applications Extensibility 5.3 Library is set in this book. The project in the problem workbook is not locked. I tried to output the value of WB with Debug.Print, but nothing was shown. Is this normal? Stepping through the code, after the line Set WB = Workbooks(WBName) I hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of object '_Workbook' failed. Am I missing something obvious? Ian "Ian" wrote in message ... Thanks, Chip. As usual, a very informative response. 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. A very useful article, though the warning about anti-virus raises concerns. It may be necessary for the end users to temporarily disable the AV on their laptops. I'll have to look into that. 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 I think I'll programmatically open the problem workbook and automatically remove the reference. I need to make it as easy as possible as some of the users are not particularly PC literate. When I distribute the fix, it will be with a batch file to copy the workbook to a specific location and open the workbook. The code will run on opening. 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). Is this a standard library on all PCs (or at least, all PCs runningMS Office)? I don't want to end up with any more problems with missing references. As an aside, can you think how the problem reference came to be set? I certainly haven't done it manually, particularly in view of the fact that, once I remove the reference on my own laptop, it disappears from the list of available references. Ian |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" problem
Hi Chip
I'm making progress, but still having issues. I no longer get the runtime error below. This was due to Excel XP not having "Trust access to Visual Basic Project" ticked in the security settings. I now have a different problem. After running your code without any (visible) errors, when I try to open the problem checklist with macros running: Excel XP - Excel takes 99% CPU time until I stop it. Excel 2000 - I get a MSVB message box stating "Error in loading DLL". Help shows this as error 48 which refers to a lirary specified in the Lib clause of a Declare statement. There is no Declare statement in my code. Stepping through your code again, in the section starting with Set VBProj, the line: Set Ref=VBProj.References("REFEDIT") doesn't appear to do anything. Ref=Nothing so the rest of this section is skipped In both versions the reference to MISSING: Ref Edit Control is still present. Any ideas? Ian "Ian" wrote in message ... Just a thought. The file I'm trying to fix is a template (xlt), not xls. Does this make any difference? Also, I've opened the problem file without macros running. Does this matter? Ian "Ian" wrote in message ... Hi Chip I've had a chance to try your posted code on another machine with the erroneous reference. I get a run-time error 1004 on the line Set VBProj = WB.VBProject The code is in a new workbook and the reference to Microsoft Visual Basic For Applications Extensibility 5.3 Library is set in this book. The project in the problem workbook is not locked. I tried to output the value of WB with Debug.Print, but nothing was shown. Is this normal? Stepping through the code, after the line Set WB = Workbooks(WBName) I hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of object '_Workbook' failed. Am I missing something obvious? Ian "Ian" wrote in message ... Thanks, Chip. As usual, a very informative response. 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. A very useful article, though the warning about anti-virus raises concerns. It may be necessary for the end users to temporarily disable the AV on their laptops. I'll have to look into that. 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 I think I'll programmatically open the problem workbook and automatically remove the reference. I need to make it as easy as possible as some of the users are not particularly PC literate. When I distribute the fix, it will be with a batch file to copy the workbook to a specific location and open the workbook. The code will run on opening. 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). Is this a standard library on all PCs (or at least, all PCs runningMS Office)? I don't want to end up with any more problems with missing references. As an aside, can you think how the problem reference came to be set? I certainly haven't done it manually, particularly in view of the fact that, once I remove the reference on my own laptop, it disappears from the list of available references. Ian |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
FAO Chip Pearson
Hi Chip
Any thoughts on this issue? I'm making progress, but still having issues. I no longer get the runtime error below. This was due to Excel XP not having "Trust access to Visual Basic Project" ticked in the security settings. I now have a different problem. After running your code without any (visible) errors, when I try to open the problem checklist with macros running: Excel XP - Excel takes 99% CPU time until I stop it. Excel 2000 - I get a MSVB message box stating "Error in loading DLL". Help shows this as error 48 which refers to a lirary specified in the Lib clause of a Declare statement. There is no Declare statement in my code. Stepping through your code again, in the section starting with Set VBProj, the line: Set Ref=VBProj.References("REFEDIT") doesn't appear to do anything. Ref=Nothing so the rest of this section is skipped In both versions the reference to MISSING: Ref Edit Control is still present. Any ideas? Ian "Ian" wrote in message ... Just a thought. The file I'm trying to fix is a template (xlt), not xls. Does this make any difference? Also, I've opened the problem file without macros running. Does this matter? Ian "Ian" wrote in message ... Hi Chip I've had a chance to try your posted code on another machine with the erroneous reference. I get a run-time error 1004 on the line Set VBProj = WB.VBProject The code is in a new workbook and the reference to Microsoft Visual Basic For Applications Extensibility 5.3 Library is set in this book. The project in the problem workbook is not locked. I tried to output the value of WB with Debug.Print, but nothing was shown. Is this normal? Stepping through the code, after the line Set WB = Workbooks(WBName) I hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of object '_Workbook' failed. Am I missing something obvious? Ian "Ian" wrote in message ... Thanks, Chip. As usual, a very informative response. 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. A very useful article, though the warning about anti-virus raises concerns. It may be necessary for the end users to temporarily disable the AV on their laptops. I'll have to look into that. 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 I think I'll programmatically open the problem workbook and automatically remove the reference. I need to make it as easy as possible as some of the users are not particularly PC literate. When I distribute the fix, it will be with a batch file to copy the workbook to a specific location and open the workbook. The code will run on opening. 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). Is this a standard library on all PCs (or at least, all PCs runningMS Office)? I don't want to end up with any more problems with missing references. As an aside, can you think how the problem reference came to be set? I certainly haven't done it manually, particularly in view of the fact that, once I remove the reference on my own laptop, it disappears from the list of available references. Ian |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
FAO Chip Pearson
I've been having lots of problems with RefEdit on Vista/Excel2007. I think
I'm going to quit using it. It has always been a troublesome control -- half assed design and development I would guess. I have no idea why it would spike the CPU, though. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Ian" wrote in message ... Hi Chip Any thoughts on this issue? I'm making progress, but still having issues. I no longer get the runtime error below. This was due to Excel XP not having "Trust access to Visual Basic Project" ticked in the security settings. I now have a different problem. After running your code without any (visible) errors, when I try to open the problem checklist with macros running: Excel XP - Excel takes 99% CPU time until I stop it. Excel 2000 - I get a MSVB message box stating "Error in loading DLL". Help shows this as error 48 which refers to a lirary specified in the Lib clause of a Declare statement. There is no Declare statement in my code. Stepping through your code again, in the section starting with Set VBProj, the line: Set Ref=VBProj.References("REFEDIT") doesn't appear to do anything. Ref=Nothing so the rest of this section is skipped In both versions the reference to MISSING: Ref Edit Control is still present. Any ideas? Ian "Ian" wrote in message ... Just a thought. The file I'm trying to fix is a template (xlt), not xls. Does this make any difference? Also, I've opened the problem file without macros running. Does this matter? Ian "Ian" wrote in message ... Hi Chip I've had a chance to try your posted code on another machine with the erroneous reference. I get a run-time error 1004 on the line Set VBProj = WB.VBProject The code is in a new workbook and the reference to Microsoft Visual Basic For Applications Extensibility 5.3 Library is set in this book. The project in the problem workbook is not locked. I tried to output the value of WB with Debug.Print, but nothing was shown. Is this normal? Stepping through the code, after the line Set WB = Workbooks(WBName) I hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of object '_Workbook' failed. Am I missing something obvious? Ian "Ian" wrote in message ... Thanks, Chip. As usual, a very informative response. 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. A very useful article, though the warning about anti-virus raises concerns. It may be necessary for the end users to temporarily disable the AV on their laptops. I'll have to look into that. 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 I think I'll programmatically open the problem workbook and automatically remove the reference. I need to make it as easy as possible as some of the users are not particularly PC literate. When I distribute the fix, it will be with a batch file to copy the workbook to a specific location and open the workbook. The code will run on opening. 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). Is this a standard library on all PCs (or at least, all PCs runningMS Office)? I don't want to end up with any more problems with missing references. As an aside, can you think how the problem reference came to be set? I certainly haven't done it manually, particularly in view of the fact that, once I remove the reference on my own laptop, it disappears from the list of available references. Ian |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
FAO Chip Pearson
Hi Chip
I've given up on this and reissued the workbooks with the reference removed. It would have been good to know how the reference had got in there and why I couldn't remove it programmatically. Still, I've learnt a bit along the way, so it wasn't all wasted effort :-) "Chip Pearson" wrote in message ... I've been having lots of problems with RefEdit on Vista/Excel2007. I think I wasn't using it. I don't know what it's for. My workbook just decided it needed the reference for some reason. I'm going to quit using it. It has always been a troublesome control -- half assed design and development I would guess. I have no idea why it would spike the CPU, though. Are you referring to a different issue here? I don't understand the CPU reference. Thanks again for your input. Ian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Problem: Worksheets("New Style 2006").Unprotect Password:="naPrint" | Excel Programming | |||
Problem with "On error resume next" with "custom VLookup" | Excel Programming |