Command button do not activate until changing cell.

[Hybris6]

Hi all,

this is my first post to the forum

I have a small problem with my command button in excel.

A short description of my program would be I made a form with a save button that opens up another excelbook and save some data from the form in the other book and then closes the other book. I put all the code in the buttons "on click" part.

The problem is that the users cannot click on the button (totally unclickable) if they have an active cell where they wrote something in.

The workaround is simply to click in a new cell (then the button works fine) but if you start to type anything in the new cell the button becomes unclickable again.

The problem is not everyone that will use the program have the time/patience to click in a new cell before pushing the button so I guess Im looking for something like
button on mouse over get focus or something but I cant get it to work.. could someone please enlighten me

Thanks

[millz]

You can try a workaround like:

Please Login or Register  to view this content.

So when a user presses enter or tab after entering in the cell, it would be equivalent to clicking the button. You may want to also check if the Target is within a specified range where the user would enter data in.

[Hybris6]

Hello,

wouldn't that execute the button every time someone hit enter? (this would be a problem since the spreadsheet is a report and by clicking the button it opens up a summary page and adds the values from the form, sends some emails and then clears the form before closing it). Anyway if i hit tab the button becomes unstuck anyway. It is just if i start to write into a cell and then tries to click the button then the button is inactive (because it is the current cell where im typing things into that is active (like the cell is waiting for more input and thereby locks the control).

[Solus Rankin]

I think millz meant that to be Worksheet_SelectChange. As it is now, every time you press a key it would click the button. Maybe:

Please Login or Register  to view this content.

But it would have to me an activeX button.

[Hybris6]

Hi and thanks.

So there is no way to do this for a standard button (not activeX)?

I was thinking in the excelsheet if I could put some code in a onmousemove or something to shift focus to the button from the cell.. any ideas?

Also is there a way to run VBA code (like a button like this) even for users that doesnt allow macros? (I'm doing this in my company and I would not really like to turn off the protection for all computers... Or atleast is there a way to code so instead of the yellow warning on top of the sheet asking to run the macro would it be possible to have a msg box come up and ask if they would like to allow macros? (not everyone here is good with computers and to instruct them how to turn on macroactivation would be tough heh

[blue.chio]

Hi,
perhaps something like this for the second problem, you can try Ken's idea
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

[Hybris6]

Hmm yeah seems like a good way to fix problem no. 2. Thanks

[blue.chio]

Thank you for the feedback and rep. hope it 'll help you!

[Solus Rankin]

No, standard buttons don't have events, and there isn't a Mouse_Move event for a worksheet.

[blue.chio]

XeRo Solus, I tried in the past to do something similar with a form control (button) and I did not find anything to work, I used an activex as you also posted.