Macros Running Excel Out Of Memory

[Phil Hageman]

OS X Yosemite 10.10.5
Excel 2011 version 14.7.2 (170228) on MAC
Am running the below macros in module 4 as an end of month worksheet cleanup, and have been getting "Out of Memory" messages. How can this be fixed?

Sub ClearCheckbookEntries()
Range("V5:Y54,AB5:AE54").Select
Range("AB5").Activate
Selection.ClearContents
Selection.ClearComments
Range("U5").Select
End Sub


Sub ClearCheckBoxes()
ActiveSheet.CheckBoxes.Value = xlOff
Range("U5").Select
End Sub


Sub RestoreCheckbookBackgroundColor()
Range("U5:AF55").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.8
.PatternTintAndShade = 0
End With
Range("U5").Select
End Sub


Sub RestoreCheckbookBorders()
Range("U5:AF54").Select
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 44
End With
Range("U5").Select
End Sub

 

[Michael M]

which one causes the problem. They all look pretty straightforward,but i'd remove the selects
If anything I'd guess it's the clear checkboxes macro
Also, why not have the code all in one macro ??

Sub ClearCheckbookEntries()
Range("V5:Y54,AB5:AE54").ClearContents
Range("V5:Y54,AB5:AE54").ClearComments
End Sub


Sub ClearCheckBoxes()
ActiveSheet.CheckBoxes.Value = xlOff
End Sub


Sub RestoreCheckbookBackgroundColor()
With Range("U5:AF55").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.8
.PatternTintAndShade = 0
End With
End Sub


Sub RestoreCheckbookBorders()
With Range("U5:AF54").Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 44
End With
End Sub

 

[Phil Hageman]

Exactly how do I post the code?

 

[Phil Hageman]

Option Explicit

Sub ClearCheckbookEntries()
Range("V5:Y54,AB5:AE54").Select
Range("AB5").Activate
Selection.ClearContents
Selection.ClearComments
Range("U5").Select
End Sub

Sub ClearCheckBoxes()
ActiveSheet.CheckBoxes.Value = xlOff
Range("U5").Select
End Sub

Sub RestoreCheckbookBackgroundColor()
Range("U5:AF55").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.8
.PatternTintAndShade = 0
End With
Range("U5").Select
End Sub

Sub RestoreCheckbookBorders()
Range("U5:AF54").Select
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 44
End With
Range("U5").Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'Change text to upper case
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End Sub

Sub BalanceDblUnderline()
'INSERTS A BOTTOM-OF-CELL DOUBLE BORDER (CALLED UNDERLINE) TO SHOW AGREEMENT
'BETWEEN SPENDING BALANCE AND CHECKING ACCOUNT BALANCE.
With Selection
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893 '(Orange, Accent6, Darker 50%)
.Weight = xlThick
End With
End With
End Sub

Sub GoToVystarCreditUnion()
' Clicking button hyperlinks to Vystar Credit Union online.
ThisWorkbook.FollowHyperlink "https://www.vystarcu.org"
End Sub

Sub SortBudget()
Dim ActiveSheet As Object
Set ActiveSheet = ThisWorkbook.ActiveSheet
Range("B10:H54").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("B10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Sort Column B first
ActiveSheet.Sort.SortFields.Add Key:=Range("C10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Sort column C second
With ActiveSheet.Sort
.SetRange Range("B10:H54")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B46").Select
End Sub

Sub Posted()
'Change cell background color, indicating value posted
ActiveCell.Interior.Color = RGB(220, 220, 220)
End Sub

 

[Michael M]

Firstly, please use code tags when posting code .....see my title block for this
The worksheet change event does not have a set range for the target, so everytime something happens to that sheet, the change event will run...over and over again
So try something like this

Private Sub Worksheet_Change(ByVal Target As Range) 'Change text to upper case
if intersect(target, range("A1:Z100")) is nothing then exit sub
application.enableevents=false
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
application.enableevents=true
End Sub

But a better way would be to incorporate the Ucase request into one of the other macros and not have the change event !!