VBA code - Hide the Gridlines, Headlines, Formula line, Tabs and protect the workbook

[gnaske]

Can anybody please help me with two codes.

I'm not a VBA geek and can't make the codes myself, but I'm sure I know where to place the codes in the VBA module.

I had attached my workbook and want to use it as a master to future projects and anybody who can help, can add the codes in this workbook and return it to me.
I also attached two pictures, where I believe the two codes should be inserted.

A little about the workbook
To prevent other in destroying something in the workbook, I'll design my own Ribbon and hide the Excel Ribbon.
For that I already designed a "Show/Hide Excel's Ribbon" in this master and I can easily add new buttons to my own Ribbon.

What I want the two new codes to do
- When somebody open the workbook, Excel's Ribbon, the Grid lines, the Head lines, the Formula line and the Tabs will be hidden and the workbook protected as default.
- When I want to edit the workbook and open Excel's Ribbon, it should show all above and unprotect the woorkbook and all the Tabs.
- When I close Excel's Ribbon or save and close the workbook, it should all go back to be hidden and protected.

What I need is a code to hide the Grid lines, the Head lines, the Formula line and the Tabs and to protect the workbook when it's being opened.
I believe the CallBackRibbon code can do that, when the workbook is being opened.
And the other way around, when I open Excels Ribbon.

I don't know if it's possible to make codes like that, but I hope it is and I hope somebody can help me and insert the codes in the Master.

Thank you in advance.

Ib
Denmark

[Alf]

Perhaps this link may be of help?

http://www.mrexcel.com/forum/excel-q...-workbook.html

To hide gridlines you could just add

Please Login or Register  to view this content.

Alf

[gnaske]

Hi Alf.

It actually works - Thank you very much
The workbook is attached.

I inserted this code below in the Password Form, which show and unprotect all.

And this in the CallBackRibbon module Button1, which hide and protect all.

Now I only have two problems

1.

When I use Application.DisplayFormulaBar = True/False, is it global, which mean the formula bar is hidden in all Excels, including a new opened Excel too.
I only need it to be hidden in this Workbook.
I can't get ActiveWindow.DisplayFormulaBar = True/False in each sheets to work.
Do you know a code for that ?

2.

When I protect/unprotect the workbook, I use "Next sheet" and protect/unprotect all sheets in the same code.
It should be possible to do the same with the "Display", instead of pinpointing each sheet.
Do you know how to include that in the Display Code ?

Thank you in advance

Ib
Denmark

[Alf]

For problem 1 I don't know of a solution at the moment but will have a look around,

For problem 2 perhaps a macro similar to this could be used?

Please Login or Register  to view this content.

Alf

[gnaske]

Hi Alf.

It works - Wouuv - Thank you

I have some hidden columns in each sheet in my project and I'm now trying to find a code to Show/Hide these columns.
This is actually funny when it works.
Thank you

[gnaske]

Hi Alf

Now the hiding of the columns works too
Look the attached.

The last I need in this master is the Application.DisplayFormulaBar not to be global, but only in a specific workbook.

[Alf]

Hi Ib

No luck in finding a specific workbook setting for "Application.DisplayFormulaBar" but I think you could shorten the code for hiding the columns a bit. Normally one can avoid using the command "Select" and "Selection" instead just use the "proper" command i.e.

Please Login or Register  to view this content.

Alf

[Andy Pope]

Look at using the events in thisworkbook to determine when to toggle status of formula bar.

[Alf]

This link shows how to hide the ribbon in a specific Excel file. Perhaps an overkill?

https://www.youtube.com/watch?v=2IMOPlLTOAY

Or you could use Andy's suggestion i.e. putting a macro containing the line

Please Login or Register  to view this content.

in "ThisWorkbook" as shown in YouTube link.

Alf

[gnaske]

Hi Alf - Andy.

I had decided to use this code below in ThisWorkbook - Attached.
- When the workbook open, it first maximize and close the Formula-, Scroll- and Status bar.
- Then it unprotect all Tabs, because it can't hide any columns if it's protected.
- Then it hide the columns and I use Sheets().Select because I need to hide a lot of different columns in different Tabs - Don't know else, how to tell which Tab and Columns, in each Tab, I want to hide.
- Then it close the Tabs, Grid and Heading in all Tabs.
- At last it Protect all the workbook.

When one of the Admins use the Hide Excel Ribbon button from Andy's Visual Ribbon Editor, it doesn't contain the Application.DisplayFormulaBar = False.
No need for that, because the Formula Bar is hidden, when the workbook reopen.

