VBA Code for Data Validation on Another Sheet

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
298
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for your assistance. How can I fix the Run-Time error and get the code to execute properly and also prevent the rows from getting shifted up when I delete the old data validation.

I would like to set a name range and then create a data validation. I would like to this macro to execute:
(1) without having to activate any Worksheets
(2) check if the name range exists and delete if and make a new one (I don't think I put code in to delete if it exists)
(3) check if the data validation exists and if so delete it and make a new one

The issue becomes with the following where I get an error "Run-time error '438': Object doesn't support this property or method"
VBA Code:
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"

VBA Code:
Option Explicit

Public Sub Get_Rng_Nm()

    'Dimensioning
        Dim LastRow As Long
        Dim RngB As Range
        Dim WS_Src As Worksheet
        Dim WS_Dest As Worksheet
      

    'Set Sheets
        Set WS_Src = Sheets("Our.Summary")
        Set WS_Dest = Sheets("Name.Ranges")


    'Code
        With Sheets("Our.Summary")
            LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set RngB = .Range("B9:B" & LastRow)
            RngB.Copy Destination:=WS_Dest.Range("B9")
            Range("B9").Select
        End With
  
      
        With Sheets("Name.Ranges")
            LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set RngB = .Range("B9:B" & LastRow)
            Range("B9").Select
        End With

      
        ThisWorkbook.Names.Add Name:="Tickers.Nm.Rng", RefersTo:=RngB
      
    'Data Validation
  
        With Sheets("Our.Summary")
            With Range("B7")
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="=Tickers.Nm.Rng"
                .Range("B7").Interior.ColorIndex = 37
            End With
        End With
      
      

End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Instead of this:
VBA Code:
    With Sheets("Our.Summary")
        With Range("B7")
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"
            .Range("B7").Interior.ColorIndex = 37
        End With
    End Wit

Try this:
VBA Code:
    With Sheets("Our.Summary")
        With Range("B7")
            With .Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"
            End With
            .Range("B7").Interior.ColorIndex = 37
        End With
    End With
 

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
298
Office Version
  1. 2019
Platform
  1. Windows
@rlv01 thanks for your assistance as I was able to fix that part of the code with your help. When I change the line in your code from
VBA Code:
.Range("B7").Interior.ColorIndex = 37
to
VBA Code:
.Interior.ColorIndex = 37
it works.

Also, when I changed the following line of code
VBA Code:
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"
to:
VBA Code:
  ThisWorkbook.Names.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"
that fixed it because I was making a local (Worksheet specific) name range and trying to use on another sheet. I needed to make a global name range, it fixed the macro. I will post the final solution.
 
Last edited by a moderator:

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
298
Office Version
  1. 2019
Platform
  1. Windows
The following is the final solution.

VBA Code:
Option Explicit

Public Sub Get_Rng_Nm()

    'Dimensioning
        Dim LastRow As Long
        Dim RngA As Range
        Dim RngB As Range
        Dim WS_Src As Worksheet
        Dim WS_Dst As Worksheet
        

    'Set Sheets
        Set WS_Src = Sheets("Our.Summary")
        Set WS_Dst = Sheets("Name.Ranges")


    'For Sheets ("Our.Summary") - get last row, set range, and then copy and paste
        With WS_Src
            LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set RngB = .Range("B9:B" & LastRow)
            RngB.Copy Destination:=WS_Dst.Range("A9")
        End With
    
    'For Sheets ("Name.Ranges") - get Last Row and set Range
        With WS_Dst
            LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Set RngA = .Range("A9:A" & LastRow)
        End With
        
    'Add name range
        ThisWorkbook.Names.Add Name:="Tickers.Nm.Rng", RefersTo:=RngA
        
    'Create data validation
        With WS_Src.Range("B7")
            .Validation.Delete
            .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"
            .Interior.ColorIndex = 36
        End With
        

End Sub
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top