Data Validation in VBA Code

H

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
626
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
 
Sort by date Sort by votes
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Even in a Blank brand new workbook Its not working :eeek:. 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

Yongle said:
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
Click to expand...
 
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
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
281
Fluff
Fluff
L
  • Question Question
How to fix VBA code to stop saving and emailing PDF of blank cell from data validation list?
Replies
2
Views
267
lharr28
L
F
  • Solved
Data Validation List - Auto update cell values with a twist
Replies
1
Views
1K
Fritz23
F
KlausW
  • Question Question
create a link between two Excel files which can be dynamic. VBA code.
Replies
0
Views
146
KlausW
KlausW
M
  • Question Question
Preserve data validation input messages when dragging
Replies
4
Views
751
matt767
M

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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