Results 1 to 4 of 4

Thread: Drop down list

  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The following macro is held within a worksheet. The macro worked quite happily for some days. But now whenever I select a relevant cell it fails at the .ignoreblank or the .incelldropdown = true lines

    Ideas on why it is failing and how to fix it would be welcomed

    Thanks

    Alex


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'To create the supplier selection dropdown
    Dim SupplierStr As String
    Dim i As Integer
    On Error GoTo ExitHere
    Application.EnableEvents = False
    CurrentSupplierRow = ActiveCell.Row
    If ((Not Intersect(ActiveCell, Range("M1:M500")) Is Nothing) Or (Not Intersect(ActiveCell, Range("AG1:AG500")) Is Nothing)) And _
    Cells(ActiveCell.Row, 1) = 1 Then

    SupplierStr = ""
    For i = 2 To 200
    If Me.Cells(i, 79).Value <> "" Then SupplierStr = SupplierStr & Me.Cells(i, 79).Value & ","
    Next i
    SupplierStr = Left(SupplierStr, Len(SupplierStr) - 1)
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=SupplierStr
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    End If
    ExitHere:
    Application.EnableEvents = True
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maybe your list has become too long?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Even if it works, note that the code will only ever add validation to columns M and AG (if there's a 1 in column A) but never remove it (even if the 1 in row A is deleted).

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777507' date='29-May-2009 22:35']Even if it works, note that the code will only ever add validation to columns M and AG (if there's a 1 in column A) but never remove it (even if the 1 in row A is deleted).[/quote]

    Thanks for the advise. I traced the error to a sheet protection issue. Columns M and AG are normally protected. Adding Me.unprotect xxxx before the with statement and then Me.protect xxxx after the end with statement seems to have solved the problem

    The budget sheet is in a standard format. Once a row is designated as a data collection row, by a 1 in column A, it should remain designated as such for this budget round; well at least that is the theory :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •