Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
Les,
Try this after adding the sheet Application.VBE.CommandBars.FindControl(ID:=578).E xecute -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Les Stout" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
Hi Bob thanks for the reply, after adding you line of code i still have
the same problem. ? Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 *** Sent via Developersdex http://www.developersdex.com *** |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with C. Pearsons code to VBE
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 "Les Stout" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help using Chip Pearsons code to count cells with color | Excel Discussion (Misc queries) | |||
Pearsons | Excel Worksheet Functions | |||
Chip Pearsons "Rowlander" add-in won't install | Excel Discussion (Misc queries) | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
To Chip Pearsons | Excel Programming |