I am having trouble with the following listbox code

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.

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

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

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

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.

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

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.

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

Ture,

Sorry the gotfocus event does seem to work.

repost answer and I'll guve u the points

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