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

[Ajinkya]

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]

Hi, Ajinkya!

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

Application.Dialogs(<xlBuiltInDialog&#62.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]

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]

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

 

[Ajinkya]

Hi Faseeh, since long time back....


Thanks let me chk...

 

[SirJB7]

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]

Hi SirJB7 & Ajinkya

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

[pre]

* 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]

@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]

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

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]

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]

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]

Hi, Ajinkya!

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

Regards!

 

[Faseeh]

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

 

[SirJB7]

@Faseeh

Hi!

Thanks for sharing that tip. Useful and fearsome at the same time.

Regards!