Data Validation in VBA Code

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
hello yall Im tring to do a DV in VBA code where if A1=Yes then value in D1 must be between 1-120 and if value in A1=No then value in D1 must be between 121 - 150

Im thinking I need a DV in VBA Code. I already have a DV for D1 so I prefer a VBA.

Any suggestion is greatly appreciative.


Thanks
 
Column E
Code:
=INDEX(F$3:G3,MATCH(FALSE,ISNUMBER(MATCH(F$3:G3,E$1:E2,0)),0))
and drag down where the Letter G in (F$3:G3 goes next letter as dragged so E3 will have (F$3:H3 etc.

and column F and on will have
Code:
=VLOOKUP(E1,'CSA1'!$A$3:'CSA1'!$B$153,2,FALSE)
a V lookup code for the designated CSA sheet.
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Even in a Blank brand new workbook Its not working :eek:. Not sure what to do.
 
Upvote 0
ok So apparently after saving closing and reopen the workbook seems to done the trick.

YOU HAVE BEEN ABSOLUTELY AMAZING THAN THANKS A MILLION FOR YOUR PATIENT!!!!! :):)
 
Upvote 0
is there a way I condense the following macro that clears all datas in "A3:A122,A124:A153" in all sheets:
Code:
Sub Clear_All_Values()CarryOn = MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM")
If CarryOn = vbYes Then
Worksheets("CSA1").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA2").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA3").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA4").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA5").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA6").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA7").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA8").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA9").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA10").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA11").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA12").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA13").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA14").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA15").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA16").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA17").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA18").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA19").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA20").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA21").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA22").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA23").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA24").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA25").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA26").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA27").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA28").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA29").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA30").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA31").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA32").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA33").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA34").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA35").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA36").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA37").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA38").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA39").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA40").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA41").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA42").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA43").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA44").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA45").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA46").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA47").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA48").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA49").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA50").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA51").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA52").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA53").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA54").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA55").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA56").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA57").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA58").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA59").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA60").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA61").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA62").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA63").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA64").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA65").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA66").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA67").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA68").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA69").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA70").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA71").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA72").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA73").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA74").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA75").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA76").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA77").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA78").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA79").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA80").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA81").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA82").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA83").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA84").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA85").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA86").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA87").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA88").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA89").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA90").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA91").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA92").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA93").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA94").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA95").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA96").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA97").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA98").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA99").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA100").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA101").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA102").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA103").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA104").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA105").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA106").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA107").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA108").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA109").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA110").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA111").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA112").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA113").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA114").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA115").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA116").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA117").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA118").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA119").Range("A3:A122,a124:a153").ClearContents
Worksheets("CSA120").Range("A3:A122,a124:a153").ClearContents


End If
End Sub

Just like the code you shared with me below
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T1:T121")) Is Nothing Then
        Const Rng = "A3:A122,a124:a153"
        Dim Sh As Worksheet
        Set Sh = Sheets("CSA" & (Target.Row - 1))
        If UCase(Target) = "YES" Then Sh.Range(Rng).ClearContents
    End If
End Sub
[/QUOTE]
 
Upvote 0
Code:
Sub Clear_All_Values()
    Dim x As Integer
    If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
        For x = 1 To 120
            Worksheets("CSA" & x).Range("A3:A122,a124:a153").ClearContents
        Next x
    End If
End Sub
 
Upvote 0
Previous code does same thing as your original code, and appears concise BUT clears each sheet separately

This runs faster - range in 120 sheets selected and cleared at same time

This MUST be placed in a STANDARD module
Code:
Sub Clear_All_Values()
    Dim x As Integer, ws As Worksheet: Set ws = ActiveSheet
    If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
        Worksheets("CSA1").Select
            For x = 2 To 120
                Worksheets("CSA" & x).Select Replace:=False
            Next x
        Worksheets("CSA1").Activate
        Range("A3:A122,a124:a153").Select
        Selection.ClearContents
        Range("A1").Select
        ws.Select
    End If
End Sub
 
Last edited:
Upvote 0
Is there a way to not have it Stop at 120 and to cont. to the Last CSA #. So if Sheets are added where I don't have to Modify this macro every time a sheet is added

Previous code does same thing as your original code, and appears concise BUT clears each sheet separately

This runs faster - range in 120 sheets selected and cleared at same time

This MUST be placed in a STANDARD module
Code:
Sub Clear_All_Values()
    Dim x As Integer, ws As Worksheet: Set ws = ActiveSheet
    If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
        Worksheets("CSA1").Select
            For x = 2 To 120
                Worksheets("CSA" & x).Select Replace:=False
            Next x
        Worksheets("CSA1").Activate
        Range("A3:A122,a124:a153").Select
        Selection.ClearContents
        Range("A1").Select
        ws.Select
    End If
End Sub
 
Upvote 0
You have now strayed off topic - this is nothing to do with the original question
- so start a new thread if you want to know anything else :)

Amend the code if there are any sheet names beginning with "CSA" that should be excluded

Code:
Sub Clear_All_Values()
    Dim ws As Worksheet, sh As Worksheet
    Set ws = ActiveSheet
    If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
        Worksheets("CSA1").Select
        For Each sh In Worksheets
            If Left(sh.Name, 3) = "CSA" Then
               sh.Select Replace:=False
            End If
        Next
        Worksheets("CSA1").Activate
        Range("A3:A122,a124:a153").Select
        Selection.ClearContents
        Range("A1").Select
        ws.Select
    End If
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top