There is a bug in the code designed to write some code to a sheet module in this procedure. (Excel 2000). Suggestions/cures?
MD
There is a bug in the code designed to write some code to a sheet module in this procedure. (Excel 2000). Suggestions/cures?
MD
mdmackillop,
Found your problem. Your code was working fine. It's the code you were writing to the vba editor that was causing excel to crash. If you try to add code that the editor chokes on, it crashes excel. Take a look at the new attached file.
I knew I shold have tried to run the code that was added. LOL
CBrine - Good catch
Later
Tommy
aka
dragontooth
Hi Cbrine,
Still the same problem!
Can you tell me, if you let the MsgBox Pause run, Is FindWord visible as one of the sheet tabs?, With me it is not.
MD
Occurences of:door
LocationCell TextData1!A21Revised cost of internal doors and screensData1!A22Saving on standard doorsData1!A23Addition to glazed doors, cupboard doors and glazed screensData1!A26Window/External Doors: Investigate alternative suppliersData1!A8567 ? 68. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)Data1!A8758 ? 59. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!A21Revised cost of internal doors and screensMoreData!A22Saving on standard doorsMoreData!A23Addition to glazed doors, cupboard doors and glazed screensMoreData!A26Window/External Doors: Investigate alternative suppliersMoreData!A8567 ? 68. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!A8758 ? 59. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!E21Revised cost of internal doors and screens
MoreData!E22Saving on standard doorsMoreData!E23Addition to glazed doors, cupboard doors and glazed screensMoreData!E26Window/External Doors: Investigate alternative suppliersMoreData!E8567 ? 68. Allow the provisional sum of ?750 for supply of door bell andMoreData!E8758 ? 59. Allow the provisional sum of ?750 for supply of door bell and
Here's the response I get on running with the default value "door". It appears on Worksheet "FindWord"
Edit: That's uglier then I thought it would be.
Also, I did setup the pause msgbox to run. No Problems.
What happens when you run it?
It all works except adding the code to the sheet. Here's a simplified version.
Still no errors here MD, I'm baffled
I think I have it. I think the problem is that Excel doesn't actually "add" the CodeModule to the collection until the codepane is displayed for the "new" sheet. In other words when you are stepping through the code it is added, when you are using the macro it is not.
I revised the code to this and I got no more errors, no GPF's no excel crashing.
Which by the way I was getting a lot of before.
Sub AddSheetCode() Dim strCode As String Dim FWord As String Dim WB As Workbook Dim Sh Dim I As Integer Set WB = ActiveWorkbook strCode = "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" & vbCr _ & "If Sh.Name = " & Chr(34) & "FindWord" & Chr(34) & " Then" & vbCr _ & "If Target.Address = " & Chr(34) & "$B$1" & Chr(34) & " Then" & vbCr _ & "FindAll Target.Text, " & Chr(34) & "False" & Chr(34) & vbCr _ & "Cells(1,2).Select" & vbCr _ & "End if" & vbCr _ & "End if" & vbCr _ & "End Sub" Debug.Print strCode FWord = "ThisWorkbook" For I = 1 To WB.VBProject.VBComponents.Count If WB.VBProject.VBComponents.Item(I).Name = FWord Then Exit For End If Next If Not WB.VBProject.VBComponents.Item(I).CodeModule Is Nothing Then If Not WB.VBProject.VBComponents.Item(I).CodeModule.Find("Workbook_SheetChange", 1, 1, 100, 100) Then WB.VBProject.VBComponents.Item(I).CodeModule.AddFromString (strCode) End If End If Set WB = Nothing End Sub
Thanks Tommy and all others who rendered assistance. Final version has been posted as a KB entry. I've still to puzzle out exactly what's going on, but its working!
Hey Tommy, just trying to follow this great thread. What are GPF's?Originally Posted by Tommy
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Hi Zack,
General Protection Faults (the things that make your PC crash).
That's all I know about them!
MD
Hi Tommy,
I was running the code again, and failed to get the FindWord page to add. Investigation showed that the error on Sheets(FindWord").Select was -2147352565 , not 9 as per usual. This can be added to the routine, but I wondered if you had any ideas?
MD
Hi Zack,
To go along with MD, that is how I tell I have got to pay more attention to what I am doing . It could mean that I stepped on some system memory (OS).
MD
This is what MS says about the error.
-2147352565 (8002000B) Invalid index.
http://support.microsoft.com/default...b;en-us;186063
It is an automation error. Were you making this into an add-in? If so it may have gotten confused as to which workbook you were working on, just a guess. Did you change versions? I have a copy 2003 to test and maybe duplicate the error. If I can duplicate the error I (hopefully) can fix it, but I need to know how to duplicate.
Later
Hi Tommy,
I've amended my code to if Err <> 0 (as suggested by DRJ), so this avoids the issue. However, on using the procedure at work, I notice it doesn't handle sheet names with spaces. Can you return the KB item to WIP so I can add a couple of quotation marks
Thanks
MD
Hi MD,
I changed the KB to WIP.
I would suggest 2 things in the Err <> 0 part, this is just me and to my knowledge is not necessary. Err.Number <> 0 this is because I just like to know what I am checking for, once the Err.Number <> 0 then Err.Clear I prefer to clear it if it is there, the only reason is I have spent hours looking for an error that I knew I had trapped, come to find out it was someplace else entirely and was left over.
Later
As follows?
If Err.Number <> 0 Then 'error occured so clear it Err.Clear Sheets.Add.Name = "FindWord" Sheets("FindWord").Move After:=Sheets(Sheets.Count) 'Run macro to add code to ThisWorkbook AddSheetCode End If
Yes, but once again this just my opinion.
I agree with the logic, especially where an error is anticipated as in this case.
A "curiosity".
Entering * as the search term lists the contents of all the used cells in the workbook. Although why anyone would want to do this I don't know.
MD