Solved: Run Macro Whilst Editing

[Phelony]

Hi Guys

If from the title you happen to know the answer is "you can't", then don't worry about anything else here.

If however, that's not the case, then I have a problem! :help

I've got a macro that runs from a command button, it picks up one of several input factors in a given cell and then runs off to find the appropriate data.

However, in testing, it turns out that people aren't quite getting the whole "press return" concept. So, I've thrown in a little message box code to tell them what to do if they haven't committed the data entry into that cell.

If Range("E2") = "" Then
MsgBox ("You must enter a valid code or account number and press enter for the search function to work")
Else
End If

Thing is, this isn't working. :motz2:

Is this because you simply can't run a macro whilst editing? Or is there a way to do it that would mean an improvement to the code I've got?

Silly question most likely as I susepect it's the prior of the two, although it never hurts to check! :think:

Thanks

Phel x

[Bob Phillips]

When in edit mode, Excel has control, so you cannot run code. Why not ust test the value at some other point, siuch as close, and stop them at that point.

[Phelony]

Until there is an entry in that field there is nothing further to test, the code populates the sheet with entries from a database and E2 contains the reference to retrieve that information.

I thought it would be a "mechanical" issue rather than a coding one. :doh:

I'll just put in a floating comment. Not as clean as I'd like but I'd also like to be building the databse on SQL rather than Excel anyway, so we can't always get what we want!!

Thanks for the help Xld

Phel x

[rory]

You can run code in Edit Mode, but you have to be using Windows timer APIs to do it, and I couldn't speak to how stable it is in the long term (though I can say that if you get it wrong, you'll be rebooting a lot!)

[mdmackillop]

Two thoughts.

Check that all parameters are entered before the code runs. ie, Exit the code with the offending cell selected.

Present the User with an Inputbox to enter the required information.

[Phelony]

Present the User with an Inputbox to enter the required information.

That is really annoyingly obvious...:banghead:

Thanks Mac! Genius.

[Phelony]

Hi Guys

Reopening this one to see if a resolution to a small problem can be found.

I've got the above as a input box that opens when the sheet is opened, it seemed safe enough to do as the sheet doesn't function without this input so its the first thing it prompts you to do.

I've so written into the workbook that when it closes it goes through each page and clears any remaining entries ready for the next user.

However, when closing, the sheet with the input box gets selected and it runs the macro for the input box...is there a way to avoid this happening? To somehow disable the macro for the input box from within the macro for closing the workbook?

Any help would be appreciated.

Thanks

Phel x

[Bob Phillips]

Disable events? Application.EnableEvents = False, and reset afterwards.

[Phelony]

Perfect! Thanks Xld. :bow: