Find text in Excel workbook

[mdmackillop]

There is a bug in the code designed to write some code to a sheet module in this procedure. (Excel 2000). Suggestions/cures?
MD

[CBrine]

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.

[Tommy]

I knew I shold have tried to run the code that was added. LOL

CBrine - Good catch

Later
Tommy
aka
dragontooth

[mdmackillop]

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

[CBrine]

Occurences of:door
LocationCell TextData1!A21 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#Data1!A21)Revised cost of internal doors and screensData1!A22 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#Data1!A22)Saving on standard doorsData1!A23 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#Data1!A23)Addition to glazed doors, cupboard doors and glazed screensData1!A26 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#Data1!A26)Window/External Doors: Investigate alternative suppliersData1!A85 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#Data1!A85)67 ? 68. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)Data1!A87 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#Data1!A87)58 ? 59. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!A21 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!A21)Revised cost of internal doors and screensMoreData!A22 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!A22)Saving on standard doorsMoreData!A23 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!A23)Addition to glazed doors, cupboard doors and glazed screensMoreData!A26 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!A26)Window/External Doors: Investigate alternative suppliersMoreData!A85 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!A85)67 ? 68. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!A87 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!A87)58 ? 59. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!E21 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!E21)Revised cost of internal doors and screens



MoreData!E22 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!E22)Saving on standard doorsMoreData!E23 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!E23)Addition to glazed doors, cupboard doors and glazed screensMoreData!E26 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!E26)Window/External Doors: Investigate alternative suppliersMoreData!E85 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!E85)67 ? 68. Allow the provisional sum of ?750 for supply of door bell andMoreData!E87 (http://www.vbaexpress.com/forum/newreply.php?do=newreply&noquote=1&p=7940#MoreData!E87)58 ? 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?

[mdmackillop]

It all works except adding the code to the sheet. Here's a simplified version.

[mvidas]

Still no errors here MD, I'm baffled

[CBrine]

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

[Tommy]

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.


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

[mdmackillop]

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!

[Zack Barresse]

... no GPF's ...

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

[mdmackillop]

Hi Zack,
General Protection Faults (the things that make your PC crash).
That's all I know about them!
MD

[mdmackillop]

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

[Tommy]

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.aspx?scid=kb;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

[mdmackillop]

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

[Tommy]

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

[mdmackillop]

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

[Tommy]

Yes, but once again this just my opinion.
:)

[mdmackillop]

I agree with the logic, especially where an error is anticipated as in this case.

[mdmackillop]

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