I am having trouble with the following listbox code

I have a listbox on a worksheet.  It deletes all entries in the listbox then re populates it with certain worksheet names.  This works ok but when I select a certain value it does not display the selection.  Also if I just click the dropdown button it toggles between entries in listbox.  Can someone tell me what I am missing?

Here is the code:

Private Sub Profit_Centre_DropButtonClick()
    While Profit_Centre.ListCount > 0
    'Ensure Combobox contains list items
        If Profit_Centre.ListIndex = -1 Then
            Profit_Centre.ListIndex = Profit_Centre.ListCount - 1
        End If
        Profit_Centre.RemoveItem (Profit_Centre.ListIndex)
    Wend
    For Each nm In ActiveWorkbook.Names
        If Left(nm.Name, 15) = "No_of_open_pos_" Then    ' We can count number of name entries for this value -1 for the backtester name
            If Len(nm.Name) > 16 Then                    ' If name is not the backtester variable
                Profit_Centre.AddItem Right(nm.Name, Len(nm.Name) - 15)
            End If
        End If
    Next

End Sub
gamadminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gamadminAuthor Commented:
also when I put in msg statements to monitor what is happenning,  the follwowing code gets executed not once but multiple times after I click the dropdownbutton

Private Sub Profit_Centre_Change()    MsgBox ("You have selected " & Profit_Centre.Value)
End Sub

There is no clear example in the help files for this.

I am increasing the points offered.
0
tureCommented:
gamadmin,

Just an observation. I assume that this is just a mis-copy, but...

If your dropdown is named 'Profit_Centre', the event procedure should be:
'Private Sub Profit_Centre_DropButton_Click()'

Not:
'Private Sub Profit_Centre_DropButtonClick()'

Ture Magnusson
Karlstad, Sweden
0
tureCommented:
gamadmin,

Sorry. I was wrong. But this is right (from the help files):

The DropButtonClick event occurs whenever the drop-down list APPEARS or DISAPPEARS.

Try using the GotFocus event instead. It think that suits you better.

/Ture
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

antratCommented:
Hi gamadmin

The reason for your msgbox appearing numerous times is that you have it in the Change Event for your list box and looking at your code for the Dropbutton_Click the listbox WOULD change numerous times and each time it does the msgbox will appear.

You would probaly be better off placing your code in the ListBox_Enter event or the Exit_Event


Try using the Msgbox function to debug your code for the Dropbutton_Click by placing it after each line of code in the Dropbutton_Click procedure then you will see exactly what is happenning step by step.

Something like: MsgBox Profit_Centre.text

hope it helps

antrat
0
gamadminAuthor Commented:
antrat

regardless of whether my msgbox does appear more than one.  In the dropdownbutton click event it does not select any choice when highlighted.  A change event does happen after the dropdownbutton is selected and a value is selected but it does not change the value.  This is what I am trying to find out.


0
tureCommented:
gamadmin,

The change event happens, but as soon as you have selected a value in the list and the dropdown list disappears, the DropButtonClick event is executed again, destroying whatever selection you have made.

Again: Use the GotFofus event instead of DropButtonClick.

/Ture
0
gamadminAuthor Commented:
ture,

I am not sure why I should be using this event but when I replace the dropbuttonclick with the gotfocus event, it does not even execute.

0
tureCommented:
gamadmin,

In Excel 2000, the GotFocus event is triggered when you click the DropDown. For some strange reason it isn't so in Excel 97.

Because the DropButtonClick event is triggered BOTH when the dropdown list appears and when it disappears, you are having trouble. As I understand it, you want to run your procedure only when the dropdown appears.

To know if the dropdown is appearing or disappearing, let's use a static boolean variable that we toggle the value of every time the DropButtonClick procedure is run. Then we use the value of this variable to determine if we should re-fill the list or not.

Private Sub Profit_Centre_DropButtonClick()
  Dim nm As Name

  'Declare a static variable, which keeps it's
  'value between procedure calls
  Static Appearing As Boolean

  'Toggle the value of the variable
  'from False to True or from True to False
  Appearing = Not (Appearing)

  'Do this only when the dropdown list appears  
  If Appearing Then

    'Clear the dropdown's list
    Profit_Centre.Clear

    'Add items to the dropdown's list
    For Each nm In ActiveWorkbook.Names
      If Left(nm.Name, 15) = "No_of_open_pos_" Then
        If Len(nm.Name) > 16 Then
          Profit_Centre.AddItem Right(nm.Name, Len(nm.Name) - 15)
        End If
      End If
    Next nm
  End If
End Sub

/Ture
0
gamadminAuthor Commented:
Ture,

Sorry the gotfocus event does seem to work.

repost answer and I'll guve u the points
0
tureCommented:
Posting for points.

Have a look at my latest post too, it's not a bad idea...

/Ture
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gamadminAuthor Commented:
ture,

I have used a similar method.  Dropdownclick is weird though, it should allow you to select as well.  Getfocus works 95%. Sometimes it gives you a scroll bar in list and sometimes not. It is probably excel

thx for new event

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.