+ Reply to Thread
Results 1 to 5 of 5

Run time error 1004 - macro to change font

  1. #1
    Registered User
    Join Date
    01-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    7

    Thumbs up Run time error 1004 - macro to change font

    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.
    Last edited by mimichan; 01-17-2012 at 01:28 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Run time error 1004 - macro to change font

    Use this

    Please Login or Register  to view this content.
    Regards,
    Khaled Elshaer
    www.BIMcentre.com

    Remember To Do the Following....
    1. Thank those who have helped you by clicking the Star below their post.
    2. Mark your post SOLVED if it has been answered satisfactorily:
    • Select Thread Tools (on top of your 1st post)
    • Select Mark this thread as Solved

  3. #3
    Registered User
    Join Date
    01-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    7

    Unhappy Re: Run time error 1004 - macro to change font

    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:

    Please Login or Register  to view this content.
    Again I don't know what the problem is.
    Could you please help me with this also?

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Run time error 1004 - macro to change font

    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.

  5. #5
    Registered User
    Join Date
    01-16-2012
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Run time error 1004 - macro to change font

    I will. Thanks again, Kelshaer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1