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

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
0
gamadmin
Asked:
gamadmin
  • 5
  • 5
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now