Kamis, 26 Februari 2015

Re: [ExcelVBA] How do you get code assigned to a ListBox to run even when the same item is selected?



The compile error message is telling you that the event procedure requires the right parameters. :-

For the Mousedown these are :- (ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) . 
They are return parameters and may be used in your code or simply ignored.

What you do is :-

Turn on Developer mode then click the Design Mode button 
Click on the control to see the handles at its corners
Double click on the control, this puts you into the code window with the cursor inside the control's default event.

THEN at or near the top you will see a combo with the control's name in it on the left and a combo on the right with the event name in it.
CLICK the right hand combo dropdown, then choose the event you want to attach code to. This will put a properly formed empty Sub into your module.
Then turn off Developer mode.


Derek Turner

From: "h_dunbar@hotmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, 25 February 2015, 22:22
Subject: Re: [ExcelVBA] How do you get code assigned to a ListBox to run even when the same item is selected?

I agree, Derek, it wouldn't be intuitive enough to rely on.  Most people would click the arrow.

But I wanted to see how the MouseDown worked, and I couldn't.   I'm probably missing something very basic.

I created an ActiveX ComboBox on the mostly empty Sheet1.  The "ListFillRange" is B1:B10, and consists of 10 items.   The linked cell is A16.

This code (in Sheet1):

Sub ComboBox1_Click()
    Range("A1").Value = ""
End Sub
performs like everything else I've tried.  The statement is executed only if I pick something new from the dropdown list.

When I replace 'Click()' with 'MouseDown()', I get this pop-up error msg almost immediately:  "Compile Error:  Procedure declaration does not match description of event or procedure having the same name."  

What (and where) do I need to add so that I can use the MouseDown event with the ActiveX combobox?


(I'm working now in Excel 2003 until I get back my new laptop.)

---In ExcelVBA@yahoogroups.com, <g4swy@...> wrote :

The Active X Combobox_mousedown event fires if you click on its Text area.

But it's not very intuitive, In classic Windows style you would have a Go button at the right of the Combibox.

Derek Turner +++

From: "h_dunbar@... [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, 25 February 2015, 2:50
Subject: Re: [ExcelVBA] How do you get code assigned to a ListBox to run even when the same item is selected?

Derek, DaveS and DavidG,

I'm really sorry.   I've been saying "ListBox", but what I actually have on the sheet is a ComboBox.   It's a Forms ComboBox, not an ActiveX ComboBox.

I've now tested both kinds of ComboBox (Forms and ActiveX) on a much simpler sheet, and I can't get either one to execute a simple VBA statement when the same item from the ComboBox drop-down is selected.

I believe everything I wrote earlier is accurate, if "ListBox" is replaced by "ComboBox".


---In ExcelVBA@yahoogroups.com, <g4swy@...> wrote :


Are you talking about an  Active X  Listbox ?

This seems to behave in the way you want for me. ?

Derek Turner


From: "h_dunbar@... [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Tuesday, 24 February 2015, 10:41
Subject: Re: [ExcelVBA] How do you get code assigned to a ListBox to run even when the same item is selected?

(Well, I wrote it up on the plane as planned, and then my 1-month-old Lenovo Yoga 3 went black on Friday. It's now in KY being repaired and I'm back home working on an 11-year-old Dell!  Anyway, here's the description of why I have the ListBox.)

There are 56 items in the drop-down ListBox, 55 of which are individual games that can be analyzed via clicking a separate "Analyze Game" button after an item is chosen from the ListBox.  The 56th ListBox item is "Custom Games".  When "Custom Games" has been selected from the ListBox, the "Analyze Game" button first directs the user to a different sheet where the user can create a new game to be analyzed, then returns focus to the original sheet and proceeds as before through the game analysis code.
If the game is a Custom Game, then when the "Analyze Game" button is clicked, the name of the created Custom Game is placed in cell E1.  (E1 is referred to as Range("CustomName1").Offset(0, 1) in the Sub I originally posted.)  There are 2 reasons for putting the name in E1:
1. Displaying the name reminds the user which game has been chosen. (for non-Custom games, the name of the game shows in the ListBox.)
2. In case the user wants to do another calculation with the same game, the code checks to see if there is already a Custom Game name in cell E1.
The problem I am having is when a user may want to choose a different Custom Game immediately after having done an analysis of a Custom Game.   The natural process would be for them to again click the ListBox and again choose "Custom Games".   But I can't get the Sub I've attached to the ListBox to clear the old name from cell E1.   And therefore, the code thinks I want to use the same game as before.
I could easily create a separate "Change Custom Game" button that would take care of this.  But I have 5 of these ListBoxes on the sheet (so that the user can analyze a sequence of up to 5 games), and I would need a separate "Change Custom Game" button for each game.  The sheet is already too busy with buttons, charts, and input and output areas whose layout I'd rather not mess with. 
I do already have an "All New Games" button which resets the 5 ListBoxes to a "No Game Chosen" setting.  That'll be my fallback for the user if I can't accomplish what I'm trying to do with a Sub attached to the ListBox.


---In ExcelVBA@yahoogroups.com, <h_dunbar@...> wrote :

Thanks for your interest, Dave!

I'm getting ready to catch a flight today, but I'll write up a description during the flight and post it tonight or tomorrow morning.

Thanks again,

---In ExcelVBA@yahoogroups.com, <smartware.consulting@...> wrote :

Putting controls directly onto Worksheets cuts down on the events that they will generate,  They generate more events when on a form.  Looks as though listboxes are limited to only trigger on changes and not clicks (which is consistent with "change" events for cells, which also don't fire if you don't change the contents).
That said, I don't see from your code what your purpose is in using a listbox, rather than a button.  Could you give us a short description of what you're actually doing with these controls, and we'll think about other ways of achieving the result.

Regards, Dave S
----- Original Message -----
Sent: Thursday, February 19, 2015 2:39 PM
Subject: Re: [ExcelVBA] How do you get code assigned to a ListBox to run even when the same item is selected?

Thanks for the suggestion, David.

However, when I replace

Sub Game2_Click()


Sub Game2_MouseUp()

it acts the same as with Click.  That is, it only executes the statements in the Sub if I click on a different item from the drop down list.   If I click on the same item, it doesn't execute the statements in the Sub.

For good measure, I tried MouseDown(), too.   Still the same behavior.


---In ExcelVBA@yahoogroups.com, <yahoo@...> wrote :

Hi H Dunbar

That's the way it is.  However if you use MouseUp rather than click it will fire anywhere.

David Grugeon

On 15 February 2015 at 03:04, h_dunbar@... [ExcelVBA] <ExcelVBA@yahoogroups.com> wrote:


I have a ListBox on Worksheet "PlayList".   When the macro below is assigned to the ListBox, it does what I want, as long as I choose an item on the list different from the previous selection:

Sub Game2_Click()
      Range("ChangedGame2").Value = "Yes"
      UnProtectSheet ("PlayList")
        Range("CustomName1").Offset(0, 1).Value = ""
      ProtectSheet ("PlayList")

End Sub

But when I select the same (as previous) item from the drop-down list, nothing happens.  I have tried replacing "Click" with "AfterUpdate", "KeyPress", "BeforeUpdate", "Selection", and "Select"  (basically, everything I could find on Google, with the exception of "Kardashian").    With each of those events, the code runs only if I've picked a new item from the List.

How can I get that code to run every time I click on the ListBox? 


Posted by: Derek Turner <g4swy@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (15)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Poskan Komentar