none
Application-defined Or Object-defined Error RRS feed

  • Question

  • Dear Sir:

    I made the following program:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rng1 As Range, rng2 As Range
        Dim sht As Worksheet
        Set sht = ThisWorkbook.Sheets(1)
        Set rng1 = sht.Range("A2:A" & sht.UsedRange.Rows.Count)
       
        If Target.Column = 1 Or Target.Column = 2 Then
            For Each rng2 In Target
                With rng2.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=rng1               
                End With
            Next rng2
        End If
    End Sub

    When I ran it, it showed "Application-defined or object-defined error". When I debugged it, it highlighted the line ".Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=rng1". Would you please help me find out where the problem is and how to fix it? Your support is highly appreciated!

    Regards,

    Shell Hu

    Thursday, December 8, 2016 7:24 AM

Answers

  • Hi Shell Hu,

    I test your code and the code provided by Vishwamitra Mishra.

    the correction done by Vishwamitra Mishra in your code can solve your issue.

    you just need to change the following part in your code.

    Formula1:="=" & rng1.Address

    rest of the code will be same.

    after revising the code you had mentioned that there is no value in the list.

    so make sure that you modified the code correctly and rng1 contains the values that you had entered column A. so also check that Column A contains the value.

    you can refer the sample file provided by Vishwamitra Mishra and correct your mistakes.

    if then also you have some issue then let us know about that we will try to give you further suggestions.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Shell Hu Monday, December 12, 2016 3:51 AM
    Friday, December 9, 2016 12:42 AM
    Moderator

All replies

  • Hi,

    You were trying to pass a Range - rng1 in to a formula. 


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rng1 As Range, rng2 As Range
        Dim sht As Worksheet
        Set sht = ThisWorkbook.Sheets(1)
        Set rng1 = sht.Range("A2:A" & sht.UsedRange.Rows.Count)
        
        If Target.Column = 1 Or Target.Column = 2 Then
            For Each rng2 In Target
                With rng2.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & rng1.Address
                End With
            Next rng2
        End If
    End Sub


    Vish Mishra

    Thursday, December 8, 2016 8:03 AM
  • Dear Sir:

    I've revised the program as you instructed. There isn't any error shown again. But there isn't any list shown when I am running it. Would you please help me see which is the cause?

    Regards,

    Shell Hu

    Thursday, December 8, 2016 8:31 AM
  • Hi,

    You can refer this attached excel : https://1drv.ms/x/s!AlawUlaW5DROgSPjv9p9HARckUQq

    If this is not your expectation then explain your expectation and I can provide you the code.



    Vish Mishra

    Thursday, December 8, 2016 8:36 AM
  • Hi Shell Hu,

    I test your code and the code provided by Vishwamitra Mishra.

    the correction done by Vishwamitra Mishra in your code can solve your issue.

    you just need to change the following part in your code.

    Formula1:="=" & rng1.Address

    rest of the code will be same.

    after revising the code you had mentioned that there is no value in the list.

    so make sure that you modified the code correctly and rng1 contains the values that you had entered column A. so also check that Column A contains the value.

    you can refer the sample file provided by Vishwamitra Mishra and correct your mistakes.

    if then also you have some issue then let us know about that we will try to give you further suggestions.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Shell Hu Monday, December 12, 2016 3:51 AM
    Friday, December 9, 2016 12:42 AM
    Moderator