Running a macro from button without clicking out of cell?

[Swoop_GB]

Here's the problem.

I'm designing a worksheet for very young children and what they need to do is type a number into a cell and then click a button to run the macro. However, in order to click this button they first must either press ENTER or TAB or click out of the cell. ie, it wont let them just type in a number and click the button cos nothing happens.

How do I make the button work when the kid hasn't ENTERED or TABBED or clicked somewhere else?

[Sewram]

Hi
What do you want the macro to do?
If, say, you want to change the colour of the cell with the macro after you enter a number in a cell, then write/Record the macro in a module like
Sub ChangeColour()
'
' ChangeColour Macro
'

'
Range("A1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

In the Worksheet_SelectionChange add the following
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ChangeColour
End Sub

In the above example, I used cell "A1"
Key in any number and press "Enter" or "Tab" or "Click" elsewhere and the macro will
run automatically to change the colour of cell "A1" to "Green"

Hope this helps
Should you require any assistance... e-mail me at [e-mail]

[Swoop_GB]

Sorry but it's not as simple as that. It's not a case of running 'a' macro either, it's hundreds of different macros, yes I really do mean hundreds.

The app I'm writing is for primary school mathematics, to enable keystage 2 maths coordinators to test pupils understanding of all the government defined key objectives. This means maths problems ranging from 2 + 2 = ? through to "if train A is travelling at.....", etc, etc.

Now generating all these various tests and randomising the questions is fine, I can do that. Writing all the macro's to check answers, store data, recall data, etc, etc, is fine, I can do that too.

The issue I have is on a standard excel worksheet, if I have a cell (or 20) that is unlocked and ready for a child to type the answer "4" (for example) in......if the child DOESNT either press enter or tab or click another cell then the button I put on the worksheet that says "SUBMIT ANSWERS" on it does absolutely nothing when clicked.

Now if this was going to be used by adults I'd say that's fine, just put it in the instructions to make sure you press enter after putting in an answer. You try explaining that to a 6 year old with ADHD. What I need to avoid is thousands of kids all over the country putting their hands up and crying "Miiiiiiiss?! it wont do anything!"

[WaltP]

Does a cell have a LostFocus event? If so, replicate hitting ENTER there. This should run as the SUBMIT button is clicked.

[Swoop_GB]

I like your thinking!

Um.....how do I access / add / check on this LostFocus event?

[monarchmk]

There is no LostFocus event on cell. There is Worksheet_SelectionChange but you could not use it as when you try to edit cell, Excel enters Edit mode and no macros can run while edit mode is in effect.

[Swoop_GB]

Nuts. I was afraid of that.

Anyone got any ideas?

[Swoop_GB]

Just so you guys know in case any other boneheads ask this question, I solved it.

Instead of having a button running my "submit" macro I made a cell look like a button (grey background, border round it, etc), unlocked it and put a run macro command in the SelectionChange event.

Therefore, the user is forced to click out of the data entry cell to click the button (cell).