 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / December 2007Help 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 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.  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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 [quoted text clipped - 40 lines] > > *** Sent via Developersdex http://www.developersdex.com *** 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 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 > 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 [quoted text clipped - 40 lines] > > *** Sent via Developersdex http://www.developersdex.com *** 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 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 Les, Try this after adding the sheet Application.VBE.CommandBars.FindControl(ID:=578).Execute SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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 [quoted text clipped - 5 lines] > > *** Sent via Developersdex http://www.developersdex.com *** Hi Bob thanks for the reply, after adding you line of code i still have the same problem. ? Best regards, Les Stout 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 > 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 [quoted text clipped - 5 lines] > > *** Sent via Developersdex http://www.developersdex.com *** 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 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 > 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. [quoted text clipped - 16 lines] > > *** Sent via Developersdex http://www.developersdex.com *** 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 That would be the extensibility library. Add it in references. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Peter T, tried it in book one and now get a "compile error: > User-defined type not defined" and the row [quoted text clipped - 5 lines] > > *** Sent via Developersdex http://www.developersdex.com *** 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 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 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 > Hi Gents, this is still not working ? Could it be because it is in > another workbook that i am trying to insert the code ? [quoted text clipped - 5 lines] > > *** Sent via Developersdex http://www.developersdex.com *** |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.