• 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.

Usuing custom Style in every sheet I open/create

Ronald

Ronald

Member
Hi all,

I know how I can copy or customize a style but that specific style is not available on other future sheets (new or not). I want to have that style on every sheet/workbook I'm using and will be using/creating.

Is it possible to tackle this on any way?

Best regards,

Ronald.

Excel 2007
 
Like I said, that did the trick, thanks again.

btw, I didn't have any option on the merge styles option but did a copy of current style, same result.
 
Dear all,

The above solution works, but only for new created sheets.
Does anyone knows if there is a sulution to have this specific style on every existing sheet?
 
Copy and paste this to your Personal.xlsb Workbook class module. Then restart Excel application. The pattern I used is just a sample that I made myself. You can of course customize it according to your need.

Code: 
Option Explicit
 
Private WithEvents appExcel As Application
 
Private Sub appExcel_NewWorkbook(ByVal wb As Workbook)
    AddStyle wb
End Sub
 
Private Sub Workbook_Open()
    Set appExcel = Application
End Sub
 
Sub AddStyle(wbk As Workbook)
 
    On Error Resume Next
    wbk.Styles("MyStyle").Delete
    Err.Clear: On Error GoTo 0: On Error GoTo -1
    With wbk.Styles.Add(Name:="MyStyle")
        .IncludeNumber = True
        .IncludeFont = True
        .IncludeAlignment = True
        .IncludeBorder = True
        .IncludePatterns = True
        .IncludeProtection = True
        With .Font
            .Name = "Calibri"
            .Size = 11
            .Bold = True
            .Italic = False
            .Underline = xlUnderlineStyleNone
            .Strikethrough = False
            .ThemeColor = 2
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        With .Borders(xlLeft)
            .LineStyle = xlDash
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlRight)
            .LineStyle = xlDash
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlTop)
            .LineStyle = xlDash
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlBottom)
            .LineStyle = xlDash
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = 0
            .Color = 5287936
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With
    
End Sub
 
Thanks Sam.
I did that, just paste your code, without changing something, to see it it works (Excel 2007) but the new style isn't in my style options.... :-(

Did I do anything wrog? (print screen attached)
 

Attachments

  • styles.jpg
    styles.jpg
    64.7 KB · Views: 2
No you don't need to insert a Class module for that. I said 'Workbook class' module (it's basically the code module for the Workbook). I just mentioned 'class' because workbook is also a class.
 
Ah, okay.
It work indeed, the only thing now is that it works only for new workbooks.

Is ther any way to have this 'MyStyle' also available when I open previous created sheets?
 
Hi, Ronald!
Sam Mathai Chacko said:
...
Private Sub appExcel_NewWorkbook(ByVal wb As Workbook)
AddStyle wb
End Sub

Private Sub Workbook_Open()
Set appExcel = Application
End Sub
...[/Code]
Click to expand...
Does this quoted part of the code posted by Sam Mathai Chacko suggests you something?
Regards!
PS: Bold is mine.
 
Well, if you like to start riddles, yes sure. I've change the code in different ways without the wanted result.
The initial question from last Friday was if it could be applied for every existing sheet, my apologies if I'm not able to figure this out by myself....

Additonal, thanks for replying.
 
Ronald, I'm sure you would have guessed by SirJB7's post above that I had only used the routine for a new workbook. To make the style for any workbook that is opened, all you had to do was go to the class selection drop-down, select appExcel class, and select the required event from the adjacent event drop-down.

In case that isn't clear, just copy paste this in the same module where you've copied my original codes (no need to delete anything)

Code: 
Private Sub appExcel_WorkbookOpen(ByVal Wb As Workbook)
    AddStyle Wb
End Sub
 
Thanks for your patience and explanation Sam.
I understand now wht you mean. I tried that also but as substitute of:
Private Sub Workbook_Open()
Set appExcel = Application
End Sub

I've created my wanted Styles now and there are working fine.

One last question concerning definig this Styles by default when opening sheets:

In one of my styles I only want to change everything to a specific font & size. Everything else should stay the same (borders, colours, bolt, italic etc.)

I did non-include the different make-up but things as bold, italic on the sheet are also being unset when I apply the style. Below my changes.

On Error Resume Next
wbk.Styles("Calibri10").Delete
Err.Clear: On Error GoTo 0: On Error GoTo -1
With wbk.Styles.Add(Name:="Calibri10")
.IncludeNumber = False
.IncludeFont = True
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
With .Font
.Name = "Calibri"
.Size = 10
Is there a way to ignore things as bold & italic on the style?
Whatever the answer is, many thanks for your help!
 
Yeah, I did that but without result, the original text in bold and/or italic was set to standard (no bold, no italic)
 
I commented them out as you can see below.
However if a sheet contains bold or italic they are set to non-bold and/or non-italic when I apply the style.
For this specific style I only want to set everything selected to calibri 10 when I apply it to something. everything else should stay as it is.


On Error Resume Next
wbk.Styles("Calibri10").Delete
Err.Clear: On Error GoTo 0: On Error GoTo -1
With wbk.Styles.Add(Name:="Calibri10")
.IncludeNumber = False
.IncludeFont = True
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
With .Font
.Name = "Calibri"
.Size = 10
'
' .Bold = True
' .Italic = False
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .Underline = xlUnderlineStyleNone
' .ColorIndex = xlAutomatic
' .TintAndShade = 0
' .ThemeFont = xlThemeFontMinor
End With
End With
 
When you add a new style, Excel by default takes some values. The default value for Bold is False. So if you do not specify it, Excel will assume that you do not want to alter the default Bold (or Italics) features.
 
You must log in or register to reply here.
Back
Top