set data validation source list

**rana_shaker** · 12-13-2012, 02:14 PM

Hi ,,,

I have a data validation list in B2 .. I want to update its source when I enter the page

I want the source to be the following : "=template!$B$3:$B$last row"

which is from B3 to last row in B in "template" sheet

how can I do that using VBA ??

**TMS** · 12-13-2012, 02:18 PM

Dim lastrow as long
lastrow=range("B" & Rows.Count).End(xlUp).Row
; ... _
      "=template!$B$3:$B$" & lastrow

Regards, TMS

**rana_shaker** · 12-13-2012, 02:21 PM

Thanks TMShucks .. but my problem not this ..

what to write before this part of code "=template!$B$3:$B$" & lastrow ????

**rana_shaker** · 12-13-2012, 03:13 PM

this was my try .. but for sure it is wrong !!

Dim lastrow as long
lastrow=sheets("template").range("B" & Rows.Count).End(xlUp).Row
Range("B2").Validation.Formula1 = "=template!$B$3:$B$" & lastrow

I want to know how to make it pleeeease ..

**TMS** · 12-13-2012, 03:52 PM

Dim lastrow As Long
lastrow = Sheets("template").Range("B" & Rows.Count).End(xlUp).Row
'
Application.Names.Add "DVList", RefersTo:="='template'!$B$3:$B$" & lastrow
'
With Sheet8
    With .Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=DVList"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End With

Regards, TMS

**rana_shaker** · 12-14-2012, 02:25 AM

Thanks TMS .. thats what I want

**TMS** · 12-14-2012, 03:06 AM

You're welcome.

set data validation source list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

set data validation source list

Re: set data validation source list

Re: set data validation source list

Re: set data validation source list

Re: set data validation source list

Re: set data validation source list

Re: set data validation source list

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions