CODEWITHSUNDEEP
excelvba
John Joseph
John Joseph

Reputation: 1187

How can I toggle the Focus Cell menu option from VBA?

Excel has a nifty feature called "Focus Cell" that highlights the row and column of the selected cell... A snippet of MS Excel showing the Focus Cell option

The issue is that this behavior is not always desirable. Can I use VBA to toggle this setting on and off, based on some user behavior? For example, I'd like to use the SelectionChange event to turn Focus Cell on if the selected row is 10 or higher.

Upvotes: 0

Views: 60

Answers (1)

DecimalTurn
DecimalTurn

Reputation: 4218

In Microsoft 365, you can toggle Focus Cell with the following line:

Application.CommandBars.ExecuteMso "FocusActiveCell"

This is equivalent to pressing the button from the Ribbon UI. See ExecuteMso's documentation for more details.

In this case "FocusActiveCell" is the idMso of the button. You can find the up-to-date list of those ids in the file excelcontrols.xlsx that is maintained in the OfficeDev/office-fluent-ui-command-identifiers repo on GitHub.

And if you need to check if the Focus Cell feature is ON or OFF (True or False), you can use:

If Application.CommandBars.GetPressedMso("FocusActiveCell") Then
    'Your code here
End If

This GetPressedMso function basically checks if the button in the ribbon is pressed.

Upvotes: 3

Related Questions