Protection tab of the Format Cells dialog box is not appearing.....!!!
- Thread starter Ajinkya
- Start date
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!
As far as I know, you can display a built-in dialog with this statement:
Application.Dialogs(<xlBuiltInDialog>
.Showwhere 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!
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!!!
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
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
Debraj
Excel Ninja
Hi SirJB7 & Ajinkya
Yes, Faseeh is suggestion is correct. But he missed to remind that,
[pre]
[/pre]
then, it will locked the Insert > Column but, Insert > Row will be unlocked.
Passing the THANKS to Faseeh in advance..
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.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!
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
[/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..
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).Showas 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..