Free Microsoft Excel 2013 Quick Reference

[Excel 2003] VBA for data validation and dropdown list

Hello everyone!

I am using a macro to prevent the user from entering duplicate datas in an Excel spreadsheet. This macro works really well to warn the user a number has already been entered and tells you where. I must say I have not created this macro, a forum user created it for me here:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
Select Case Target.Value
Case ""
Case Else
For i = 1 To ThisWorkbook.Sheets.Count
Select Case Application.WorksheetFunction.CountIf(Sheets(i).[A:A], Target)
Case 0
Case 1
If Sheets(i).Name <> ActiveSheet.Name Then
GoTo modify
End If
Case Else
GoTo modify
End Select
Next
End Select
Exit Sub
modify:
myPrompt = "The Returns Note below has already been entered on " & _
Chr(10) & Sheets(i).Name & " cell A" & Sheets(i).Cells.Find(What:=Target.Value, After:=Sheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row & _
Chr(10) & _
Chr(10) & "Please modify your entry."
myDefault = Target
myInput = InputBox(Prompt:=myPrompt, Default:=myDefault, Title:="Duplicate Returns Note Entry")
Target = myInput
Next Cell
End Sub
There are 3 worksheets with at least 2 columns for each one, one with a limit of 5 choices to pick from a dropdown list and one where the user is allowed to enter a number.

So, I'd like to ensure the same number won't be entered twice, but the trick is, only if it duplicates a number already entered for one of the 5 choices picked from the dropdown list.

I hope this makes sense? I would be so grateful if someone could help

PS: attached is an example file should this be useful!

et merci!


Post your answer or comment

comments powered by Disqus
Using Excel 2007. Set up named range in F9:F10 that says Yes No. Set up data validation using List to create a drop down arrow showing the Yes No.
Works fine if I select Yes or No. However, if I skip a cell, I want the error message to come up saying it can not be a blank cell.

I have tried selecting the check box and unselecting the check box where it says ignore blanks. No joy with either, no error message saying cell must contain Yes or No, not blank.
I did write the error message. It just does not come up with a blank cell. It does come up if any other value is placed in cell.

I checked the help for data validation and it says any value can be used IF there are blank cells in the range. There are no blank cells in the range. (It is only a 2 cell range!)

What am I doing wrong?.

k

Hi all,

I need help! I created a dropdown menu using Data Validation and a dynamic range, so that anything typed into the range automatically appears on the dropdown.

The formula I used for this was a Defined Name of:

=OFFSET('Priority List'!$C$6,1,0,COUNTA('Priority List'!$A:$A),1)

Where C6 is the header cell of the column and I want the range to go on to the end.

What I want to know is how I can filter that dropdown so that when I select "A" from the dropdown menu, all the rows that do not have "A" in the C-column are hidden (like what happens with Autofilter). Is there any way to do this?

I am pretty new to the tough aspects of Excel, so please try to keep it relatively simple.

Thanks!

Hi all,

I need help! I created a dropdown menu using Data Validation and a dynamic range, so that anything typed into the range automatically appears on the dropdown.

The formula I used for this was a Defined Name of:

=OFFSET('Priority List'!$C$6,1,0,COUNTA('Priority List'!$A:$A),1)

Where C6 is the header cell of the column and I want the range to go on to the end.

What I want to know is how I can filter that dropdown so that when I select "A" from the dropdown menu, all the rows that do not have "A" in the C-column are hidden (like what happens with Autofilter). Is there any way to do this?

I am pretty new to the tough aspects of Excel, so please try to keep it relatively simple.

Thanks!

I am new at using VBA and really could use some help.

I am trying to add a data validation list to a range of cells. The list is a named range. Here is what I have so far, but I can't get the named range part to work. I am just playing with this piece of code and once I get it right, I think I will be able to write the code for my worksheet.
Also, how do I leave out the optional part of this code for the AlertStyle and the Operator?

With Range("e5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= HERE IS WHERE I NEED THE NAMED RANGE
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

I have a sheet where many cells have data validation. I have figured out how to check to see if a cell has validation, but is there a way for vba to check and see where the validation list is located for a given cell?

example:

cell A1 on sheet1 has data validation that refers to a named range called "animals" in sheet2. Is there a way for vba to look at cell A1 and return "animals"?

I would like to be able to use other worksheets or other workbooks for data
validation range.
Currently the validation range must be on the same sheet & although there
are macro workrounds, I find this restrictive.
Thanks

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hello and thanks for reading this thread as I'm stump!

Below is a very basic Excel 2003 VBA routine that has been in use for about 4 years. The code has worked as intended countless time with the exception of 3 isolated events that can NOT be repeated. I would greatly appreciate any clues, ideas, thoughts, prayers or the like to help me get to the root cause of these 3 isolated events. Many thanks in advance

Background:
This routine is part of a workbook that is used to prepare a list of survey questions from a set of user responses. After the user checks their applicable areas in a worksheet they click on “prepare survey” button that runs the code below. Description of each section of code:

Lines 1 to 16: notes, disable esc capability, define variables

Lines 17 to 23: check to see if survey is already prepared

Lines 24 to 26: copy value from cell Y11 in worksheet wsq to R11 in same worksheet. NOTE: This is the section of code that did not run on 3 isolated events from 3 different users from 3 different parts of a country. The value in Y11 is either TRUE or FALSE based on if the user checks a box in the worksheet. Checked box goes TRUE and uncheck goes FALSE. The code in these lines is important in that this value needs to be copied over to R11 so when the survey questions are prepared the ones corresponding to TRUE or FALSE are prepared. In all the isolated events the code ran perfect except it did NOT copy the value from Y11 to R11. When I looked at the 3 isolated events the values in Y11 corresponded to the user check selection but the value in R11 was NOT the same as IF the code just skipped over lines 24 to 26. When I open and use the 3 independent and exact files that the error occurs and clear the users input, enter their exact responses, and run the code (ie., “prepare survey” routine) the code works perfect. The error cannot be reproduced with the EXACT same user input in the EXACT same file they entered the data for all 3 files that generated the error

Lines 26 and on state flags to status of survey, prompt user to make sure he wants to prepare questions, runs a couple other routines that move boxes around and preps over worksheets, locks worksheets and workbook, etc…

Thanks again for you time and any help,

Mike

The code:
1	Sub prepare_survey_supplier()
2	''''''''''''''''''''''''''''''''''''''''''''''
3	'
4	' CODE CONDITIONS
5	' - Clear Macro named "clear_supplier"
6	'
7	'
8	''''''''''''''''''''''''''''''''''''''''''''''
9	    
10	    ' disable esc key
11	    Application.EnableCancelKey = xlDisabled
12	    
13	    ' define variables
14	    wsq = "Supplier Questions"      'questions worksheet
15	    wsd = "Supplier Data"           'data worksheet
16	    
17	    'survey prepared?
18	    If Sheets(wsq).Range("R8:R8") = "yes" Then
19	        MsgBox "Survey already prepared.  If you would like to reprepare the survey, press the 'Clear All Data'
button."
20	        End
21	    Else
22	        'continue
23	    End If
24	    
25	    'transfer W211 check condition in to vlookup range
26	    Worksheets(wsq).Range("R11:R11") = Worksheets(wsq).Range("Y11:Y11").Value
27	    
28	    
29	    promptflag = Worksheets(wsq).Range("R4:R4")
30	    If promptflag <> "no" Then
31	    
32	        ' Data entered used to define survey correctly?
33	        '
34	        Dim Msg, Style, Title, Help, Ctxt, Response, MyString
35	        Msg = "Make sure that your response to Question 6 is correct.  Your response can not be changed after the
survey is prepared without clearing all your data entered.  If any response is incorrect, please select 'No' and correct
it."
36	        Style = vbYesNo + vbExclamation ' Define buttons.
37	        Title = "All responses correct?"  ' Define title.
38	        Ctxt = 1000 ' Define topic
39	        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
40	        If Response = vbYes Then    ' User chose Yes.
41	            'no action
42	        Else    ' User chose No.
43	            End
44	        End If
45	    Else
46	    End If
47	    
48	
49	    
50	    
51	    'prepare hide boxes and set flags that worksheet is prepared
52	        Application.Run "hide_box_rear_supplier"
53	        Application.Run "clear_riskreport"
54	        ActiveSheet.Unprotect Password:="*****************"
55	        Worksheets(wsq).Range("R9:R9") = "TRUE"        'change common question flag to TRUE
56	        Worksheets(wsq).Range("R8:R8") = "yes"         'change survey prepared flag to yes
57	        
58	        Range("R9:R24").Locked = True
59	        Range("S11:X11").Locked = True
60	        
61	        ActiveSheet.Protect Password:="*****************"
62	    
63	    
64	    ' Sort Supplier Data based on user response
65	    
66	    ActiveWorkbook.Unprotect Password:="*****************"
67	
68	        Application.ScreenUpdating = False
69	    
70	    Sheets(wsd).Visible = True
71	    
72	    Worksheets(wsd).Select
73	    Range("C5:BZ154").Sort Key1:=Range("D5"), Order1:=xlDescending, Key2:=Range("E5") _
74	        , Order2:=xlAscending, Key3:=Range("BZ5"), Order3:=xlAscending, Header:= _
75	        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
76	 
77	    Worksheets(wsq).Select
78	    
79	    Sheets(wsd).Visible = False
80	    
81	            Application.ScreenUpdating = True
82	        
83	    ActiveWorkbook.Protect Password:="*****************"
84	    
85	    Application.Run "set_print_display_size_supplier"
86	    
87	    If promptflag <> "no" Then
88	        MsgBox "Survey prepared.  Please complete the survey below."
89	    Else
90	    Worksheets(wsq).Range("R4:R4") = "yes"
91	    End If
92	    
93	    Worksheets(wsq).Range("S12:S13") = Worksheets(wsq).Range("R12:R13").Value
94	    Worksheets(wsq).Range("T12:T12") = True
95	        
96	    Worksheets(wsq).Range("C13").Select
97	    
98	    
99	End Sub
NOTE cross post mistake:

Being new to this I cross-posted at http://www.mrexcel.com/forum/showthread.php?t=498958 I would have deleted the post at mrexcel so there would not be a cross-post but I could not find the option to delete my own threads maybe it was disabled.

Sorry for being a newbie.

I'm using Excel 2002 and 2003.

Has anyone had occasion to set up a macro to do Find/Replace (or even
just Find) on the Data Validation Input Text/Title, Worksheet Objects,
or the like?

Or perhaps a macro to dump the Data Validation set up in a worksheet or
workbook?

Hate to reinvent the wheel.

I'm starting to think in terms of a "Power" Find/Replace.

Thanks!
Neva

hi friends

with the help of one of the members here, i've been able to implement data validation on multiple cells (http://www.excelforum.com/excel-prog...ple-cells.html)

i want to preserve this validation even when the user copies & pastes the content from another excel. currently the validation works during the data entry mode only.

im told it is a limitation of excel (copy/pastes over rides any validation on the cell), though it can be achieved via VBA.

if anyone has come across any code which accomplishes this, please let me know. i've been after this for a while now.

thanks.

I would like to be able to use other worksheets or other workbooks for data
validation range.
Currently the validation range must be on the same sheet & although there
are macro workrounds, I find this restrictive.
Thanks

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I'm building a model with a master data table that drives some Pivot Reports. To avoid unlimited senseless subtotals, the data in the table needs to be rigidly validated. Some of the data is whole numbers, some percentages, some dates, and some list-driven. Originally, I set up the model using data validation for the tables, but the end-users really want the data input to be in a VBA form instead.

Trouble is, when I switch to the VBA form, I lose my data validation safeguards. The list-constrained items are easy to control with combo boxes, but I've lost my validation for the whole-number, percent and date items.

Any easy ways to get it back? There are 16 inputs total, of which 8 should be numerically or date-constrained. (So I don't want to do 8 input boxes.)

Hi:
I am seeing a discrepancy in Excel 2003 VBA
In the built-in Help , it lists the function Sqr as the VBA fucntion to
take the square root of a real argument. Notice that the S in Sqr is in
Upper Case. Similarly for other code examples within Excel and in David M.
Bourg's book, "Excel Scientific and Engineering Cookbook". But, when I am in
VBA inside of Excel it does not allow me to type (and keep) Sqr...it converts
the S to lower case s.
This works as a square root function, but is not the same syntax as Sqr.
Whiuch is correct ? Why does VBA's actual sytax (that used) not match what
is in the books, or the built-in Help ?

Another example that works correctly is the use of Tan() (with the upper
case T) for the tangent function.

Thank you

-DaleB

I need to put together as simple an application as possible to do the
following and need to know if Excel or Access should be used.

I have 3 groups in an organization who need to feed data (i.e. number of
pages printed per day) into either a worksheet or database. Each group
should have their own set of data and it's preferable to enter the data via a
form. The manager of the 3 groups needs to be able to access each of the
sets of data to review and generate reports - they'd prefer to generate them
in Excel because they are more comfortable using it than Access. Reports are
run on a daily, weekly and monthly basis. The data needs to be shared and a
harddrive on a LAN is already available. If only one person were to be
responsible for dataentry and reporting, I'd suggest setting up one workbook
with multiple worksheets. Is this still feasible in a shared setting with
multiple user access? I want to keep the solution as simple and fool proof as
possible (limited or no data loss and system crashing). All suggestions are
welcome!

Problem:

I need to use validate entry data using a series of dependent lists. For
example, in one instance the first Field (Field-A) is for Body Part (e.g.
UpperArm or Abdomen), which are stored in a series of vertical cells (List-A).

For each possible Field-A entry, there will be a series of possible Field-B
entries (for the muscles associated with that Body Part. For example
UpperArm would allow (among others) Biceps Brachii, Triceps Brachii,
Coracobrachialis, etc. and for Abdomen the possibilities would include Rectus
Abdominus, Oblique, etc.

As long as I've used defined-size Ranges on the Lists (e.g. A1:A12), I have
had no problem making the system work perfectly. In other words, I
understand that the entries on List-A must perfectly match the Range Names
for the dependent lists. Again, using defined-size ranges, the whole system
works perfectly.

The problem is that I want the ability to add an unlimited number of Field-B
possibilities (OK, there aren't an unlimited number of muscles in the body,
but I'm using this system for other things as well). So, I've been trying to
use Dynamic Range to define each List-B. Using EXACTLY the same Range Names
(re-defining each as a Dynamic Range), I run into the problem. When I click
in Field-B, I no longer see the appropriate List-B for each Field-A entry.
Instead, I see a blank drop-down.

As a point of bug-checking, the Dynamic Ranges themselves work perfectly.
If I use a test cell, and do Data Validation directly on the Dynamic Range,
the drop down list matches the Dynamic Range perfectly.

I am far, far, far from being an expert on Excel 2003, but I have come to
the conclusion that Data Validation via Dependent Lists defined by Dynamic
Range simply will not work. Am I correct?

If I am mistaken, where am I going wrong?

Lee

In Excel 2007, some cells get values from a dropdown list, using the data
validation feature. Then, I use the custom format of ";;;" for the range of
cells that pass the values to the dropdown list, in order to hide their
contents from the user.

I'm pretty sure, that the first time I've tested it, the dropdown lists were
working alright. However, at a later time, after reopening the workbook, the
contents of all the dropdown lists that get values from cells with numbers
are disappeared. I now only see the contents of a dropdown list that gets
values from cells with text.

What have I done?
Help please!
--
geotso
---- ---- --- -- -- - -
Please, remove hyphens to contact me
---- ---- --- -- -- - -

Am using VBA for data validation lists. One of my lists has
commas within each item, example:
Sales, Marketing and Administration
Parts, Planning
Excel however interprets each comma as 'start the next
item on the next line', so I get in my list:
Sales
Marketing and Administration
Parts
Planning
How do I fix this please? I was thinking it was by using a code (like
CHR(13) is used for carriage return), but can't find anything. So far I have
(all on one line
in the code version):

xlBetween,Formula1:="Sales, Marketing and Administration, Parts, Planning"

Any assistance is appreciated!

How do I determine if a range of cells is used for data validation elsewhere
in an Excel worksheet?

Hi,

Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).

Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.

In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.

I hope this is clear enough. I'm using Excel 2007.

[s]Edited: I'd also like to manage the conditional markup (I'm guessing that's what it's called in the English version) of these columns from a central location.[/s]

I've created a form in excel using VBA for data entry, i'm stuck with a problem :-(

I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.

1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.

Hope i didn't confuse :-) , will be great if you could provide some insights and help..

Thanks in Advance!!
Senthil

One of the things I dislike is when Excel VBA silently crashes and doesn't
return a debug message box. You can step through and find the bad line, but
that doesn't help you determine why the line fails.

In this routine, without the On Error line, it will silently fail on any of
the Target.Offset(0, 1).Value = lines. With the error trapping at least it
does know that there was an error, so my EnableEvents is restored.

Target.Offset(0, 1).Address is a Data Validation drop down list. The values
I'm trying to assign to the cells are valid entries. (Target.Address is
also a Data Validation drop down list.)

This is not the first time I've had problems with changes in Data
Validation drop down lists cells changing other Data Validation drop down
lists. I'm using XL2002.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo ErrorSection

If Not Intersect(Range("TermsLinked"), Target) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "Not Covered" Then
Target.Offset(0, 1).Value = "Follow Form"
ElseIf Target.Value = "Covered" Then
Target.Offset(0, 1).Value = "Excluded"
End If
ElseIf Target.Address = Range("TermsWC").Cells(10, 1).Address Then
Application.EnableEvents = False
If Target.Value = "No Exposure" Then
Target.Offset(0, 1).Value = "No Exposure"
ElseIf Target.Value = "Exposure" Then
Target.Offset(0, 1).Value = "Excluded"
End If
End If

ErrorSection:
Application.EnableEvents = True

End Sub

Don <donwiss at panix.com>.

Hi there,

Pulling my hair out to understand why i cannot save a workbook from .xlsx to .xlsm and carry over my data validations from an outside database workbook? I have tried clearing my data validation and entering it in again, but i receive an error. I have tried naming the cell range in the database workbook, creating a name in the .xmls workbook, and using the range name in the data validation, but again an error. This time it says the name range is not found in the other workbook.

The database file is saved as .xlsm, and the original workbook that is .xlsx. I wanted to create a few VBA codes, so i needed to save it now as a .xlsm, but i am at my wits end.

I have attached two files:

Database file is Master Material List.xlsm
Workbook is test takeoff.xlsx

In the workbook, I sometimes want to copy and paste a block, for instance rows 2:6. You will note that there are subtotals found in in cell O6, P6, Q6 and S6. When i copy and paste the block, I want the sum of the subtotals to be added automatically at the bottom Totals row. This is currenty O20, P20, Q20 and S20. To do this easily, i tried to use VBA colorfunction code to add all the like color cells. The code worked great, but then when i wanted to save the file as a .xlsm file to keep my codes, upon reopening the file, it would not read my Master Materil List, which is my data validation in column A, following with lookup values throughout the worksheet. Right now my test takeoff file does not have VBA behind it, therefore my totals row is simply based on the sum function.

Here is my colorfunction VBA code i was trying to use, and then changed my totals cells to read, for example in O2 =ColorFunction($O$6,O1:O17,TRUE).

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

    Dim rCell As Range

    Dim lCol As Long

    Dim vResult
    
lCol = rColor.Interior.ColorIndex



    If SUM = True Then

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = WorksheetFunction.SUM(rCell, vResult)

            End If

        Next rCell

    Else

        For Each rCell In rRange

            If rCell.Interior.ColorIndex = lCol Then

                vResult = 1 + vResult

            End If

        Next rCell

    End If



   ColorFunction = vResult

End Function
If anyone can offer suggestion, i would greatly appreciate it.

Thanks,

Hi All,

Can I set a default range for Data validation list which includes current and previous month's dates?

Any help would be appreciated!

Hello

I have problem in creating a data validation list. I have 7 independent columns City, County, State, Known As, Zip Code range, airport code, attractions. I want to be able to drop down any of the 7 columns and the other columns either auto populate or only have the selections available that correspond to the criteria met. So for example
If I Select New York à New York County à NYC à 10000-14999 à JFK
(and the other airports as well) à empire state (and other attractions)
But I also want to be able to do it reverse so if select White House I want it to auto populate the other way. Or if I select LA to populate. Basically I do not want dependent lists.
This is the basic code I started to work with:
=IF(A2="",state_NAME,IF(A2="New York",New_York,IF(A2=Buffalo",buffalo,IF(A2="houston",houston,IF(A2="washington",washington,INDEX(REGIONCOLUMN,MATCH(A2,FOBCOLUMN,0)))))))
But this only works for 2 lists not more… HELP!

Hi,

I have a data validation in a cell that works like this : if a control cell has the value "F" it validates the input with some dates in a custom list, if not it does nothing.

The formula looks like this :

Code:
It works fine but I also want it to  display the drop down list when cell F2 = F, now all i can do is type a value and it
checks if it's ok.
Can I do this without using vba ? I will send this to other persons and I don't want the macro warning to pop up when they open it.

Thanks.


No luck finding an answer? You could always try Google.