Excel is crashing after VBA populates cells on sheet

[FrankSmith]

Hello all, for a while now I've been reading answers to various VBA / Excel issues on here, but finally I've encountered an issue where I am unable to figure it out and/or find information that might help in that task, so here I am humbly requesting your insight.

I am working on an excel workbook (using Excel 2007 on Windows XP) that is intended to populate some sheets in that book based on some user inputs. On the first sheet, I added a button that opens up a form I've created. It has a bunch of various types of controls (some textboxes, radio buttons, checkboxes, combo boxes, etc), some of which (the combo boxes) are populated using an access database (though I don't think the access part has anything to do with it because I was having this problem before I tried anything with that). After filling in info on the form, the user clicks the Generate button and the VBA function sets the value of specific cells on a different sheet to the value of the corresponding control. This generation code is below:

Private Sub Btn_Generate_Click()'Populates all the necessary cells in the workbook with form data
    
    'Turn Team Ticket on or off
    If Chk_TeamTicket.Value = False Then
    
        'Rush Ticket style
        Sheets("CUTTCK").Rows(14).Hidden = True
        Sheets("CUTTCK").Rows(15).Hidden = True
        Sheets("CUTTCK").Rows(16).Hidden = False
        Sheets("CUTTCK").Rows(17).Hidden = False
        
    Else
    
        'Team Ticket style
        Sheets("CUTTCK").Rows(14).Hidden = False
        Sheets("CUTTCK").Rows(15).Hidden = False
        Sheets("CUTTCK").Rows(16).Hidden = True
        Sheets("CUTTCK").Rows(17).Hidden = True
        
    End If
    
    'Issue Date
    Sheets("CUTTCK").Range("F3").Value = Date
    
    'Time
    Sheets("CUTTCK").Range("F4").Value = Time


    'Cut Number
    Sheets("CUTTCK").Range("AA1").Value = Txt_Cut.Value
    
    'PO #
    Sheets("CUTTCK").Range("V8").Value = Txt_PO.Value
    
    'Team
    Sheets("CUTTCK").Range("V3").Value = Cmb_OrderTeam.Value
    
    'Caller
    Sheets("CUTTCK").Range("V4").Value = Txt_Caller.Value
    
    'On Field vs Personal
    Sheets("CUTTCK").Range("V10").Value = ""
    Sheets("CUTTCK").Range("AC10").Value = ""
    
    If Opt_OnField.Value = True Then
        Sheets("CUTTCK").Range("V10").Value = "X"
    ElseIf Opt_Personal.Value = True Then
        Sheets("CUTTCK").Range("AC10").Value = "X"
    End If
    
    'Ship By date
    Sheets("CUTTCK").Range("V6").Value = Txt_ShipBy.Value
    
    'In Hands date
    Sheets("CUTTCK").Range("V7").Value = Txt_InHands.Value
    
    'Ship To address
    Sheets("CUTTCK").Range("F6").Value = Txt_ShipTo1.Value
    Sheets("CUTTCK").Range("F7").Value = Txt_ShipTo2.Value
    Sheets("CUTTCK").Range("F8").Value = Txt_ShipTo3.Value
    Sheets("CUTTCK").Range("F9").Value = Txt_ShipTo4.Value
    Sheets("CUTTCK").Range("F10").Value = Txt_ShipTo5.Value
    
    'Sold To address
    ' Sheets("CUTTCK").Range("F6").Value = Txt_ShipTo1.Value
    ' Sheets("CUTTCK").Range("F7").Value = Txt_ShipTo2.Value
    ' Sheets("CUTTCK").Range("F8").Value = Txt_ShipTo3.Value
    ' Sheets("CUTTCK").Range("F9").Value = Txt_ShipTo4.Value
    ' Sheets("CUTTCK").Range("F10").Value = Txt_ShipTo5.Value


End Sub

The sheets in the workbook are locked with a password. On the Workbook_Open event, I have some code that unlocks each sheet and then relocks just the UI part (so that the sheets are still protected from user editing but my code can modify the cells as needed). This reads as such:

Private Sub Workbook_Open()'Unprotect all sheets in the workbook, then protect them from the user interface but not vba changes
    
    Dim wSheet As Worksheet
    
    For Each wSheet In Worksheets
        wSheet.Unprotect ("pass")
        wSheet.Protect Password:="pass", UserInterfaceOnly:=True
    Next
    
End Sub

The problem is, sometimes (this only seems to happen randomly) after generating the data with the form button, when I try to click on the CUTTCK sheet to verify the data was set up correctly, Excel just immediately crashes with an "Excel has encountered a problem and needs to close" error. The error appears like so:

I'm not sure what triggers this error; like I said it doesn't occur every time and I can't seem to find any distinct set of actions to produce it intentionally. When it does happen it's only after I click the Generate button and subsequently view the CUTTCK sheet. I don't know if it has something to do with the data I'm entering into the form controls, something going wrong with locking/unlocking cells, or maybe something else entirely. Does anyone have an idea what I should be looking for? (If more information is required I'll post whatever you need.)

Thank you for taking the time to read this, your help is appreciated.

 

[aaalviny]

In my experience, this happens only when the spreadsheet is very complicated. (How complicated it is in your workbook?)

or you may try to check if there are any invalid defined names or objects, and clear them.
From the code you posted, I think it's simple and it isn't the root cause.

I will suggest you to open a new workbook and re-do everything from scratch.

Note: I only use Excel 2003, maybe other users could help you

 

[FrankSmith]

The spreadsheet isn't particularly complicated. None of the sheets have any formulas on them; they're just blank formatted sheets designed to have data added to them so they can be printed out. The CUTTCK sheet has a grid size such that it is 30 cells wide and 45 cells long (they're basically squares to make it easier to format), and different groups of them are merged together to form the "fields" that get populated. The sheet itself has no conditional formatting, no formulas, and no code associated with it aside from the population that the form does.

 

[FrankSmith]

Bumping as I still am looking for help with this. Thanks!

 

[FrankSmith]

Update - I've disabled the sheet's protection and yet I'm still having issues with the CUTTCK sheet randomly crashing the workbook. I am just about out of ideas as to what could be causing this.

 

[FrankSmith]

I'm still having this issue. After populating the sheet, my form closes, and when I try to open up the other sheet, Excel sometimes just crashes. Interestingly enough, as I've mentioned, when Excel auto-recovers the file, the data I populated is still saved on that sheet. I just can't think what would cause such an error.