Help with C. Pearsons code to VBE

[Les Stout]

Hi all, i am using Chip Pearsons code below to insert some code in a
worksheet. When i start the macro with F8 and then run through with F5
it works fine, however when i run the full code excel gives me a dialog
box and says "Sorry excel encountered a problem and has to close" and
then re-starts ??

Any help would be appreciated.

Sub BBBStdR()
'
On Error Resume Next
CreateEventProcedure
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:01"), "MoveKTLToArchiveR"

End Sub

Sub CreateEventProcedure()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long, sh As String
Const DQUOTE = """" ' one " character
sh = ActiveWorkbook.ActiveSheet.CodeName
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(sh)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("BeforeDoubleClick", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, "Cancel = True"
.InsertLines LineNum, "Worksheets(""0908 RMT"").Activate"
.InsertLines LineNum, "Application.ActiveSheet.ShowAllData"
.InsertLines LineNum, "Cancel = True"
End With
End Sub


Best regards,

Les Stout

 

[Bob Phillips]

It worked okay for me Les (apart from not running the OnTime macro).

I assume you must have Trust Access to VBProjects and so on.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Les Stout]

Hi Bob, it is flagged and i have also flagged the reference "microsoft
visual basic for applications extensibility 5.6". I do not know if there
are other references that should be flagged ??

Best regards,

Les Stout

 

[Peter T]

Only a guess - are you trying to add the event code to a newly inserted
sheet. If so, if you run with the VBE closed, you'd get an error here -

sh = ActiveWorkbook.ActiveSheet.CodeName
Set VBComp = VBProj.VBComponents(sh)

- as you would not have been able to return the codename, (at least not
without more work).
Although that would cause an error I don't think that alone would trigger
quite what you describe.

Regards,
Peter T

 

[Les Stout]

Hi Peter T, you are right it is to a new sheet added in the code prior
to this. How would i get around this ?

Best regards,

Les Stout

 

[Les Stout]

Hi Peter T, i even tried saving it first but still get the message. It
puts the code into the sheet but then hangs for a while and then the
message pops up.



Best regards,

Les Stout

 

[Bob Phillips]

Les,

Try this after adding the sheet

Application.VBE.CommandBars.FindControl(ID:=578).Execute

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peter T]

Hi Les,

I'm not quite sure what's going on, in particular the hanging around bit
followed by the problem msg and quit. However if(?) you add a new sheet
with the VBE closed you won't be able to return it's codename until after
saving and reopening the file, - or - trying any one of a number of tricks.
If you've merely failed to return the codename I'd only expect the code to
fail with an error normal message, unlike what you've described (unless of
course you've got more code trying to run beyond what you've posted).

Here's one way that might be viable for you, particularly as your insert
code will end up with the VBE open in front of the user (nice to close it
when done)

include following towards the top of your procedure, actually it could
replace the line
' sh = ActiveWorkbook.ActiveSheet.CodeName

If GetCodeNameOpenVBE(ActiveSheet, sh) = False Then
Application.Goto "CreateEventProcedure"
MsgBox "Please run CreateEventProcedure"
Exit Sub
End If

and this function -

Function GetCodeNameOpenVBE(sht As Object, sName As String) As Boolean
Dim cbb As CommandBarButton

sName = sht.CodeName
If Len(sName) = 0 Then
Set cbb = Application.CommandBars.FindControl(ID:=1695)
If Not cbb Is Nothing Then
cbb.Execute
sName = sht.CodeName
End If
End If
GetCodeNameOpenVBE = Len(sName) > 0
End Function

All it does is open the VBE, after which normally the codename of the new
sheet can be returned.

Regards,
Peter T

 

[Les Stout]

Hi Bob thanks for the reply, after adding you line of code i still have
the same problem. ?

Best regards,

Les Stout

 

[Les Stout]

Hi Peter T, i have inserted and tried, with the same results. I do need
to continue with my code after inserting it, hence the code below.

Sub BBBStdR()
'
On Error Resume Next
CreateEventProcedure
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:02"), "MoveKTLToArchiveR"

End Sub

You also talk about closing the VBE, how would i do that ? Sorry i am
not a trained programmer and still stumbling a bit...

Best regards,

Les Stout

 

[Peter T]

I would have expected both methods to work (or are you running Excel 2007).
There may be something else in your code that you are doing that is causing
strange results.

In a new workbook include your code to insert new event code, only
CreateEventProcedure(). Adapt along the lines I suggested, in the new
function try with both (but not at the same time) -

Set cbb = Application.CommandBars.FindControl(ID:=1695) ' open the VBE
Set cbb = Application.VBE.CommandBars.FindControl(ID:=578) ' compile

CLOSE the VBE and run your CreateEventProcedure with Alt-F8 (don't forget -
the test is only valid with the VBE closed).

I'd be surprised if either method fails, assuming OK - add back more code
from the main project, testing bit by bit until you hit the problem.

Regards,
Peter T

 

[Les Stout]

Hi Peter T, tried it in book one and now get a "compile error:
User-defined type not defined" and the row
Dim VBProj As VBIDE.VBProject is highlited ?


Best regards,

Les Stout

 

[Bob Phillips]

That would be the extensibility library. Add it in references.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Les Stout]

Hi Bob/Peter T, it was firstly the library and secondly the cbb =
Application.VBE.CommandBars.FindControl(ID:=578) seems to be working
with the OnTime, with the VBE closed. Operated from a button on the
sheet.

Thanks you so much for the help, much appreciated.

Best regards,

Les Stout

 

[Les Stout]

Hi Gents, this is still not working ? Could it be because it is in
another workbook that i am trying to insert the code ?
If i use the same workbook sheets it works fine ?

Best regards,

Les Stout

 

[Peter T]

Your routine 'CreateEventProcedure' should add code behind the activesheet,
whichever workbook the activesheet happens to be in and irrespective as to
which wb 'CreateEventProcedure' is in. At least that's the way it appears it
should work on the basis of what you have posted.

Regards,
Peter T