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

Font in VBA will not change

bobhc

bobhc

Excel Ninja
Good day All


I have this in a module

[pre]
Code: 
With Selection.Font
.Name = "Calibri"
and I am trying to change it to

"With Selection.Font
.Name = "Arial Black"
[/pre]
I have changed the font saved in VBA and run code but the font still shows as Calibri, but go back to VBE and it shows as Arial Black ??
 
Works fine for me with Excel 2010 & 2013


Are you sure you don't have any more code that is changing it back or undoing it?

Have you put a Break point (on a line in front of the above code

Select a line and press F9

Then step through the code when i t pauses and watch what happens to teh excel sheet
 
Good day Hui

This is the full code inserts company details with time, I have tried some "weird" fonts to get a striking visual change but no difference !!

[pre]
Code: 
Sub NameandTime()
'
' CompanyNameandTime Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
ActiveCell.FormulaR1C1 = "Companys Name and Address"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
ActiveCell.Activate
Selection.Font.Bold = True
With Selection.Font
.Name = "Cooper Black"
.Size = 40
.Italic = True
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.ThemeFont = xlThemeFontMinor
End With
End Sub
[/pre]

Changing font size or style such as Italic works?
 
Bobhc


Try removing the '.ThemeFont' line

I think that will be changing the font to a default style


I'd also simplify the code to:

[pre]
Code: 
Sub NameandTime()
Range("A1") = "Companys Name and Address"
Range("A2").FormulaR1C1 = "=NOW()"
With Range("A1:A2").Font
.Bold = True
.Name = "Arial Black"
.Size = 14
.Italic = True
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
End With
End Sub
[/pre]
You will note that you don't need to .Select items to use them

That helps speed up things and also makes reading the VBA code simpler
 
bobhc,


It is also working fine for me(in excel 2007).


just have a look at the file for your reference.


https://hotfile.com/dl/164867092/4fdb747/Font_Change_working_fine.xlsm.html

[make changes at B2(>1 or <1] and run the macro to test it]


Can u please upload a sample file(with dummy data) so that we can have a look at what exactly happens when your code runs?


Regards,

Kaushik
 
My thanks to Hui and kaushik03 for your help,


Just getting on the first step of working my way through a VBA book. Hui I have tried your cleaner code, works a treat....but is there away that your code could be adopted to insert code in any highlighted cell in a worksheet, I know that my example does this but it is not as tidy
 
Hi bobhc,


I am sure Hui will reply but, meanwhile, could you please clarify the following:


Do u mean to say that once you select a cell/group of cells or if a cell/range of cells are highlighted, the formatting should happen automatically?


Kaushik
 
[pre]
Code: 
Sub NameandTime()
Selection = "Companys Name and Address"
Selection.Offset(1, 0).FormulaR1C1 = "=NOW()"
With Selection.Resize(2, 1).Font
.Bold = True
.Name = "Arial Black"
.Size = 12
.Italic = True
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
End With
End Sub
[/pre]
 
Good day kaushik03


Your question has me thinking...with Huis code the cell range can be altered to fit the required cells but I was wondering about the code to fit his example that would work on any active cell or is it a matter of pasting these lines in

[pre]
Code: 
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
ActiveCell.Activate"
[/pre]
into the correct place in my example.


Next.....your last post set me thinking, what would the code be if you wanted to pick up the active worksheet formatting??
 
@bobhc


I am sorry...i could not understand the query properly and unnecessarily get you into the confusion..My apologies...


I got it now and saw Hui's last reply too..


Kaushik
 
Bobhc


If you want to read the selections format its similar

[pre]
Code: 
With Selection.Font
Variable 1= .Bold
Variable 2= .Name
Variable 3= .Size
Variable 4= .Italic
Variable 5= .Underline
Variable 6= .ThemeColor
End With

or 

With Selection.Font
MsgBox "Bold: " + CStr(.Bold)
MsgBox "Font Name: " + .Name
MsgBox "Font Size: " + CStr(.Size)
MsgBox "Font Italic: " + CStr(.Italic)
MsgBox "Font underline: " + CStr(.Underline)
MsgBox "Font Theme Color: " + CStr(.ThemeColor)
End With
[/pre]
 
Hui thanks for your time and patience.I have pasted in your bottom cosde and I get a compile error in that VBE says it expects a End With which is there along with End Sub

[pre]
Code: 
Sub NameandTimeShort4()
Range("A1") = "Companys Name and Address"
Range("A2").FormulaR1C1 = "=NOW()"
With Range("A1:A2")
With Selection.Font
MsgBox "Bold: " + CStr(.Bold)
MsgBox "Font Name: " + .Name
MsgBox "Font Size: " + CStr(.Size)
MsgBox "Font Italic: " + CStr(.Italic)
MsgBox "Font underline: " + CStr(.Underline)
MsgBox "Font Theme Color: " + CStr(.ThemeColor)
End With
End Sub
[/pre]

A Fool doing some thing wrong.....but I will keep one trying :)
 
Thats not quite what I posted


Firstly you opened a second With without closing it

Also you can only retrieve 1 cells values at a time without being more complex with Arrays

So try:

[pre]
Code: 
Sub NameandTimeShort4()
Range("A1") = "Companys Name and Address"
Range("A2").FormulaR1C1 = "=NOW()"
With Range("A1").Font
MsgBox "Bold: " + CStr(.Bold)
MsgBox "Font Name: " + .Name
MsgBox "Font Size: " + CStr(.Size)
MsgBox "Font Italic: " + CStr(.Italic)
MsgBox "Font underline: " + CStr(.Underline)
MsgBox "Font Theme Color: " + CStr(.ThemeColor)
End With
End Sub
[/pre]
 
I also wouldn't worry about the Fool bit


A Fool wouldn't ask or admit when he didn't know something

and a Fool wouldn't want to learn
 
Hui My thanks Some learning to do, but........"played" around with code I messed up and finished up with this, which seems to do what it should:)

[pre]
Code: 
Sub NameandTimeShort4()
Range("A1") = "Companys Name and Address"
Range("A2").FormulaR1C1 = "=NOW()"
With Range("A1:A2").Font
MsgBox "Bold: " + CStr(.Bold)
MsgBox "Font Name: " + .Name
MsgBox "Font Size: " + CStr(.Size)
MsgBox "Font Italic: " + CStr(.Italic)
MsgBox "Font underline: " + CStr(.Underline)
MsgBox "Font Theme Color: " + CStr(.ThemeColor)

End With
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top