question

BenneGort-5847 avatar image
0 Votes"
BenneGort-5847 asked BillBell-6414 answered

How to disable the quick acces ribbon through Excel VBA

I use Excel 2016 installed on a stand-alone desk-top computer
I have written a program for employee info and to register free days / and holydays etc.

How to prevent other people to have acces to the commands on the Ribbon
and the Quick Acces Ribbon, such as OPEN FILE / SAVE FILE / NEW FILE etcetera.

In fact, what I mean is to hide the RIBBON and the QUICK ACCES RIBBON when
the program starts and enable them again when the program is terminated.

Are there specific commands in VBA

Many thanks in advance

B. Gort
The Netherlands

office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Your problem is related to Excel for developer, I changed the tag to office-vba-dev, the reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

To handle the Save or SaveAs commands, you can add these lines to your module:

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    . . .    
 End Sub

To cancel the operation, execute ‘Cancel = True’.

I think that you can let the user to freely use New and Open operations to work with any other workbooks. Blocking Excel globally looks too severe.

But maybe the protection features of Excel can be used too. See the “Protect” group of “Review” tab.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BenneGort-5847 avatar image
0 Votes"
BenneGort-5847 answered

Dear Viorel-1

The meaning of it is that the user cannot execute other Excel files because it is a
very large program and very big (more then 800 macro's and counting)

And because of the amount of information on the worksheet I need as much space
as possible, so every line is more info visible in the sheet
(I do hide the formula bar and the row and column indicators)


I hope this info is sufficient

Many thanks in advance

B. Gort
The Netherlands




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BillBell-6414 avatar image
0 Votes"
BillBell-6414 answered BillBell-6414 edited

Check this post out


I used the Custom UI Editor to this. Here is the post I used to understand how to do it.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BenneGort-5847 avatar image
0 Votes"
BenneGort-5847 answered

Dear BillBell-6414

Thanks for your answer, but this is about customizing the ribbons.

What I want is to HIDE the ribbons so that users cannot us them
and only can use the menus provided in the Excel-program

Is that possile?

Many thanks in advance

Benne Gort

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BillBell-6414 avatar image
0 Votes"
BillBell-6414 answered

The best description of how to do this is in a book called Automated Data Analysis Using Excel (Chapman & Hall/CRC Data Mining and Knowledge Discovery Series) 2nd Edition. It has a whole chapter on the ribbon including the QAT. It is an excellent book on Excel and data analysis. I got it last year.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.