Find text in Excel workbook

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

MD,
Did you setup the reference for the vba extensibilty lib?

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. :bawl

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.

Code:

---

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!

Quote:

---

Originally Posted by Tommy

... no GPF's ...

---

Hey Tommy, just trying to follow this great thread. What are GPF's?

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?

Code:

---

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