none
Code for Inserting Data Validation List RRS feed

  • Question

  • I am trying to create a small Module that always runs in the background. All it does is if there is data in cell G11, then it will insert a Data Validation list in cell F11 with two options: Auto,Manual. I am testing the data validation part, but it doesn't seem to work. It keeps giving me Error 1004. I don't want the user to have to run this macro, I'd like it to be there so if at any point data is entered in G11, that the list will appear in F11. Here is my code:

    Sub Toggle()
    Worksheets("Sheet1").Activate
    
    Worksheets("Sheet1").Range("F11").Validation.Add Type:=xlValidateList, Formula1:="Auto,Manual"
    End Sub

    Thanks!
    Tuesday, July 9, 2019 7:06 PM

Answers

  • Right-click the sheet tab of Sheet1.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("G11"), Target) Is Nothing Then
            If Range("G11").Value = "" Then
                Range("F11").Validation.Delete
            Else
                Range("F11").Validation.Add Type:=xlValidateList, Formula1:="Auto,Manual"
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Gradient127 Friday, July 12, 2019 1:53 PM
    Tuesday, July 9, 2019 9:03 PM
  • You code should insert a data validation in cell F11.

    And if you get an error, there may be one of these possibilities....

    1) Sheet1 is protected.

    2) You will get an error if F11 already has a data validation in it and you try to insert it again without deleting it first.

    If Sheet1 is protected, you must unprotect it first, insert the data validation and protect it again.

    But in both the cases, you must first delete data validation if it exists in F11 before inserting it.

    You may try something like this...

    Sub Toggle()
    Worksheets("Sheet1").Activate
    
    With Worksheets("Sheet1").Range("F11").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="Auto,Manual"
    End With
    End Sub


    Subodh Tiwari (Neeraj) sktneer


    Thursday, July 11, 2019 12:43 PM

All replies

  • Right-click the sheet tab of Sheet1.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("G11"), Target) Is Nothing Then
            If Range("G11").Value = "" Then
                Range("F11").Validation.Delete
            Else
                Range("F11").Validation.Add Type:=xlValidateList, Formula1:="Auto,Manual"
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Gradient127 Friday, July 12, 2019 1:53 PM
    Tuesday, July 9, 2019 9:03 PM
  • You code should insert a data validation in cell F11.

    And if you get an error, there may be one of these possibilities....

    1) Sheet1 is protected.

    2) You will get an error if F11 already has a data validation in it and you try to insert it again without deleting it first.

    If Sheet1 is protected, you must unprotect it first, insert the data validation and protect it again.

    But in both the cases, you must first delete data validation if it exists in F11 before inserting it.

    You may try something like this...

    Sub Toggle()
    Worksheets("Sheet1").Activate
    
    With Worksheets("Sheet1").Range("F11").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="Auto,Manual"
    End With
    End Sub


    Subodh Tiwari (Neeraj) sktneer


    Thursday, July 11, 2019 12:43 PM