Sleeper: Adding code from XLA to workbook (Excel 2000)

[mdmackillop]

I've created the following search routine, but it seems to have a glitch. The sub ExportCodeMod should write a routine into a newly created worksheet, but this is not happening. Any ideas what I'm doing wrong? If I run the code to the sub, Cname appears to be empty. Stepping through the code picks up the value. In both cases, strCode is not being written to the sheet module. The xla file is attached.



On Error Resume Next
Sheets("SearchWord").Select
If Err <> 0 Then
Sheets.Add.Name = "SearchWord"
CName = Sheets("SearchWord").CodeName
ExportCodeMod WB, CName
End If


Sub ExportCodeMod(WB As Workbook, CName)
Dim strCode As String
strCode = "Private Sub Worksheet_Change(ByVal Target As Range)" & 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 Sub"
Application.WB.VBE.VBComponents.Item(CName).CodeModule.AddFromString (strCode)
End Sub



Edit:
The following code does work to add code from within the same workbook.


Sub ExportCodeMod()
Dim strCode As String
strCode = "Private Sub Worksheet_Change(ByVal Target As Range)" & 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 Sub"
Application.VBE.ActiveVBProject.VBComponents.Item("Sheet3") _
.CodeModule.AddFromString (strCode)
End Sub

[Justinlabenne]

This was xld's recent postings (http://vbaexpress.com/forum/showthread.php?t=5281) on how to add from a textfile stored where your add-in can find it, and this method worked with better success for me in the past as doing an AddFromString seems to crash Excel for me (same when testing your add-in also).

If storing it in a text file isn't an option, why not just create the "SearchWord" sheet in the add-in (code included) and then copy it out to the Activeworkbook.


On Error Resume Next
Sheets("SearchWord").Select
If Err <> 0 Then
ThisWorkbook.Sheets("SearchWord").Copy Before:=ActiveWorkbook.Worksheets(1)
End If

[mdmackillop]

Hi Justin,
Never thought of adding the page to the XLA, but in this case it's the simplest solution. I'll look into XLD's method as well though for other situations. Revised XLA posted for information.
Regards
Malcolm

[Justinlabenne]

That seems to work md.

I know your still working on this and I thought I would point a few minor things. After I do one find, and the sheet is copied in, running it from the "SearchWord" sheet and not finding a result, Application.EnableEvents doesn't get reset to True after the "not found" message because it exits the sub:

One other, the Tools item doesn't get added, not sure why, at first glance I didn't see anything that would make me think of why not, but didn't spend alot of time testing, Apologies if you already knew about these things, looks good though..

[mdmackillop]

Thanks Justin,
I'll look into these things
Regards
Malcolm

[Bob Phillips]

One other, the Tools item doesn't get added, not sure why, at first glance I didn't see anything that would make me think of why not, but didn't spend alot of time testing, Apologies if you already knew about these things, looks good though..

You probably just opened the xla rather than installing it as I did.

md, you should code for that, lots of people do that, not installing it.

[Justinlabenne]

It didn't happen either way. I noticed that the add-in install event was used to create the toolbar, so I installed it, and opened it via Tools > Add-ins but no menu item. Tried to open by double clicking also, same results. DIdn't have time to test as to why for you, sorry bout that......classes this semester are getting a bit time consuming.http://vbaexpress.com/forum/images/smilies/119.gif

[mdmackillop]

Any idea how to convert an XLA file back to XLS to allow changes to be made to a worksheet in it? I can obviously just copy the code, but I'm sure there must be a "proper" method.

[Justinlabenne]

Maybe I am missing what your entirely asking here my friend, but what about going into the vbe, Select the ThisWorkbook code object, F4 to bring up the Properties dialog > and change the IsAddin property to false.

[Bob Phillips]

Maybe I am missing what your entirely asking here my friend, but what about going into the vbe, Select the ThisWorkbook code object, F4 to bring up the Properties dialog > and change the IsAddin property to false.

Why even bother with that, why not just make the changes to the worksheet directly. There is no need to take it back to .xls.

[mdmackillop]

Thanks again both.
I didn't know of the IsAddIn property, but having set it to false, I can of course change the sheet as suggested.
Still learning!:thumb

[Bob Phillips]

Thanks again both.
I didn't know of the IsAddIn property, but having set it to false, I can of course change the sheet as suggested.
Still learning!:thumb

Why do you need it, the sheet is exposed as an object set as True or False?