Inserting an event procedure into sheet code

I have the following code that adds a combo box to the ActiveCell in
the ActiveSheet and creates the event procedure for it in the
ActiveSheet as well.  It all works fine, except that after it
completes, you are dropped down into the VBE for no apparent reason. No
errors have occcurred.  Anyone seen this before?


The code is below:

Sub buildcombo()
    Dim combo As OLEObject
    Dim LineNum As Long
    Dim CodeMod As Object

    ' Create the combo box on the ActiveCell on the ActiveSheet
    Set combo =
ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Link:=False,
DisplayAsIcon:=False, _
        Left:=ActiveCell.Left, Top:=ActiveCell.Top,
Width:=ActiveCell.Width + 5, _
        Height:=ActiveCell.Height + 5)

    ' now name the combobox and insert the handler for it into the
sheet code module
    On Error GoTo err_trap
    combo.Name = "ComboBox1"
    Set CodeMod =
ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
    With CodeMod
        LineNum = .CreateEventProc("Change", combo.Name) + 1
        .InsertLines LineNum, vbTab & "If (ComboBox1.ListIndex <> -1)
Then"
        .InsertLines LineNum + 1, vbTab & vbTab & "Cells(10, 4).Select"
        .InsertLines LineNum + 2, vbTab & vbTab & "ActiveCell.Value =
ComboBox1.List(ComboBox1.ListIndex)"
        .InsertLines LineNum + 3, vbTab & "End If"
    End With
    Exit Sub
err_trap:
    MsgBox "Got error = " & Err.Description
End Sub


Please forgive the word wrapping.  Thanks in advance for any help.

Roy

0
roybrew (35)
5/20/2005 8:03:16 PM
excel 39879 articles. 2 followers. Follow

0 Replies
609 Views

Similar Articles

[PageSpeed] 12

Reply:

Similar Artilces:

