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

Thanks for looking at what I wrote, Dave.

Most of what you do appears to be controlled by the Analyze Game button, which can be trusted to generate a click event.

--Right.

So, if I read it correctly, your only problem is when the user selects Custom Games for a second time and does not want to do the same custom game again.

--Exactly!

(Which raises the question of what would happen if the user wanted to analyze the same custom game again.)

--In such cases, the user would just change whatever other inputs he/she wanted to, and click the Analyze Games button again. There would be no reason to click the dropdown, because it is already showing "Custom Game", and the name of that custom game is still showing in cell E1.

Probably the easiest way to handle this is to simply treat the Analyze Game button press with Custom Games selected as a request to analyze a new custom game. As such, the drop-down probably doesn't need any code at all - everything can be initiated from the Analyze Game button which will simply ask the drop-down for its current selection.

--The problem with that is that the user who wants to change Custom Games will see that the old Custom Game's name is still listed in E1. The user won't know that there will be an opportunity to change the game AFTER clicking Analyze Game.

--Also, it's much more likely that a user would use the same Custom Game while varying other inputs than want to create a different Custom Game to analyze. So I was hoping to not have to have the user jump through the same hoop in those cases where the same game was intended.

--But, given that there doesn't seem to be a way to trigger code upon the clicking of the combo box, I may use your suggestion, Dave. I can insert a simple Yes/No InputBox into the Analyze Game code, which won't be much of a burden on the user. And once they encounter the InputBox once, they'll know they have the option of changing CustomGames that way.

So, thanks for thinking about it and thanks for the suggestion!

Dunbar

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

Most of what you do appears to be controlled by the Analyze Game button, which can be trusted to generate a click event.

So, if I read it correctly, your only problem is when the user selects Custom Games for a second time and does not want to do the same custom game again. (Which raises the question of what would happen if the user wanted to analyze the same custom game again.)

Regards, Dave S

----- Original Message -----
From: h_dunbar@... [ExcelVBA]
To: ExcelVBA@yahoogroups.com
Sent: Tuesday, February 24, 2015 9:41 PM
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 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?

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5645 / Virus Database: 4293/9172 - Release Date: 02/24/15

__._,_.___

Posted by: h_dunbar@hotmail.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (14)

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