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.

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.

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

Option Explicit

Private Sub Worksheet_Activate()
    CommandButton1_Click
End Sub

Sub CommandButton1_Click()
'Replace the MsgBox command with the macro desired.

    MsgBox "It clicked !"
End Sub

Paste in ThisWorkbook Module :

Option Explicit
'Having to activate Sheet2 first because Excel activates/opens Sheet1 by default. Therefore, the button
'will not get auto-clicked.
'If the button is located on any other sheet, this Workbook_Open event is not required. When the user
'goes from Sheet1 to the Sheet in question, the Sheet_Activate event for that sheet will run the macro.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
    Sheets("Sheet2").Activate
Application.ScreenUpdating = True
    
    Sheets("Sheet1").Activate   'This sheet has the button you want auto-clicked
End Sub

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.