Click here to Skip to main content
15,921,279 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have write a code that create dynamic drop-down list without any blank
and it works very good, but the only problem is when I run the marco while I am clicking any object (pictures, buttons, etc..) it shows Run-Time error '1004' Application-defined or object-defined error

I have use the debugger in VBA and it shows the error in these two
VB
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=Join(Application.Transpose(valuesArray), ",")

And this is the whole code, please help
VB
Sub DynamicDropDown()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim cell As Range
    Dim validationFormula As String
    Dim nonBlankValues As Collection
    Dim dropDownCell As Range
    
    ' Set source and destination sheets
    Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
    Set destinationSheet = ThisWorkbook.Sheets("Sheet2")
    
    ' Define the source range (adjust the range as needed)
    Set sourceRange = sourceSheet.Range("A1:A" & sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row)
    
    ' Define the destination range where you want the drop-down list
    Set destinationRange = destinationSheet.Range("N10")
    
    ' Create a collection to store non-blank values
    Set nonBlankValues = New Collection
    
    ' Collect non-blank values from the source range
    On Error Resume Next
    For Each cell In sourceRange
        If cell.Value <> "" Then
            nonBlankValues.Add cell.Value, CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0
    
    ' Convert the collection to an array
    Dim valuesArray() As Variant
    ReDim valuesArray(1 To nonBlankValues.Count, 1 To 1)
    
    Dim i As Integer
    For i = 1 To nonBlankValues.Count
        valuesArray(i, 1) = nonBlankValues(i)
    Next i
    
    ' Set the validation formula
    destinationRange.Validation.Delete
    With destinationRange.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=Join(Application.Transpose(valuesArray), ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
  
End Sub


What I have tried:

I have tried everything that I know but nothing works
Posted
Updated 22-Jan-24 21:37pm
v5
Comments
Dave Kreskowiak 22-Jan-24 13:04pm    
There is no thinking allowed. You either know which line is throwing the error or you have to use the debugger in VBA to find out. Guessing only gets you lost and wasting time.
Faisal faisal from Vsevolozhsk 22-Jan-24 13:09pm    
I have use the debugger and it shows the error in these two:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(Application.Transpose(valuesArray), ",")
KarstenK 23-Jan-24 3:38am    
remember basic coding rules: only one statement a line. As you see it helps to see the bug.
Faisal faisal from Vsevolozhsk 23-Jan-24 4:08am    
I try everything:(, but no solution unit now
have notice that the code is working very well when I clicking only in cells but if I have clicked on pictures, buttons, any object (non cell) and then run the code it will shows this error.

Try to split this line:
VBA
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
     xlBetween, Formula1:=Join(Application.Transpose(valuesArray), ",")

into:
VBA
Dim result As String
result  = Join(Application.Transpose(valuesArray), ",")
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
     xlBetween, Formula1:=result 
 
Share this answer
 
Comments
Faisal faisal from Vsevolozhsk 23-Jan-24 4:07am    
I try this but the same problem happen, I have notice that the code is working very well when I clicking only in cells but if I have clicked on pictures, buttons, any object (non cell) and then run the code it will shows this error.
What is calling this DynamicDropDown method? Whatever that is needs to check to see what is being clicked on is an object that is valid for the call to DynamicDropDown to succeed.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900