Stable formating

[shahsavand]

I have workbook contains a lot of sheet.i wanna make all of the sheets' formating hidden only.(not locked)
Even when I create a new sheets

 

[RickXL]

Hi,

I am not quite sure what you mean.

However, you can paste Formats from one Worksheet to another. So you could have a Workshheet set up, possible hidden, that had the right formatting and then you could paste it over any new Worksheets when they are created. Would that solve the problem?

You could add some code to detect the creation of a new WOrksheet and apply the Formats automatically. For instance:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Worksheets("Sheet1").Range("A1:F8").Copy
    Sh.Range("A1").PasteSpecial xlPasteFormats
End Sub

That needs to be placed in the ThisWorkbook module.
The example copies the formats from Sheet1 in cells A1:F8 and pastes them into any new Worksheet.

Regards,

 

[mikerickson]

One way to do that would be to re-define the Normal format to the protection style that you want.

 

[shahsavand]

Hi,

I am not quite sure what you mean.

However, you can paste Formats from one Worksheet to another. So you could have a Workshheet set up, possible hidden, that had the right formatting and then you could paste it over any new Worksheets when they are created. Would that solve the problem?

You could add some code to detect the creation of a new WOrksheet and apply the Formats automatically. For instance:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Worksheets("Sheet1").Range("A1:F8").Copy
    Sh.Range("A1").PasteSpecial xlPasteFormats
End Sub

That needs to be placed in the ThisWorkbook module.
The example copies the formats from Sheet1 in cells A1:F8 and pastes them into any new Worksheet.

Regards,

Maybe I explained it in a bad way.
I have worksheet protected in order to not show the formulas.when I unprotect it and then reprotect it with a macro code it changes to lock formating style again.
I want it to be in hidden format even when I create a new sheet and lock it with password: "s"

Format cells/protection/Locked

 

[RickXL]

It may be the "formatting" word that is causing the confusion.

Are you saying that when you create a new worksheet you want it to be hidden? If so then try this:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If Sh.Type = xlWorksheet Then Sh.Visible = False
End Sub

 

[shahsavand]

It may be the "formatting" word that is causing the confusion.

Are you saying that when you create a new worksheet you want it to be hidden? If so then try this:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If Sh.Type = xlWorksheet Then Sh.Visible = False
End Sub

yeah hidden word is confusing.
please go to this direction Format cells/protection/Locked

in protection tab there's two option which you can lock or hide a cell format.
i want the whole sheet be in hidden style.even when i create a new worksheet

 

[RickXL]

We probably need to do this in steps.

Is this right?

When a new worksheet is created:

1. Copy formats from the template sheet. (If so, what is the template sheet name?)
2. Paste the formats to the new sheet.
3. Protect the new sheet.
4. Hide it.