Results 1 to 6 of 6

Excel code stopped working

Threaded View

  1. #1
    Registered User
    Join Date
    09-23-2010
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    97

    Excel code stopped working

    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
    Last edited by Jollyfrog; 09-28-2010 at 06:51 AM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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