I know I am asking excel to do a huge task here which I think is why it is crashing. Is there a better way around this other than causing myself serious amounts of stress??
Here is the code that I am trying to use and is making excel crash:
Sub Add_Checkboxes_Across()
Dim myCBX As CheckBox
Dim myCell As Range
Dim RAN As Range
'Sheets.Add 'for testing
Set RAN = ActiveSheet.Range("F5:DW100")
Application.ScreenUpdating = False
With ActiveSheet
.CheckBoxes.Delete
For Each myCell In RAN
With myCell
.RowHeight = 24
.Offset(0, 0).RowHeight = 24
.Offset(0, 0).VerticalAlignment = xlDistributed
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).RowHeight = 24
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Width:=12, _
Left:=.Left + ((.Width - 12) / 2), _
Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 0).Address(external:=True)
.Caption = ""
.Value = xlOff
End With
End With
Next myCell
With Range("F5:DW100")
.HorizontalAlignment = xlCenter
End With
End With
Application.ScreenUpdating = True
End Sub
I have also uploaded sample sheet that is identical to the one I am using.
Example File.xlsx
I really hope someone can help!?
Bookmarks