Data Validation refers to Sheet Name [Closed]

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Hello,

I would like to request for solving my problem
My workbook contains many sheets, some ends with the "-E" (Hyphen Symbol and Alphabet E)
So would like to request for macro that run in active sheet if sheet name ends with "-E" then
it will create DataValidation1

    '   Creating DataValidation1
        With Range("C6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=Product_Details!$B$7:$B$890"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "Invalid Product No"
            .InputMessage = ""
            .ErrorMessage = "You have typed the invalid Product No"
            .ShowInput = True
            .ShowError = True
        End With


Else Create DataValidation2

    '   Creating DataValidation2
        With Range("C6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="='Product_Details-E'!$B$7:$B$890"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "Invalid Product No"
            .InputMessage = ""
            .ErrorMessage = "You have typed the invalid Product No"
            .ShowInput = True
            .ShowError = True
        End With


May be I communicate the matter perfectly.
Please solve this problem
Thanks

Muneeb

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!