Free Microsoft Excel 2013 Quick Reference

Activate Macro from Data Validation Selection

The raw data is on sheet "Full Price List" and is arranged in four columns: item number, item description, pricing category, and list price. There are approximately 40,000 entries.

The primary sheet contains an order form where the customer selects which of the first three data categories to base the order on (item number, item description, or pricing category). Data validation in cell C13 is used to activate a macro that formats the rest of the form by setting up data validation for the selected basis, and vlookup to fill in the remaining data.

I'm currently using Worksheet_Change and a Select/Case statement to accomplish this, but when something in C13 is selected or any other action is taken anywhere on the sheet, it falls into an infinite loop and crashes excel.

Here is the offending code:


	VB:
	
Private Sub Worksheet_Change(ByVal Target As Range) 
    Select Case Range("C13").Value 
         
         'user has selected to fill in form using item numbers
    Case "Item Number" 
         
         'sets up data validation for item numbers
        Range("B18:C29").Select 
         
        With Selection.Validation 
             
            .Delete 
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
            xlBetween, Formula1:="=Items" 
            .IgnoreBlank = True 
            .InCellDropdown = True 
            .InputTitle = "" 
            .ErrorTitle = "" 
            .InputMessage = "" 
            .ErrorMessage = "" 
            .ShowInput = True 
            .ShowError = True 
             
        End With 
         
         'fills in item description from raw data using vlookup
        Range("D18:F18").Select 
        ActiveCell.FormulaR1C1 = _ 
        "=IF(ISNA(VLOOKUP(RC[-2],'Full Price List'!R2C1:R40031C2, 2, FALSE)) = TRUE, "" "", VLOOKUP(RC[-2],'Full Price List'!R2C1:R40031C2, 2, FALSE))" 
        Range("D18:F18").Select 
        Selection.AutoFill Destination:=Range("D18:F29"), Type:=xlFillDefault 
        Range("D18:F29").Select 
         
         'fills in list price from raw data using vlookup
        Range("G18").Select 
        ActiveCell.FormulaR1C1 = _ 
        "=IF(ISNA(VLOOKUP(RC[-5], 'Full Price List'!R1C1:R40031C3, 3, FALSE)) = TRUE, "" "", VLOOKUP(RC[-5], 'Full Price List'!R1C1:R40031C3, 3, FALSE))" 
        Range("G18").Select 
        Selection.AutoFill Destination:=Range("G18:G29"), Type:=xlFillDefault 
        Range("G18:G29").Select 
         
         'user has selected to fill in form using item description
    Case "Item Description" 
         'to be filled in
         'user has selected to fill in form using pricing category
    Case "Pricing Category" 
         'to be filled in
    Case Else 
         'do nothing
         
    End Sub '========================================

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope I adhered to the rules, thanks in advance.


Post your answer or comment

comments powered by Disqus
Hi

I have a data validation list that takes a list of 'options' stored in cells AE28:AE35
I would like a different macro to run each time an option is chosen from the data validation list.

Example data validation list contains these options:
Option 1 - Apple
Option 2 - Orange
Option 3 - Lemon

So if the user selects Option1 - macro 'Apple' is run
if the user selects Option2 - macro 'Orange' is run etc etc

can this be done - any help appreciated
thanks

Hello,

I am seeking a VBA MAcro help.

My question is if i select a list from data validation list. Only selected one list should have password as restricted information.

Let say i have 3 options as below
- Staff List (Any one can see)
- Staff Account (Any one can see)
- Staff salary (Restricted) no one can see only me.

Sample file is attached for your easy reference.

i need a solution on this to apply on my original file.

thanks in advance for the help.

Hello,

I am seeking a VBA MAcro help.

My question is if i select a list from data validation list. Only selected one list should have password as restricted information.

Let say i have 3 options as below

- Staff List (Any one can see)
- Staff Account (Any one can see)
- Staff salary (Restricted) no one can see only me.

Sample file is attached for your easy reference.

i need a solution on this to apply on my original file.

thanks in advance for the help.

I'm trying to have a macro run when I get a "Not in List" from data validation. So, if a user enters a value that is not on the list (DV is set up to Warning, so it will accept the value), I want to run a macro that will add the new value to the list. I can write the macro with no problem, what I'm stuck on is how to capture the fact that the user got the warning so I know when to execute the code.

How can I match a data validation selection with a value from the data validation list range. I need to find which row a name is in from the range and then pull more information from the range page row of the matching value.

I have a layout like this:
My information page:
Driver, VIN, ##, Year, Make, Model, LP#, Exp Date
Bob, xqew23weqiiuwer, AB0201, 2008, FORD, E-350, XXX 111, Dec-08

I have the data validation list based on the Driver column. I need to populate another worksheet with the rest of the row information.

Thanks,
Dave

best way of making hyperlinks from data validation list...?

i have a solution, but i'm not sure if it's the best...

picking an item from data validation and having it show as a hyperlink...

???

thank you.

