Excel code stopped working

**Jollyfrog** · 09-28-2010, 05:38 AM

I have some code that takes a validation list and enters it into a combo box which allows the user to use a form of autocomplete and then outputs the results into the desired cell, it used to work, but for a reason i cannot find the code has stopped working.. when i try copy pasting into a new workbook, it fails again!

what have i messed up?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
'Dim wsList As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
'Set wsList = Sheets("ValidationLists")


If Target.count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

  On Error GoTo errhandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    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 + 15
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errhandler:
  Resume exitHandler

End Sub

**snb** · 09-28-2010, 05:54 AM

maybe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  on error goto XL90
  If Target.count > 1 Then Exit Sub
  application.screenupdating =False

  With OLEObjects("TempCombo")
    .Visible=false
    If Target.Validation.Type = 3 Then
       .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = mid(Target.Validation.Formula1,2)
      .LinkedCell = Target.Address
      .Visible=True
    End With
  End If

XL90:
  Application.ScreenUpdating = True
End Sub

If validationrules in the worksheet have been removed the code won't work. Start checking the worksheet.

**TMShucks** · 09-28-2010, 05:58 AM

Stopped working? What doesn't it do that it used to do? How long is it since it last worked? What's changed in the meantime? More data? Less data? New structure ... more or less columns? Additional worksheets? Updates to Windows, Office, Excel ... ?

One way to test it is to comment out the error handling and see where it breaks. You could also put a break point on the code (Set ws = ActiveSheet) and step through (using F8) from there.

For someone in the forum to provide assistance, it would be helpful if you uploaded a sample workbook with some typical, desensitized data.

Regards

**Jollyfrog** · 09-28-2010, 06:36 AM

hmm tried the suggested code... validation rules were active.. but no luck.. thanks for your sugestion!

**Jollyfrog** · 09-28-2010, 06:51 AM

Originally Posted by TMShucks

Stopped working? What doesn't it do that it used to do? How long is it since it last worked? What's changed in the meantime? More data? Less data? New structure ... more or less columns? Additional worksheets? Updates to Windows, Office, Excel ... ?

same sheet and versions as previous the last change i made was the addition of small amount of code that i subsequently removed, can't be an update as i have another version that works ok, i tried copying the code across but no change in function..
the current problem is that while it still imports the validation lists, when the combo box is altered the change is not carried through to the required cell!

One way to test it is to comment out the error handling and see where it breaks. You could also put a break point on the code (Set ws = ActiveSheet) and step through (using F8) from there.
i tried this also, and it errored on the line .LinkedCell = "" saying usupported
then locked up completely.. refusing to update.. i created a macro to set Application.EnableEvents = true.. this seemed to remedy all problems... odd, at least i knwo what to do if i mess it up in future.. i guess it managed to fail without going though error handler resulting in Application.EnableEvents = true being set.. not sure how though...

Thanks so much for your time, you been a great help.

Jonathan

**TMShucks** · 09-28-2010, 07:34 AM

You're welcome. Thanks for the feedback.

You can type Application.EnableEvents in the Immediate window ... saves creating a macro to do it.

I'm still unsure what's changed ... if the code used to work, why doesn't it now? What's different in the version that does work?

All academical perhaps ...

Regards