• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Protection tab of the Format Cells dialog box is not appearing.....!!!

Ajinkya

Member
Hi Friends,


While enjoying with excel work, I just got one thought/ challenge...i would like to share it....


How can hide/ unhide "Protection tab of the Format Cells dialog box" it should not appear in the sheet or worksheet.


Thanks in advance


Rgds,


Ajinkya
 

SirJB7

Excel Rōnin
Hi, Ajinkya!

As far as I know, you can display a built-in dialog with this statement:

Application.Dialogs(<xlBuiltInDialog>).Show

where if used with xlDialogCellProtection it will pop-up the last tab of the format cells dialog menu. But I don't know any way to inhibit any of those tabs.

Regards!
 

Ajinkya

Member
Hi SirJB7,


Long time back i had received excel file from my friend, in this excel file if im going to open "format cell dialog box" the Protection Tab is not appearing for the specific sheet1 only,sheet2 and sheet3 are having the protection tab.


Friends,

Pls guide...how to do it...i am sure, i will get the solution from this forum only because you are the only masters who can guide me...


Thanks :)


Cheers!!!
 

Faseeh

Excel Ninja
Hi Ajinkya,


Your Sheet1 is password protected against "Format Cells".

(You can reach Protect Format Cells from Review Tab > Protect Sheet > Format Cells > [Will ask for Password])


About Your problem.

Select Sheet1 and...

1. Go to the Review Tab.

2. Click Unprotect Sheet.

3. I expect it will ask you for a password.


How to Unlock it:


J-Walks Spread page discusses it here:

http://spreadsheetpage.com/index.php/tip/spreadsheet_protection_faq1/#P1


(You can try it for yourself, take a new workbook, protect one of its sheet for Format Cell and do nothing remaining sheets, and now check for Protection Tab by Right clicking>Format Cells)


Hope that helps!! :)


Regards,

Faseeh
 

SirJB7

Excel Rōnin
Hi, Ajinka!

If Faseeh suggestion works for you, well, I apologize but I didn't understand what you were asking for. If not, please consider uploading the file.

Regards!
 

Debraj

Excel Ninja
Hi SirJB7 & Ajinkya

Yes, Faseeh is suggestion is correct. But he missed to remind that,

[pre]
Code:
* At the time of Protect Sheet, checkbox for 'Allow all users of this worksheet to..
Format Cell --> should be true.
Excel will Display the

[b]Application.CommandBars("Worksheet Menu Bar").Controls("Format").Controls("Cells...").Execute

But due to Protection criteria, It will hide the Cell Protection TAB from the Format Cell Dialog Box.

Application.Dialogs(xlDialogCellProtection).Show[/b]


In the same manner, if we also select

* At the time of Protect Sheet, checkbox for 'Allow all users of this worksheet to..
Insert Row --> Checked.
[/pre]
then, it will locked the Insert > Column but, Insert > Row will be unlocked.


Passing the THANKS to Faseeh in advance..
 

SirJB7

Excel Rōnin
@Debraj Roy


Hi!


Maybe I'm a little asleep today, but:

a) Application.CommandBars("Worksheet Menu Bar").Controls("Format").Controls("Cells...").Execute raises error 5

even with:

Application.CommandBars("Worksheet Menu Bar").Controls("Format").Execute

b) how could the format cell dialog hide the protection tab? I still don't get it...


Regards!
 

Debraj

Excel Ninja
Hi Sir,


A) Application.CommandBars("Worksheet Menu Bar").Controls("Format").Controls("Cells...").Execute

I afraid, I cant solve it..

Its just calling the dialog box in excel 2003 shortcut method. It works perfectly for me.

May be
Code:
Application.CommandBars("Hoja de barra de menús").Controls("formato").Controls("Las células...").Excecute

will work for you..(ha ha.. Sorry.. blind guess)


(B)If we allow users to Format Cell, Excel will display the below tabs..

[pre][code]Application.Dialogs(xlDialogFormatNumber).Show
Application.Dialogs(xlDialogAlignment).Show
Application.Dialogs(xlDialogFontProperties).Show
Application.Dialogs(xlDialogBorder).Show
Application.Dialogs(xlDialogPatterns).Show
[/pre]
as these are the part of Formatting a cell.


but

as the sheet was protected, MSO, will remove the CellProtection Dialog Box

Application.Dialogs(xlDialogCellProtection).Show[/code]


Please see the attached.

https://www.dropbox.com/s/f31wz9ppl74s170/captura%20de%20pantalla%20.xlsx


Please shout on me, if I am still wrong.. :(
 

SirJB7

Excel Rōnin
Hi, Debraj Roy!

Thanks a lot for sharing that tip, and more indeed for the Spanish version shoot, I appreciate. I'll give a try and post the results.

Regards!
 

Ajinkya

Member
Hi SirJB7,


Extremelly sorry, answering little bit late......:-(


Actually, after Fasseh's guideline, i got the solution.


Now, i have some more intresting things to learn which Debraj Roy update...


thanks...... all :)
 

SirJB7

Excel Rōnin
Hi, Ajinkya!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 

Faseeh

Excel Ninja
Hi All,


Just to add a little point that Excel Password can be opened using Excel from Open Office. See this Video:


http://www.youtube.com/watch?v=i28PoAZoQJc


Regards,

Faseeh
 
Top