Can I activate macros from cell by clicking it??

Hi all
Is it possible to run a macro with data validation?
Tahnks

Trying to write VBA code to have the contents of C2 paste (pastespecial) into B2 if the user selects "P" which is a selection possibility for the data validation list that resides in E2. Would like users of the form to be able to enter values into B2 unless "P" is selected from E2, at which time it would lock the cell and copy in value from C2 which is a formula that resides in a hidden column. If they select any other value the cell would unlock and the contents would clear. I have some code written, but have a feeling that due to my experience with VBA, it wouldn't be of any assistance here.

Hi friends,

I need to write a code, where a userform is displayed based on the selection from a data validation list.

My data validation has two options: Existing project and Proposed project. If the user selects, Proposed project then I wish to display a userform giving him some warning and asking to proceed or abort the operation.

For example, if the user selects Proposed project, a userform should be displayed with the message:

"Do you wish to commission the project in the next six weeks? And two commands buttons Yes and No. If he says, No then there should be message saying that "You are not eligible". If he says Yes, then the userform should go away and he should return to the normal functioning of the excel sheet.

Please help.

Hi all,

I have the code below that runs a separate macro or function for every 'option' in my data validation list.
When the sheet is opened it is protected.
What I would like is for the sheet to be unprotected each time an option is chosen from the data validation list, when the macro or function is run, then protect the sheet again.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "F8" Then
    Select Case Target.Value
        Case "Add new Job"
frmNewJob.show
         Case "Edit report"
frmEditJobref.show
       Case "View old report"
frmGetJob.show
        Case "View Adhoc Log"
Sheets("adhoc").Select
        Case "Add Shift Report"
Sheets("sheet2").Select
frmReport.show
        Case "View / edit Shift Report"
frmReport.show
        Case "Print card"
frmPrintjob.show
    End Select
End If
End Sub
any ideas/help appreciated
thanks

No matter the size of my table data for my validation or the size of the validation cell, the drop down text is 8pt font. Can I make this larger?

Also, Is it possible to have the user start typing in a data validation cell and it activate the drop down list and jump to the matching characters they typed for ease of selecting from a large list of products?

Hello Everyone,

I'm new here and I need a little help with my code.
Basically I have a a sheet to track the status of tasks.

The possible status are: Not Started, In Progress, For Review, Completed, and Overdue.
I used Data Validation in Excel to have a drop down list. Using macro to create Data Validation is not on my option.
I did not use Conditional Formatting as it is limited to 3 conditions only.
I created a module for the code. I did not use the Sheet(right click > view code) itself because doing so disables the Undo function of the worksheet.
I was able to create a code that changes the cell color depending on the status. The problem is, the cell does not change color instantly after selecting a value.
I need to double click the cell and then hit enter or click another cell for it to change its color.
I want the cell to change color right after selecting a value.
Another problem is that, the user can add tasks, therefore adding rows. Is there any way to automatically update the range?
For Example, the original Range is (H4:M50), when a user adds 5 tasks, the Range should now be (H4:M55)

Here's my code:


	VB:
	
 auto_open() 
     
     
    ThisWorkbook.Worksheets("Sheet1").OnEntry = "StatusChange" 
     
     
End Sub 
 
 
 
 
Sub StatusChange() 
    Dim KeyCells As String 
     
     
    KeyCells = "H4:M50" 
     
     
    If Not Application.Intersect(ActiveCell, Range(KeyCells)) _ 
    Is Nothing Then UpdateColor 
     
     
End Sub 
 
 
Sub UpdateColor() 
    Dim Cell As Object 
    For Each Cell In Range("H4:M100") 
         
         
        If Cell = "Not Started" Then 
            Cell.Interior.ColorIndex = xlNone 
            Cell.Font.ColorIndex = 1 
        End If 
         
        If Cell = "In Progress" Then 
            Cell.Interior.ColorIndex = 40 
            Cell.Font.ColorIndex = 1 
        End If 
         
        If Cell = "For Review" Then 
            Cell.Interior.ColorIndex = 40 
            Cell.Font.ColorIndex = 5 
        End If 
         
         
        If Cell = "Overdue" Then 
            Cell.Interior.ColorIndex = 3 
            Cell.Font.ColorIndex = 1 
        End If 
         
        If Cell = "Completed" Then 
            Cell.Interior.ColorIndex = 35 
            Cell.Font.ColorIndex = 1 
        End If 
         
         
    Next Cell 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I hope you guys can help me. Thanks a lot in advance!

I'm using xl2K and I've created a data validation drop down box that contains
10 items for the User to choose from. Is it possible to incorporate a macro
into this drop down box so that everytime an item is selected, a new row
(containing the same drop down list) is either added or unhidden.

Ay suggestions on how I can get this done?

After I make a selection from a data validation drop down list, I'd
like the active cell to move one row down.

Currently, the active cell stays in the cell with the data validation
drop down.

-M

