• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

Strange ActiveX ListBox behavior

I have inserted an activex listbox on a spreadsheet.

I am loading values into the listbox, on click action of a button, from database.

There are two issues in am having with this list box

1) the listbox changes its size on its own. I checked the code, this happens at .additem

2) the other major issue i have is, on random occassion, after the listbox is loaded, I am not able to select any values from listbox. I verified the listbox is locked=false.
But this is completely random, if i try loading again, i can select the values from listbox !!

I am working with excel 2007 version.
0
cynx
Asked:
cynx
  • 10
  • 8
  • 2
1 Solution
 
Martin LissOlder than dirtCommented:
Is SendMessage used with LB_SETHORIZONTALEXTENT anyplace in the program?
0
 
dlmilleCommented:
ActiveX Controls sometimes have quirks, so I'm told :)

See:  http:/A_5315.html

Vote "Yes" if you find this article helpful!  :)

I believe you might try a setting quoted from the article:

"While the ListBox has an IntegralHeight property whose side-effect of a FALSE setting will keep that control from going askew, and while command buttons have properties such as move/size with cells, etc., other controls are not as graceful."

Set the IntegralHeight property to FALSE and see if that helps


PS - any reason to not use the ListBox Forms control, instead?

Cheers,

Dave
0
 
cynxAuthor Commented:
Dave, Thanks,  IntegralHeight property to FALSE fixed the issue with form auto size,

Any idea what is causing the point 2 in question?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cynxAuthor Commented:
ListBox Forms control, didnt use the same, just because, I was not able to read the same from VBA module, didnt waste time exploring why, as active X worked, and also couldnt find how to set it to multicolumn, as i am 3 columns, with 1 of 0pt, in my active x listbox
0
 
cynxAuthor Commented:
@ MartinLiss: No
0
 
dlmilleCommented:
Check the locked property right after you get this "failure"
1)   Try deleting the control, saving (make backup first), then re-create it.

2)    Let me know if the problem persists.

3) Please provide the code you use that manages the data in the listbox.

Dave
0
 
Martin LissOlder than dirtCommented:
Is this a standard Listbox control?
0
 
cynxAuthor Commented:
hi dave,
the locked property right after you get this "failure" - this was false, and enabled was true

tried replacing, still prob is there

code attached (which loads the data into Listbox)

i have masked new names in code, strDBSP_LOAD_xxx_LIST is the stored procedure that brings in the data from DB.



Public Function isLoad_xxx_List(cmdConn As ADODB.Command, lngID_Quote As Long) As Boolean

Const strERRORLOC As String = "'ADO Load', 'Load Quote: xxx List', Reference: "

Dim rsxxxList As ADODB.Recordset  'xxx recordset for saved data
Dim lngCounter As Long
Dim vListArray() As Variant

    On Error GoTo ERROR_ROUTINE:
    Application.StatusBar = "Loading xxxx List..."
    isLoad_xxx_List = True
    
    With cmdConn
        'set command to DB SP
        .CommandText = strDBSP_LOAD_xxx_LIST
        .CommandType = adCmdStoredProc
        Set rsxxxList = .Execute(Parameters:=lngID_Quote)
        
        With rsxxxList
            If .BOF Or .EOF Then
                'if error, close rs and display error
                .Close
                Exit Function
            End If
            Erase vListArray()
            
            'Load Array
            vListArray() = .GetRows
            .Close
        End With
        
       'Load List 
       wsPoli.lst_xxx.Clear
       For lngCounter = LBound(vListArray, 2) To UBound(vListArray, 2)
        With wsPoli.lst_xxx
            .AddItem vListArray(0, lngCounter)
            .List(.ListCount - 1, 1) = vListArray(1, lngCounter)
            .List(.ListCount - 1, 2) = vListArray(2, lngCounter)
        End With
       Next lngCounter
    End With
Exit Function
ERROR_ROUTINE:
'set function to failed
isLoad_xxx_List = False
MsgBox strMSG_ERROR & strERRORLOC & lngID_Quote & vbNewLine & Err.Description, vbExclamation + vbOKOnly, strMSG_TITLE
lngID_Quote = lngERROR
End Function

Open in new window

0
 
dlmilleCommented:
Ok - I'm looking at this, now.

In the meantime, have you tried to delete the control, then add it back (renaming it) to see if you have the same failure?

I don't see anything wrong with how you're loading your listbox.

I created a demo workbook and your logic works for me...

I assume your worksheet is protected, re: the locked property is "supposedly" preventing you from touching the listbox (in spite of its setting?)  However, my understanding is the locked property doesn't disallow selection - it disallows users from editing the listbox in design mode, so I don't think the locked property is the culprit...

