Run time error 1004 - macro to change font

**mimichan** · 01-16-2012, 12:28 PM

I am a VBA beginner and need some help.

This is an input form using Template Wizard to store the data in the database sheet.
Half of the users of this form have Excel 2010 and half have 2003.
Because of that environment, "WordArt" and "text box" are not showing the same (not compatible between those versions).

What I like to do is change the font/size when a certain word is selected for the header.
Since Conditional Format won't change the font, I had to create a macro.

Private Sub Worksheet_Calculate()

If Range("K19").Value = "WANTED" Then
Range("A8:H8").Select
With Selection.Font
.Name = "Arial Black"
.Size = 85
.Bold = True
End With

Else
Range("A8:H8").Select <-- error: highlight here & opens the database sheet
With Selection.Font
.Name = "Impact"
.Size = 80
.Bold = False
End With
End If
End Sub

This macro works fine to change the font but when I try to save the file, I get a message:
"Run time error 1004 - Unable to set the name property of the font class" and the line after "Else"
will be highlighted and the database sheet will be opened.

I don't know what the problem is.
Please someone help me with this.

**Kelshaer** · 01-16-2012, 03:51 PM

Use this

Private Sub Worksheet_Calculate()
If Range("K19").Value = "WANTED" Then
    With Range("A8:H8").Font
        .Name = "Arial Black"
        .Size = 85
        .Bold = True
    End With
Else
    With Range("A8:H8").Font
        .Name = "Impact"
        .Size = 80
        .Bold = False
    End With
 End If
 End Sub

**mimichan** · 01-17-2012, 12:27 PM

Thank you very much Kelshaer for your reply. This problem was solved.

Now I get another problem saving.
The file can be saved fine but the message won't show after saving:

Sub SAVE_XLS_FORMAT()
'Working in Excel 97-2010
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook
    Set Destwb = ActiveWorkbook
    
    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2010
            FileExtStr = ".xls": FileFormatNum = 56
            
            'Disable compatibility check
            Destwb.CheckCompatibility = False
        End If
    End With

    'Run Template Wizard Add-in to write record upon saving
    Application.Run "wztemplt.xla!commit"
    
    'Save the new workbook and close it
    TempFilePath = "\\usershare\2012 XLS\"
    TempFileName = Format(Range("E17"), "000") & "_" & Replace(Range("B19"), " ", "_")

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        .Close SaveChanges:=False
    End With
    
    MsgBox TempFileName & " has been saved in '2012 XLS' folder"

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Again I don't know what the problem is.
Could you please help me with this also?

**Kelshaer** · 01-17-2012, 12:38 PM

Hi Mimichan.
Please mark this thread as solved and create a new one for your other problem.
Please try to describe more on what are you trying to do with the other code and what is the problem exactly.