Excel Crashes on Worksheet with VB Code

[DMurray3]

Hi All,

I can´t find the reason for continous crashes in my Excel 2013 spreadsheet.

I am buidling Data Entry System for my wife's business; in one of the multiple worksheets we are using, I included the following VB Code:

Option Explicit
'*****WORKING


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
'*** This code allows to auto-complete combo boxes, specifically those that are to appear in 
'*** range J14:J63 of the worksheet
'*** taken from Contextures.com
Dim str As String
Dim cboTemp As OLEObject
Dim WS As Worksheet
Set WS = ActiveSheet


Set cboTemp = WS.OLEObjects("TempCombo9")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errhandler


'*****To Define the cells where the DV w/CBx will be applied*****
If Not Intersect(Target, Range("J14:J63")) Is Nothing Then
      If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Cancel = True
        Application.EnableEvents = False
        'get the data validation formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 5
          .Height = Target.Height + 5
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        'open the drop down list automatically
        Me.TempCombo9.DropDown
      End If
End If
  
errhandler:
  Application.EnableEvents = True
  Exit Sub


End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'*** This code allows to auto-complete combo boxes, specifically those that are to appear in 
'*** range J14:J63 of the worksheet
'*** taken from Contextures.com
Dim str As String
Dim cboTemp As OLEObject
Dim WS As Worksheet
Set WS = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True


If Application.CutCopyMode Then
  'allow copying and pasting on the worksheet
  GoTo errhandler
End If


Set cboTemp = WS.OLEObjects("TempCombo9")
  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With


errhandler:
  Application.EnableEvents = True
  Exit Sub


End Sub


'*** Optional code to move to next cell if Tab or Enter are pressed
'*** from code by Ted Lanham and Contextures.com
'***  **NOTE: if KeyDown causes problems, change to KeyUp


Private Sub TempCombo9_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub


Private Sub S1_SpinUp()
'*** SpinUp/Dwn code taken from Trevor Easton at Online-PC-Learning.com
Dim i
On Error GoTo errhandler:
    'Unprotect_One
For i = 50 To 14 Step -1
    If Cells(i, 1).EntireRow.Hidden = False Then
    Cells(i, 1).EntireRow.Hidden = True
    'Protect_one
    Exit Sub
End If
Next
    If Range("a14") = ActiveCell Then MsgBox "Este es el Fin del rango de filas disponibles"
    'Protect_one
    Exit Sub


errhandler:
    'Protect_one
    MsgBox "There appears to be an error please contact your administrator"
End Sub


Private Sub S1_SpinDown()
'*** SpinUp/Dwn code taken from Trevor Easton at Online-PC-Learning.com
Dim i
On Error GoTo errhandler:
    'Unprotect_One
For i = 14 To 50
    If Cells(i, 1).EntireRow.Hidden = True Then
    Cells(i, 1).EntireRow.Hidden = False
        'Protect_one
    Exit Sub
End If
Next
    If Range("a50") = ActiveCell Then MsgBox "Este es el Fin del rango de filas disponibles"
    'Protect_one
    Exit Sub


errhandler:
    'Protect_one
    MsgBox "There appears to be an error please contact your administrator"
End Sub

Data Validation (DV) Drop Down is working correctly in all the cells with this DV; but when I try using the "auto/complete" function in any of these cells, Excel simply crashes.

Similar "auto-complete" code is used in other worksheets of the workbook with no problem (without the SpinUp/Dwn" code..). Crashing only ocurrs on the whorksheet that has the above specified code.

If useful, I am using MS Excel 2013 on W8.1 64 bit.

I´d appreciate advising if my code has errors and/or the best way to identify the reason for the crashing.

Many thanks for your interest & support.

Rds,
Daniel Murray
DMurray3

 

[mrmmickle1]

A few questions to help with your inquiry:

1. Do you get an error message?
2. If so what does the message say?
3. Have you tried stepping through the code one line at a time using F8
4. Have you tried putting Debug.Print Statements into the code to identify where the code is getting hung up?
5. Do the error handlers you have in place work properly?

 

[DMurray3]

Hi All...

After trying to resolve the reported exception/problem, I realised that on the Contextures auto-complete ComboBox solution, there was a warning mentioning that Execl had a bug -from some ago- that when data validation or dynamic named ranges referenced within the DV use data in structured tables, the worksheets containg said structured tables must be kept at 100% zoom.

I reset the zoom on the worksheet containing my structured table back to 100%, and I no longer am getting crashes when accessing / doulble-click my "auto-complete" combo-boxes.

Sorry if I posted before thoroughly looking into the matter. In any event, I trust this case helps anyone else.

Case closed.

Kind regards.

 

[mrmmickle1]

Thanks for following up with the additional information. I'm glad you got your issue sorted. I will keep an eye out for this issue.