I have this spreadsheet I am working on and have data validation lists set up in 2 columns currently A, B. Columns C- G currently are set up just for typing in data. In column H I want to automatically have current date entered when changes are made to any of the columns A-G and need to have this on the entire sheet. I'm pretty sure a macro is my best bet but have no clue how to even begin to start this one. I tried recording a macro and when I look at the code it produces it doesn't make any sense to me. Any help with this would be greatly appreciated.

This is what I get when I record the macro

Sub Macro2()

    ActiveCell.Offset(0, 7).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=now"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=now"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=now"
    ActiveCell.Offset(1, -7).Range("A1").Select
End Sub


Greetings,

I've been using Excel for years and just recently found this forum extremely helpful! Thank you for reading.

I've attached a portion of my workbook to make this a little more clear. The List in E5 contains all the National Forests in Regions 2, 3, and 4. E6 will have all the Ranger Districts of each of those Forests. I'm trying to code the E6 List to be effected by E5. For example, if the user selects the Apache-Sitgreaves NF in E5, I want them to only be able to select from the five Ranger Districts on that Forest (Alpine, Black Mesa, Clifton...)

Here's my original thought on what I can write in the Source field under Data Validation:

    ...and so on. This could take millennia considering there are a 41 Forests that have to go into that formula. Is it
possible to code VBA or record a macro to make this easier? I've had very minimal training on VBA and macros. So, if it is
possible, can anyone recommend a good source for learning VBA (book, website, etc.).

Cheers

Data Validation Excel Forum.xlsx

Hi - New to VBA & Excel Forum.

I've created a "dummy" worksheet to simplify what I'm trying to do. I have a task list in A1:A5 and a data validation box for each task in B1:B5. The values are either "Complete" or "Not Complete". If the value is changed to "Complete", I would like to add the current date to the next cell over in C1:C5.

I've tried several different codes and ended up piecing this one together from something I found online -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim isect As Excel.Range
Set isect = Application.Intersect(Range(ActiveCell.Address), _
    Range("A1:A5"))
If isect Is Nothing Then
    'Do Nothing
Else
    Range("C1") = Now()
    'Code for entering today's date in the next cell over?
End If
End Sub
Two problems -

#1 - I can't seem to find a code that will put the date in the next cell over [i.e. Offset(0,1)] from the data validation box that was selected.

#2 - My biggest problem - When "complete" is selected, nothing happens until a value is physically typed in another cell within the A1:A5 range. Once entered, the "Now()" is entered into C1 based on the initial entry.

Hopefully this makes sense! I'm using Excel 2004 for Mac. Thank you ahead of time for your help!

Kevin

I have two adjacent data validation lists. The first provides vice
president names and the second sales director names. When the user
selects from the first (a vice president), I would like the second to
automatically select the correct matching sales director. However, I
also want to give the user the option to override the normal matching
name, in which case the program should not change his/her selection.

Thanks.

Hi,
I have a large list of strings. Is there a way to arrange all those items in a combo box (made from data validation) in alphabetical order, and then typing just the first letter to get to the corresponding section?

ex: typing "a" would set the selected entry to the first entry that starts with an "a''.

If so, is it possible to code it?

Thanks,

Alain

Hi, i was wondering if any one can help on excel 2000. I am trying to create a spreadsheet and have applied Data validation to certain cells to enanble me to pick fom a list of values. However its a long list. What im wondering is if theres a method with this (or a completley different function) where by if you say type in 'bone' it will shorten the list to items that only contain bone( much like the LOV in oracle 11i works) which can then be selected and will enter into the cell .Hope thats clear. Any help would be much appreaciated

Dear Excel Helpers,

I will be very thankful if someone can please help me with the following.

I have created a Data Validation list in a Worksheet in a workbook WB1 where the drop down list shows the 7 values (105,0305,0505,0605,1005,1105,1205). These numbers represent the month and the year of data. There is a second Workbook (WB2) with 7 worksheets and the tab names show the dates (0105 through 1205) as given above. Now when a user chooses a month&year from the drop down list, say 0305, then, I want the codes to be written to go to WB2 and choose that particular tab (0305) for further analysis .
I have been working on this for a long time and could not get it working. Any help will be highly appreciated.
Thank you all.

Angela

Hello!

I have a workbook that contains a data validation list between G4:G31. I am looking for a macro that automatically populates H4:J30 with data from its corresponding sheet.

Example: Someone clicks on "Client Services" from the list located in the "Billing" tab. Data is copied from the Client Services tab A1:C24 to H4:J30 in the "Billing" tab. There is a sheet for every list item in the data validation list and the data will always be copied into H4:J30 on the "Billing" tab.

Thank you!

Hi and thanks a lot in advance for your help.

I want to make a drop-down list to fill different coulumns in different,seperated excel files(different workbooks). I do it from Data=> Validation=> Setting=> allow => list . but here I cannot select data from another file/workbook. (The data I want to put in this drop-down list is from a column of another source file, which may be modified and thus the data in that drop-down menu should change as the result). Can such a thing be done? How can I do this?


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