DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How To Change the availability for a menu item using VBA in Excel?

#tochangetheavailabil #vbamacros

In this blog post, you will learn how to ** change the availability of a menu item using VBA ** in Microsoft Excel. Let’s see them below!! Get the official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

  • Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
  • Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic
Select Visual Basic

  • Now, you have to copy and paste the code given below. 
Sub ToggleMenuControls()
Dim m As CommandBarControl, mi As CommandBarControl
    Set m = CommandBars.FindControl(ID:=30002) ' File Menu
    If m Is Nothing Then Exit Sub
    For Each mi In m.Controls
        If mi.ID = 18 Then mi.Enabled = Not mi.Enabled 
        ' toggles the state for the Print menu
    Next mi
    Set mi = Nothing
    Set m = Nothing
End Sub
Enter fullscreen mode Exit fullscreen mode
  • You need to save the code by selecting it and then close the window.

Save the Code

  • Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
  • Then, you need to choose the Macros option in the Code section.

Choose Macro option
Choose Macro option

  • Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

  • Finally, after running the macro, you will get the output ** as **change the availability of a menu item in Excel.

Summary

We hope that this short tutorial gives you guidelines on how to change the availability of a menu item using VBA in Microsoft Excel. Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!

Read Also:

Top comments (0)