I had tried to play with Display Formula Bar.
Sometimes, when I open a new Excel and hide the Formula Bar and then open another new Excel, the Formula Bar is hidden in that workbook too.
Other times it isn't.
I can't find any pattern in, when it's hidden or not - Strange Hmmmmmmmm

Anyway until now, thank you for all your help - I had talked with Andy in private mails too.
I really appreciate the help from the both of you and couldn't manage all this without it.
I'm a bit stupid with all this coding, but find it very enjoyable when I get help and manage to put it all together, to something which actually works too.
Thank you

Ib

[Alf]

Really a minor point but the "HideColums" part could be "condensed" a bit more i.e.

Please Login or Register  to view this content.

If your problem is now solved don't forget to mark this thread "Solved" as per forum rules.

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

[gnaske]

Thank you very much Alf.

I'll use your EntireColumnHidden code.
And I'll mark this thread as solved.
I was actually looking for that yesterday, but couldn't find it.

Kind regards

Ib

[Andy Pope]

add the following to the Thisworkbook object

Please Login or Register  to view this content.

Now switching between workbooks should hide/reveal formula bar

[gnaske]

Hi Andy.

I found the FormulaBar is connected to Excels Ribbon and not to a specific workbook, which mean if I open an empty Excel, the FormulaBar will be visible.
If I want to remove the FormulaBar using Excels settings, is it global in all Excels I open from my computer and the same if I use the setting in the Ribbon.
The Grid, Heading, Scroll etc. is in the present workbook and will not have any global effect at Excel.

Your Ribbon Editor doesn't give the opportunity to remove the FormulaBar, together with the rest of Excels Ribbon and this was actually the special code I was asking for, when I wrote to you in a private mail.

Thank you very much Andy.

Kind regards

Ib

[Andy Pope]

The formula bar is not related to the ribbon. It is an application level setting.

[gnaske]

Okay - I don't know all the expressions and especially not in English.
E.g Formula Bar is Formel linje in Danish and the right expression for me would be Formula line, although is wrong.

I just try to translate as good I can and tell my experiences of how it seems to be and for me it looked as it was connected to the Ribbon.
But I'm fast learning, including because of you

[Alf]

Perhaps this link?

http://wwwhome.ewi.utwente.nl/~tries...cel/excel.html

but nothing for "normal" expressions like "sheet", "workbook". "range" and so forth and no VBA translation found.

Alf

[gnaske]

Hi Alf.

Thank you.

My Excel is at Danish and when I ask about something in forums like this or in private mails, it can be difficult to translate to correct technical English expressions.
When I wrote in this forum the first time, I didn't even knew when to use Workbook or Worksheet and I'm not exactly sure yet, when I should use Worksheet.
For me is either a Workbook or a Sheet.

When I write about coding with nerds (like you and Andy and when I write nerds, I mean it in a good way) those people believe I know a lot about basic coding and expressions and I believe this is normal
But I don't and sometimes I'm a biiiiig question mark, when I see the reply.
And then I'm a little afraid to ask again, because I'll look as a totally stupid fool and the person I were asking, will get tired of my stupidity.

That's also why I sometimes attach pictures and Workbooks when I ask about something.
In that way it's more easy to see what I mean, if I don't express myself clearly or use the right technical expressions.
And maybe I ask to put the codes into the Workbook too, instead of getting half a code back, I don't understand anyway.
In that way I can see what had been done and maybe understand it all a little bit better.

But I had learned a loooot and actually tried to make VBA codes myself this time.
I understood it right away, when both you and Andy had send a code and it worked too.
And even when trying to simplify some of the codes.

I just want to say thanks.
All this has been really useful for me and I had learned a lot

Ib

[Alf]

And then I'm a little afraid to ask again, because I'll look as a totally stupid fool and the person I were asking, will get tired of my stupidity.

Hi Ib

Remember we have all been there (at the learning stage) and we still keep on learning just because people ask so it's a win/win situation the way I see it. I either learn something new (in this thread how to use ThisWorkbook for macros. I didn’t know that before) or I can refresh some "old" knowledge I've almost forgotten since it's so long since I last used it. Remember the only "stupid" question is the one you don't ask.

When trying to give an answer in a thread I always wonder how much do this person know because it can be very difficult to judge a persons level of expertise and the answer you give is based on what you think they know so at times one perhaps tend to explain not enough about the how and the way leaving the OP (that's you by the way Original Poster) quit puzzled. So if things are unclear don't hesitate to ask.

Regards

Alf

[gnaske]

Hi again Alf.

I understand all are not good to tell about their level and how difficult it is for the supporter to guess about it and that's also why I always start to tell I'm bad

This is how I started this thread

Can anybody please help me with two codes.
I'm not a VBA geek and can't make the codes myself, but I'm sure I know where to place the codes in the VBA module.