+ Reply to Thread
Results 1 to 9 of 9

Unable to Display a Userform from an ActiveX Control

  1. #1
    Registered User
    Join Date
    06-06-2006
    MS-Off Ver
    2010, 365
    Posts
    39

    Question Unable to Display a Userform from an ActiveX Control

    Hi,

    I have a single select listbox (activex control) embedded in a worksheet. Now, what I have to do is that when the user clicks any row of data in the listbox, it should open a userform and display its data on the userform. Unfortunately, I am unable to do that. I tried using the code below, but it generates error '438' on clicking on the listbox:

    Please Login or Register  to view this content.
    Where am I going wrong? Please help.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    Try the attachment which seems to do what you want.
    Attached Files Attached Files
    Martin

  3. #3
    Registered User
    Join Date
    06-06-2006
    MS-Off Ver
    2010, 365
    Posts
    39
    hi mrice,

    thanks a ton for the response. i saw what you have done. but tell me, how do i insert columns (with headings) in this type of a listbox? in the properties, there is no option to add columns. the same also seems as not possible with coding. please help.

    peace,

    shivboy

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    I think you'll have to move to a listbox on a user form for this sort of funtionality.

    You'll need to use the add item method to populate the box.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You would need to use a ListBox from the Controls Toolbar.

    Set the ColumnHeads Property to True
    If you have the Heading in A1 then set the ListFillRange to $A$2:$A$6, or whatever the last row of the list is.
    Use the ListBox_Click event to display the UserForm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    06-06-2006
    MS-Off Ver
    2010, 365
    Posts
    39
    hi mRice, royUK,

    It's still not working.

    First, to include columns and column headings, I did the following:

    1. Inserted a listbox control (Form Control) from the tool box into the worksheet.

    2. Changed the name in the name box to 'myList'

    3. Selected 'Properties' option on the toolbar, and inserted the range into the 'Input Range' option in the Control tab.

    4. After that, in the VBE, I entered the following code:

    Please Login or Register  to view this content.
    The above generated the following error on activating the worksheet:

    Run-time error '438':
    Object doesn't support this property or method
    Then, I tried the following:

    1. Inserted a module, and inserted the following sub -

    Please Login or Register  to view this content.
    2. Right-clicked on the listbox in the worksheet and selected 'Assign Macro' and selected 'myList_Click'.

    On clicking, the following error was generated:

    Run-time error '1004':
    Method 'OLEObjects' of object '_Worksheet' failed

    What is so wrong that I am doing?

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Have you tried a listbox from the Controls Toolbar as I suggested?

  8. #8
    Registered User
    Join Date
    06-06-2006
    MS-Off Ver
    2010, 365
    Posts
    39
    hi roy,

    i tried what you suggested in excel 2003, it worked. but it is not working in excel 2007. what to do?

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Yes you can.

    Open the Developer Tab and click the Design View button. Then from the Insert Drop down select Listbox from the Activex controls. Then do exactly what i suggested previously.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1