Solved

Strange ActiveX ListBox behavior

Posted on 2011-09-21
20
630 Views
Last Modified: 2012-05-12
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
Comment
Question by:cynx
  • 10
  • 8
  • 2
20 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 36575682
Is SendMessage used with LB_SETHORIZONTALEXTENT anyplace in the program?
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36578833
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
 
LVL 1

Author Comment

by:cynx
ID: 36579423
Dave, Thanks,  IntegralHeight property to FALSE fixed the issue with form auto size,

Any idea what is causing the point 2 in question?
0
 
LVL 1

Author Comment

by:cynx
ID: 36579432
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
 
LVL 1

Author Comment

by:cynx
ID: 36580889
@ MartinLiss: No
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36582080
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 36582943
Is this a standard Listbox control?
0
 
LVL 1

Author Comment

by:cynx
ID: 36588048
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36590733
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36590741
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:cynx
ID: 36600952
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36601361
>>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
 
LVL 1

Author Comment

by:cynx
ID: 36708013
>>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
 
LVL 1

Author Comment

by:cynx
ID: 36708018
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36710513
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
 
LVL 1

Author Comment

by:cynx
ID: 36710976
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36711275
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
 
LVL 1

Author Comment

by:cynx
ID: 36984967
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36986805
Ok - a simple mockup, or sharing your structure/code might be useful to help further.

Cheers,

Dave
0
 
LVL 1

Author Comment

by:cynx
ID: 37068803
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now