You might try something (see example in the worksheet's codepage for the Worksheet_Activate event) - try to identify any property that might prevent selection (not sure there is one) - but here you can "reset" that to the proper setting... It might help you find the culprit.  When the problem happens, go to another tab and then back and see if the reset from Worksheet_Activate helped... At least that's what I attempted to do with the article and the patch there...

I'm just noodling to see if any of this might help...

See attached - if you can get this to "fail" then upload it and let me see.  Is there a way to get a version of yours that fails that can be uploaded, re: understand sensitive data issues so don't want that.

Dave
loadListBox-r1.xlsm
0
 
dlmilleCommented:
Where is your activex control?  on the spreadsheet or on a form?

Someone had this problem on a form:  http://www.access-programmers.co.uk/forums/showthread.php?t=189145

A couple other things to try.  Is it single select or multi-select?  If multi, try single for a while and see.  Also, check all properties to see if they are ok - e.g., # columns should be 3, etc.  Do a listing of the vArray() in a spreadsheet to see what data is in all 3 columns - any funny bizness?  Have a macro on hand to unload the data from the listbox to a sheet just to see, etc...

Dave
0
 
cynxAuthor Commented:
Hi Dave,
I tried deleting & recreating listbox with different name, the issue persists.

my sheet is protected, however i dont see issue with that, i also tried unprotecting it.

My listbox is placed directly on Sheet.

In the example you shared, i noticed, you are referencing listbox as "wsname.oleobjects("Listbox")

where as I am accessing listbox as wsname.Listbox , do you think is this causing an issue ?
I will try changing this and confirm.

Also when i click on your listbox, it shows =EMBED("Forms.ListBox.1","") in the formula bar. what does this stand for ?

0
 
dlmilleCommented:
>>In the example you shared, i noticed, you are referencing listbox as "wsname.oleobjects("Listbox")


That's because I'm addressing the listbox from a public module.  You're probably doing it from a worksheet's codepage?



>> Also when i click on your listbox, it shows =EMBED("Forms.ListBox.1","") in the formula bar. what does this stand for ?

It means the listbox is embedded in the worksheet.

What do you get when you click yours?  Are you doing this on a worksheet or userform (big difference)?

0
 
cynxAuthor Commented:
>>That's because I'm addressing the listbox from a public module.  You're probably doing it from a worksheet's codepage?

I am using wsname.listbox even from module.

>> Its embedded in the worksheet, just noticed I too get the same EMBED in formula bar.

I will probably try refererring my listbox as you have done, just to see if any luck
0
 
cynxAuthor Commented:
if i dont get this working, do see any workaround for this ? you mentioned for placing it on form, how does that work ? I mean how can i place a form on worksheet ?
0
 
dlmilleCommented:
Can you upload at least a mockup of what you're working with?  So I can understand your perspective?  Some print screen images?

I think I can help more if I can understand context.
0
 
cynxAuthor Commented:
this listbox is for an application which is built using vba, excel front end and sql DB, interacting with few other external apps.
there is a button whcih brings up a form, that form has a list of all entries in DB, user selects an entry, there are several fiters on form which helps user to search/filter down their entry.
There is no issue with listbox on this form.

once the user selects the entry on the form, it pulls relevant data to this entry from several DB tables and loads the information to approx 10-12 sheets into excel.

The listbox i have inserted is on one of above sheet, which displays list of all entries linked to entry loaded into the excel via the load form. The point of having this is, so that user can directly double click on related entries and load , rather than going to the load form and again filtering/searching.

Hope this makes sense, Sorry cant post screenshots due to Confidentiality. I will try to create a mockup and upload tonight.


0
 
dlmilleCommented:
Ok.  That sounds fine.

Seeking more information so I can help further:

Are there any events you are using that are associated with the listbox?  Any macros that get fired as a result of selecting the listbox?  Any code in the sheet's codepage that we should both be aware of that's associated with the listbox?  A click or double-click event?

Dave
0
 
cynxAuthor Commented:
sorry i was away for a while, yes there is a double click event from the listbox.
recently i discovered that , when listbox is locked, after couple of clicks on other worksheets and back to worksheet which has listbox, activates it. thou this is random
0
 
dlmilleCommented:
Ok - a simple mockup, or sharing your structure/code might be useful to help further.

Cheers,

Dave
0
 
cynxAuthor Commented:
Guess this issue is solved now, I did make a change
1) unlock the cells over which the listbox was placed, so that they are not locked even when sheet is protected

Thanks for your help on this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now