how can i search for sheet with any part of sheet name
hi every on i need code for userform of textbox and listbox to search for any sheet in workbook with any part of that sheet name on i enter on textbox to populate on listbox all sheets named contain that enterd text on textbox . any help appreciated . thanks This code takes a string and searches the sheet name for it. Set strData to your textbox value and instead of msgbox, do a listbox.add Dim strData As String strData = "Dat" For i = 1 To ThisWorkbook.Sheets.Count For j = 1 To Len(ThisWorkbook.Sheets(i).Name) - Len(strData) + 1 If UCase(Mid(ThisWorkbook.Sheets(i).N...

Using onchange event to Hide a field
I would like to dynamically show / hide certain fields on my CRM form based upon the user's selection from a Picklist. Is there any way to dynamically show / hide a field on a form using the onchange event of the Picklist? There is no supported way to do this. You could try setting the visibility of the associated DIV to false, but no guarantees. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Tue, 16 Aug 2005 23:50:12 -0700, "cdobkins" <cdobkins@discussions.microsoft.com> wrote: I would like to dyn...

Lookup selective from another sheet
Assume I have one sheet as below. How can I create a new sheet and display only those entrys that are greater than a entered value. i.e main sheet abc 2 def 5 ghi 6 jkl 5 fgh 3 krk 4 on second sheet, if 4 is entered only entrys >4 are shown. i.e def 5 ghi 6 jkl 5 krk 4 Try something like this: C1=IF(VLOOKUP(A1,Sheet2!A$1:B$4,2,FALSE)>Sheet1!B1,VLOOKUP(Sheet2!A$1:B$4,2,FALSE),NA()) Your main sheet is A1-b6 and Sheet 2 is A1:B4 "Jim" <Jim Forrest@hotmail.com> wrote in message news:WBb4f.34203$U9.12412@fe3.news.blueyonder.co.uk... > Assume...

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

COM events
I am writing a COM client prog in MFC / Visual studio 2003. The COM server has an event (callback) interface and a "normal" interface. I have created the class for the "normal" interface thru the class wizard Add Class from the TLB file of the server object. I use this class thru ::CreateDispatch. For the callback interface, I have found that the wizard Add Claas does not create something I could use, so I have created by hand a class, derived from CCmdTarget with automation capabilities. Now the question : How do I use the class wizard to create in that class (with the...

Automatioc spill over data to a new Excel sheet from Xml source?
I have an XML source file with data for more than 256 columns. Since one sheet can contain a max of 256 cols, Is there a way to make Excel, create an additional sheet to keep the excess data? Preferrably using Xml map / template? ...

New Calendar won't print time or complete event
I'm a newbie on Outlook 2007 added on to my Office 2007 suite. I've bought it for the montly Calendar view which is important to us. I've begun entering events or appointments with time and date. When I go to print preview or print, the time of the appointment does not appear and the event text is truncated severely. 1.) How can I get the appointment time to print in the monthly calendar? 2l) How can I activate word wrap or get the complete text to fit into the date box in montly calendar view? Thanks for all help offerred. Outlook 2007 doesn't use word wrap in the cale...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

CDialog Event Notification Problem
Hello I'm having problems receiving WM_KEYDOWN events in a COleControlModul derived app. The app is an .ocx and is instantiated from a 3rd party program The only window is a modeless CDialog derived class. I need event notificatio of user keystrokes on dynamically created CEdit controls OnCommand is firing from dynamically created CButtons. I am also receiving messages for a dynamically created MSHFlexGrid though an EVENTSINK_MAP I am not receiving OnChar, OnKeyDown, etc. when the focus is set on one o the CEdit controls Any help would be greatly appreciated. Thanks David ...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Event Viewr: ID 52
I'm gettint the following error in event viewer: Event Type: Warning Event Source: Disk Event Category: None Event ID: 52 Date: 5/31/2010 Time: 4:45:41 PM User: N/A Computer: COMPUTER1 Description: The driver has detected that device \Device\Harddisk0\DR0 has predicted that it will fail. Immediately back up your data and replace your hard disk drive. A failure may be imminent. Other info: OS Name Microsoft Windows XP Professional Version 5.1.2600 Service Pack 3 Build 2600 OS Manufacturer Microsoft Corporation System Manufacturer MSI System Model MS-7309 System...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

Event ID 9548 #2
Hi, I keep getting Event ID 9548 in my application log on my Exchange 2000 server. The warning is: Disabled user /o=ProBusiness, Inc./ou=ProBusiness/cn=Recipients/cn=username does not have a master account SID. Please use Active Directory MMC to set an active account as this user's master account. I get these from time to time and I am able to follow http://support.microsoft.com/default.aspx?scid=kb;en-us;291151&Product=exch2k to grant the necessary rights to the SELF account, but in this case the user that is mentioned in the warning does not exist in Active Directory (or at ...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Print Chart in New Sheet on Two Pages
Is there a way to print a chart on more than one page if it is a "new sheet" as opposed to an object in a worksheet? I noticed that Page Break Preview is disabled in this case. Thanks ...

Writing Code
I am a novice at writing code so I need help. I am trying to create an input box to pop up in an excel form and have the information that was put in the box to drop into a particular cell? Help! sk, You could use something like this: Range("A1").Value = InputBox("Enter your name.") If you want to validate the entry, you can use: Range("A1").Value = Application.InputBox("Enter your name.", , , , , , , 2) which makes Excel validate that the user enters a string. HTH, Bernie MS Excel MVP "sk" <sk@discussions.microsoft.com> wrote in...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

MultiWorkbook Change Event Detection / Spell Checker
Excel 2003 or 2007. Looking to be able to force spell checking whenever any cell content is is changed. I've been successful on a single workbooks by adding Spell Check execution on Worksheet_Change. I'm hoping to take this one step further - make it an 'Add-In' so the check will run for any workbook. It looks like the Worksheet_Change event only triggers for the host workbook so an 'Add-in' will not do the job. Any suggestions to force spell checking on a change for any workbook/worksheet I modifiy? TIA, - Pat The way I would go about this i...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

On Exit Event
On Exit Event Hello, I have a form that opens to a new record. I set the focus to a control that I want the user to populate without going any further. This code below will not work, however, because the record has yet to be created: Private Sub comSubject_BeforeUpdate(Cancel As Integer) If IsNull(Me.comSubject) Then MsgBox "You cannot leave the [Subject] field empty!" Cancel =3D True Me.Undo End If I suppose I could add a default value or make the record dirty, but that seems unnecessary. This code works too well; I can=92t move anywhere unti...

Insert dataset to another database
I'm posting this to this group also since it pertains to queries, primarily. Hello, Using VB6/ADO, I'm thinking I can create a recordset and insert it into another table in a different Jet database, all within the execution of a single query. But, I can't seem to get it to work, even in an experiment in Access 2K. My sql in Access 2K: SELECT D.lorder as Ord, A.Lorder FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER JOIN [;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder = a.lorder WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC')...