Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to disable some of excel commands exist in ribbon

0

Hi, i wanna know how to disable some of excel commands that are in ribbon menu. I have an excel file here that i need you to send back the file please in condition that the pivot table command had been disabled so that i cant create the pivot table. Please elucidate how to disable and enable the command/function again. Also do you have any link video & website recomendation related to my question? 

Waiting for your reply.. 

excel
MS Office Version: Fida_mutia
posted by: Fida_mutia (rep: 28) on Fri Jan 10, 2020 at 1:37 pm
Answer
Discuss

Answers

0

You can remove the Pivot Table menu from the Ribbon's Insert tab.

  1. From the Ribbon's File tab, select Options.
  2. Choose Customize Ribbon.
  3. On the right side of the dialog box that opens, find the Insert tab and expand it.
  4. Select the Tables item. Take a look at which buttons are in that menu.
  5. Right-click and choose Remove.
  6. Click OK. This will remove the button from the menu.

I believe the change will affect Excel as a whole, meaning the button will remain invisible for all workbooks of all users.

To restore the button I suggest the following steps.

  1. From the Ribbon's File tab, select Options.
  2. Choose Customize Ribbon.
  3. On the right side of the dialog box that opens, find the Insert tab and select it.
  4. Near the bottom of the dialog box, click the Reset button.
  5. Select Reset only selected Ribbon tab.
  6. Press OK.

I realise that the above is a far way from what you imagined. Please look at this site to get a better understanding of the complexity of what you ask.

However, there may be other methods you might employ to achieve your aim, if you can re-define it. For example, you might argue that you don't care how many pivot tables someone creates in your workbook so long as the file isn't altered. This thought would enable you to seek solutions either preventing changes from being saved or by only distributing copies while the original remains in your possession.

posted by: Variatus (rep: 4889) on Fri Jan 10, 2020 at 8:52 pm
Discuss

Discussion

Actually i want the button is still visible but just unable to be clicked bcs it had been disabled beforehand. For example, i want the sort&filter button in the home tab and picture button in the insert tab is unable to be clicked nor used so how to disable the buttons in the ribbon menu without making them invisible??
Fida_mutia (rep: 28) Jan 10, '20 at 9:12 pm
Disabling Ribbon buttons is probably not possible with VBA. Try and re-define your requirement. Bear in mind that whatever solution you find, it will involve code. Since you seem unfamiliar with VBA it may not be a good idea to deploy it in your project. You lose control to some extent. That may be too high a price to pay for something that might be achieved by a good back-up system instead.
Variatus (rep: 4889) Jan 10, '20 at 9:25 pm
So, the solution is using vba editor? Could you give me the code instruction to disable one of buttons in ribbon menu? 
Fida_mutia (rep: 28) Jan 10, '20 at 10:03 pm
As I said, "Disabling Ribbon buttons is probably not possible with VBA".
Variatus (rep: 4889) Jan 11, '20 at 2:59 am
Well, that was also mean you were saying disabling Ribbon buttons was probably possible too then? Sorry, but when i was working as a data entry staff, i used Excel to input data and the IT Staff were setting up several Ribbon buttons into active and inactive, i mean all Ribbon buttons were visible but some unable to be used nor clicked. So thats why i'm asking you how to disable some of the Ribbon buttons. I can run macro and use VBA editor but i dont know what code that perfectly match my need? 
Fida_mutia (rep: 28) Jan 13, '20 at 1:19 am
I meant the IT staff had disable some of buttons in Ribbon menu in Ms. Excel that i used in working. And i want to know how they do that. Sorry my poor English 
Fida_mutia (rep: 28) Jan 13, '20 at 2:43 am
I did something like that a few years ago. As I recall, it involved XML language embedded in VBA. The XML code was created by a special editor.  I included a link to a website in my reply where you might get the editor.
Look for the reason why you didn't follow up on that link. It's the same reason why I stopped disabling ribbon buttons: In most cases the task of managing a modified ribbon is greater than the benefit derived from it. In your case it's even bigger than for me because you neither know XML nor VBA, not the VBA editor nor the XML editor. And yet you want buttons disabled when one workbook is active and re-enabled when you switch screen to another or close the workbook. 
The switch itself comprises of only 5 or 6 lines of XML code - one for disabling and another for re-enabling. The code that manages the switches, however, needs several event procedures in different levels of the project, smoothly interacting with each other. Getting the code won't end the problem for you. That's just the beginning. Sorry, friend, I don't want to spend my time on that. If it's worth your while look at the web link I provided.
Variatus (rep: 4889) Jan 13, '20 at 3:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login