Isbold Function

[rach_oh]

I have the IsBold function as a module to use a formula in my workbook. Everytime i run another sub, the formula that uses the function throws an error. Is there any work around for this?

IsBold function:

Function IsBold(BoldRange As Range)
IsBold = BoldRange.Font.Bold
Application.Calculation = xlCalculationAutomatic
End Function

Other Subs that run and throw off IsBold function

Private Sub CommandButton1_Click()
LineStart = 6
LineEnd = 217
ColumnNumber = 2
For i = LineStart To LineEnd
If Cells(i, ColumnNumber).Value <> "Yes" Then
Cells(i, ColumnNumber).EntireRow.Hidden = True
Else
Cells(i, ColumnNumber).EntireRow.Hidden = False
End If
Next i
End Sub

Private Sub CommandButton2_Click()
Dim tbl As ListObject
Set tbl = Worksheets("Dynamics Data Load").ListObjects("Table1")
With tbl.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
End With
tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel

Add this line to the end of your macros:

Application.ScreenUpdating = True

Private Sub CommandButton1_Click()
  LineStart = 6
  LineEnd = 217
  ColumnNumber = 2
  For i = LineStart To LineEnd
    If Cells(i, ColumnNumber).Value <> "Yes" Then
      Cells(i, ColumnNumber).EntireRow.Hidden = True
    Else
      Cells(i, ColumnNumber).EntireRow.Hidden = False
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
Dim tbl As ListObject
Set tbl = Worksheets("Dynamics Data Load").ListObjects("Table1")
With tbl.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
End With
tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
Application.ScreenUpdating = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DanteAmor]

It works better this way:

Function IsBold(BoldRange As Range)
  Application.Volatile
  IsBold = BoldRange.Font.Bold
End Function

Private Sub CommandButton1_Click()
  Application.ScreenUpdating = False
  LineStart = 6
  LineEnd = 217
  ColumnNumber = 2
  For i = LineStart To LineEnd
    If Cells(i, ColumnNumber).Value <> "Yes" Then
      Cells(i, ColumnNumber).EntireRow.Hidden = True
    Else
      Cells(i, ColumnNumber).EntireRow.Hidden = False
    End If
  Next i
  Application.ScreenUpdating = True
  Application.Calculate
End Sub

Private Sub CommandButton2_Click()
  Dim tbl As ListObject
 
  Application.ScreenUpdating = False
  Set tbl = Worksheets("Dynamics Data Load").ListObjects("Table1")
  With tbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
  tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
  Application.ScreenUpdating = True
  Application.Calculate
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[rach_oh]

Private Sub ToggleButton4_Click()
Dim rng As Range

Set rng = Range("N:O,U:V,AB:AC,AI:AJ,AP:AQ,AW:AX," & _
"BD:BE,BK:BL,BR:BS,BY:BZ,CF:CG,CM:CN,CT:CU")

With Me.ToggleButton4
rng.EntireColumn.Hidden = .Value
.Caption = IIf(.Value, "Show 2023 Actuals", "Hide 2023 Acutals")
End With
End Sub

Also have this sub, where should the "Application" line be added in the beginning?

 

[DanteAmor]

At the beginning and at the end:

Private Sub ToggleButton4_Click()
  Dim rng As Range
  
  Application.ScreenUpdating = False
  Set rng = Range("N:O,U:V,AB:AC,AI:AJ,AP:AQ,AW:AX," & _
    "BD:BE,BK:BL,BR:BS,BY:BZ,CF:CG,CM:CN,CT:CU")
  
  With Me.ToggleButton4
    rng.EntireColumn.Hidden = .Value
    .Caption = IIf(.Value, "Show 2023 Actuals", "Hide 2023 Acutals")
  End With
  Application.ScreenUpdating = True
  Application.Calculate
End Sub