+ Reply to Thread
Results 1 to 2 of 2

Excel Crashes With Code ... PLEASE HELP!!!

  1. #1
    Registered User
    Join Date
    06-18-2014
    Location
    Scotland
    MS-Off Ver
    2010
    Posts
    19

    Angry Excel Crashes With Code ... PLEASE HELP!!!

    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!?

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Excel Crashes With Code ... PLEASE HELP!!!

    With almost 12k checkboxes it's not suprising it crashes.

    Instead of checkboxes try using special font and double click of cell to toggle responses.

    In the attached example run the macro to fill with empty boxes. then double click cells to update status.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel Crashes with my code!
    By Laingy24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2015, 12:57 PM
  2. Excel crashes when I change code
    By leinadw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2014, 08:21 AM
  3. My code crashes excel
    By ben_sumner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-06-2006, 03:20 AM
  4. Excel crashes when code runs, Please help.
    By Potoroo in forum Excel General
    Replies: 2
    Last Post: 09-12-2006, 08:39 PM
  5. VBA WinInet Code Crashes Excel
    By curt.lindner@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2006, 12:55 AM

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