Why doesn't this code change Font from Bold to non-Bold

[Mackeral]

Here is the code

Function Cell_Font(Sheet_Spec, ByVal Rng_Arg, _
                     Optional Bold_Test As Boolean = False, _
                     Optional Italic_Test As Boolean = False, _
                     Optional Font_Size As Integer = 0, _
                     Optional Font_Name = "", _
                     Optional Underline_Style As XlUnderlineStyle = xlUnderlineStyleNone)
    ' 10/6/15 Put in All_Rng_Arg call. WML
    ' 9/2/19 Added ""Name" to argument list. WML
    ' 6/1/20 Changed name from "Cell_Font" and changed args around. WML
    ' 8/31/20 WML Reworked. WML
    
    ' Note: If the Font Name is illegal, Excel will revert to using the current font.
      
    Dim SHEET As Worksheet
    
    Prog = "Cell_Font"
    
    Call Wait(0, 0, 0.5)
    Call Sheet_Arg(Sheet_Spec, SHEET, Sheet_Name)
    
    If InStr(Rng_Arg, ":") = 0 Then Rng_Arg = Rng_Arg & ":" & Rng_Arg
    
    With SHEET
        Range(Rng_Arg).Select
            
        Selection.Font.Bold = Bold_Test   '  <-- Bold_Test is set to False and the cell to be operated on is showing in Bold.
        Selection.Font.Italic = Italic_Test
        If Len(Font_Name) Then Selection.Font.Name = Font_Name
        If FontSize > 0 Then Selection.Font_Size = Font_Size
        
        Selection.Font.Underline = Underline_Style
        
    End With
    
End Function ' Cell_Font()

When it gets called inside a Program, it doesn't change the indicated cells from Bold to non_Bold.
If I stop the code on the call and then execute it, it does.
Can anyone tell me what's gong on?

Thanks, Mac

 

[RoryA]

Your With block is currently not doing anything. Also, there is no need to select the range - you can simply manipulate it directly:

With SHEET.Range(Rng_Arg)

.Font.Bold = Bold_Test ' <-- Bold_Test is set to False and the cell to be operated on is showing in Bold.
.Font.Italic = Italic_Test
If Len(Font_Name) Then .Font.Name = Font_Name
If FontSize > 0 Then .Font_Size = Font_Size

.Font.Underline = Underline_Style

End With

​

 

[Mackeral]

Forgot to add the call used as

Call Cell_Format(A_WorkSheet, 3,False)

It's a big system, and this proceedure gets called from many places and is used in other programs.

 

[Mackeral]

If the block is the problem, why does it work when I execute the individual call line?

 

[Mackeral]

Re the Block: You are fight. Apparently it only works on the ActiveSceeen like Freeze does.

Here is the updated code:

Function Cell_Font(SHEET as Worksheet, ByVal Rng_Arg, _
                     Optional Bold_Test As Boolean = False, _
                     Optional Italic_Test As Boolean = False, _
                     Optional Font_Size As Integer = 0, _
                     Optional Font_Name = "", _
                     Optional Underline_Style As XlUnderlineStyle = xlUnderlineStyleNone)
    ' 10/6/15 Put in All_Rng_Arg call. WML
    ' 9/2/19 Added ""Name" to argument list. WML
    ' 6/1/20 Changed name from "Cell_Font" and changed args around. WML
    ' 8/31/20 WML Reworked. WML
    
    ' Note: If the Font Name is illegal, Excel will revert to using the current font.
      
    Dim SHEET As Worksheet
    
    Prog = "Cell_Font"
        
    If InStr(Rng_Arg, ":") = 0 Then Rng_Arg = Rng_Arg & ":" & Rng_Arg
    
    ORIG_SHEET = ActiveSheet.Name
    SHEET.Activate
    
    Range(Rng_Arg).Select
        
    Selection.Font.Bold = Bold_Test
    Selection.Font.Italic = Italic_Test
    If Len(Font_Name) Then Selection.Font.Name = Font_Name
    If FontSize > 0 Then Selection.Font_Size = Font_Size
    
    Selection.Font.Underline = Underline_Style       
    
    Sheets(ORIG_SHEET).Activate
    
End Function ' Cell_Font()

 

[RoryA]

Or just use the code I posted, which is more efficient.