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

Selasa, 24 Februari 2015

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 56^th 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.

Thanks,

Dunbar

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

Dunbar

---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 -----
From: h_dunbar@... [ExcelVBA]
To: ExcelVBA@yahoogroups.com
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()

with

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.

Best,
Dunbar

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

Regards
David Grugeon

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

Greetings!

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:

<code>
Sub Game2_Click()

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

End Sub
</code>

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: h_dunbar@hotmail.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

----------------------------------
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:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group