How to ACTUALLY click a button in excel? (triggering the "disable edit" bug)

Okay, I've googled this, and I haven't found an answer.

Excel has this funny bug where if you disable the formula bar and click a button, the edit functionality disappears until you exit out of the worksheet and go back in. The way I got around this was to enable the formula bar and then disable it again, which causes the cursor to re-appear.

However - I want to use this "disable editing" bug to my advantage. I have a worksheet called "Config" that contains all the various values that I use in other worksheets, and I don't want users to edit this worksheet.

So I created a dummy button, and sure enough, by clicking this dummy button (with formula bar disabled), it does make the cursor disappear and thus prevents editing. The problem is that I don't know how to activate this dummy button whenever the worksheet is activated. I know the various ways to activate the clickbutton event, but activating the event does not trigger the "disable editing" bug.

Any ideas? Thanks!

.
Quicker and easier to password protect that one sheet.

use this code to disable formula bar:

Application.DisplayFormulaBar = false

Then try clicking a button - the cursor will then disappear from that worksheet.

Originally Posted by Logit

.
Quicker and easier to password protect that one sheet.

protecting that one sheet prevents macros from editing that sheet. Like I said, that worksheet contains values used by other worksheets. Macros in other worksheets contain all their values, such as dropdowns that are dynamically updated in the "Config" worksheet. So protecting the worksheet won't work.

Originally Posted by rorya

Protect it when opening the workbook and specify the userinterfaceonly:=true argument.

Thank-you for replying. Actually as soon as I said "it won't work" I went and Googled how to protect a worksheet from users only, and it came up with the userinterfaceonly argument. Thanks for helping.

My spreadsheet is a showcase of functionality that is basically Jira with forecasting and resource planning ability. If there is anything cool that you can do with Excel macros, I'll find a way to incorporate it into my spreadsheet. So if there's a way to ACTUALLY click a button as requested in the OP (disregarding the fact that there is an alternative way to achieve the objective), please let me know. Thanks!

.
Paste in the Sheet Module :

Please Login or Register  to view this content.

Paste in ThisWorkbook Module :

Please Login or Register  to view this content.

As indicated above, Excel opens Sheet1 by default unless otherwise instructed in the Workbook_Open event. If the command button you are targeting resides on Sheet1, these macros will do what you desire.
If the command button resides on any other sheet, the only macro required is the first two - you can ignore the ThisWorkbook_Module macros. NOTE: this macro relies on an ActiveX CommandButton ... not a
UserForm Button.

Hope this is understandable and that it helps.