Run A Macro When A Drop Down List Choice Is Changed
Jul 23, 2007
I am using INDIRECT to create a second dynamic drop down list (L1) that is based on the cell contents of a first drop down list (I1). I want to create a macro that will blank the 2nd drop down list ONLY when the choice in the first list is CHANGED. I have the following, but it blanks the second list as soon as the first list is clicked on, rather than when a change is made:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Range("I1"), Target) Is Nothing Then Range("L1").ClearContents
End Sub
View 2 Replies
ADVERTISEMENT
Feb 6, 2008
I currently have a drop down menu in one of my worksheets, in which I have several different text values entered. What I would like to do is link each of those text values to a numerical value, which would be entered in to another cell. So if I select "Option A" from my drop down list, and Option A is equal to 200, I want "200" to show up in another cell. If I select "Option B" from my drop down list, and Option B is equal to 400, I want "400 to show up in that same other cell.
View 4 Replies
View Related
Apr 23, 2008
I have a drop-down box(K6) When a style is selected I need to have data show up in other drop-down boxes (H3,L3,P3) These selections would only pertain to the selection in K6. They would change when a different style is selected. There are multiple choices in the secondary drop-downs. [IMG][/IMG]
View 9 Replies
View Related
May 1, 2008
What I need to do is create a Drop Down box. When a user makes their selection it gives another drop down box from what they selected. Each choice in the 1st drop down will give a different drop down box in the next field from what they selected in the 1st. I am having troubles getting this to work.
View 3 Replies
View Related
Oct 3, 2009
I'm making a spreadsheet where I want the user to select an item in A1 from a List made using Data Validation. When the person makes the selection in A1 I want the next 5 columns to automatically display specific attributes that belong to A1. However, I'd like the user to then be able to change each of these items (in B1-F1) from their own individual Validation Lists. I'm using this to mix and match mythical monsters. So in A1 I have well known monsters like Jaws, Godzilla and the Lochness Monster. When you select one of these creatures, that creature's specific attributes show up in the next 5 columns -- things like Appearance, Motive, MO, Hideout, and Special Features. I then want to be able to change Appearance, Motive, MO, Hideout, and Special Features individually from their own Validation List. I basically just want to mix and match between the various creatures.
I've gotten it only partially working by doing the following: At the bottom of the page (for simplicity, I have everything in 1 sheet but I intend to move the "data" table for the Lists to their own sheet) I have a table where in the row for each creature I have the different attributes. A1 (my "creature name") was made using Data Validation from the A column. For the next column I made a Data Validation List for the items. Once that pulldown was made, I then went to the B1 cell and used VLOOKUP to autofill the cell when the person makes their "A1" selection. I repeated this for cells C1-F1. This works ok the first time you make a selection. But whenever you change the "default" value for B2 using the validation drop down list, the Vlookup formula is replaced by the actual text value. This results in the cell not changing when the person next changes the A1 value.
View 3 Replies
View Related
Feb 17, 2009
If I choose 1 thing from a list I want it to then fill in many cells with info.
For example: I have a color list to choose from, once I make my choice cells near it fill in with 1.5lbs yellow, 2lbs blue, 4lbs red,.....or what ever I have established as the "recipe" for my color choice. When I choose a different color from my list these cells each fill in with different values.
View 9 Replies
View Related
Oct 4, 2006
I'm trying to do is build a form that will allow me to select from a list of options, that links back to a catologue of data so that when i click on the generate button it will pull the data associated to the item selected from the list into a text box in excel. I have attached the form that I have created.
View 4 Replies
View Related
May 15, 2008
I have a worksheet with 7 macros each run from a seperate button. I would like to tidy up the sheet by having a combo box containing a description of each macro and one button to run the macro currently shown in the box.
View 4 Replies
View Related
Aug 25, 2008
I have found a macro that should work with a change of a specific cell.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub
However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:
Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................
View 8 Replies
View Related
Oct 25, 2006
Here is what I'm trying to do:
I have 4 sheets:
1 called main
1 called states
1 called counties
1 called people
On "main" I have 1 drop down box and 1 cell:
the drop down lists the states from "states" sheet
the cell, which i would like to have as a drop down is supposed to be listing the counties in the state selected from the 1st drop down. right now it is setting the value to TRUE for testing purposes.
then when the county is selected, it will display the people in that county listed in "people". So far I can only get the 1st drop down to list the states. that's the easy part. i need to getting the counties to list in another drop down based on what state is selected.
View 2 Replies
View Related
Sep 3, 2007
I'm looking for a method, vba macro or Excel code, to preform the following task:
In the C column the user chooses one of several options from a menu, let's call these options 1 - 10 (this part is already done). Depending on the choise, different things are supposed to happen:
For 1-9, the formulas for cell A - Q on the current row should be copied to the next row (but with +1 in row number in formulas of course).
For 10, 3 blank rows should be created (well they already are blank..), on the forth row down, A3 - Q3 should be copied exactly. On the fifth row, the same formulas thing that was created for choise 1-9 should be created.
Since the real xls-file is too big already and it contains some sensitive information, I can't attach it here, but I made a small and similar xls-file, that better explain what I want to do.
View 8 Replies
View Related
May 13, 2013
I used the "record macro" to record a simple sorting (high to low) of columns A-F based on values (0-3) in column A. Everything works when I initially institute the macro. However, when I change anything or add a new item at the end of the list, the order reverses and sorting goes from lowest to highest.?
Here is what ended up in the macro:
Code:
Sub SortColumnA()
' SortColumnA Macro
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
[Code]....
I noticed that it only includes a fixed value for the length of the list (row 90). Is there a way to have it increase when anything new is added?
As of now I plan on just making an arbitrary large index (~200 ish) to automatically grab anything new.
View 1 Replies
View Related
Mar 19, 2009
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies
View Related
Aug 15, 2008
What I want to do is lets say in the drop down menu I have options Food, sports, Movies. now if I select Sports then it should give me a sub caterory options like baseball, hockey, football and not the options for food & movies as well - so as to make the choices lesser and more user friendly. How do I create that - is it too tough - I am a novice in excel. Can you upload an example sheet with tips on how you did it. I have uploaded an example.
View 3 Replies
View Related
Aug 9, 2007
getting a macro to dynamically assign a drop down box to a list of numbers?
Specifically, I have a list of numbers in column A, and I would like to dynamically (because the size of the column differs every time) add drop down boxes in column B so that a user may select different values in column A, simply by changing the drop down box value to "Selected"
This would this make it easier for collect all the "Selected" values and perform further analyses upon them.
View 5 Replies
View Related
Dec 11, 2008
I am building a template in Excel and I want an error message (or any message) to appear to the user if they select from a static drop-down the same option in column I as in column J (i.e. they have to pick something different in each case).
Issue is I can't exclude any options from the second validation because, essentially, it's the same list. So if, on one row, a person picks "Bill" in column I, they can't pick "Bill" in column J. However, if they have picked "James" in column I, they can pick "Bill" in column J.
Don't know if this is well explained enough but would appreciate any tips on this - I've tried messing about with validation, conditional formatting, putting an IF statement in the next column and so forth but I haven't found a method that isn't convaluted and easy for the user of the template...
View 9 Replies
View Related
Dec 2, 2009
I have attached a spreadsheet that I need to automate. Cell C3 is a drop down box of the raw data found on the raw data sheet. I would like B6-8, c6-8 and d6-8 to automatically updated and change depending on what users select from the drop box in C3. (so basically im trying to re create the yellow table with formulae/ vb.)
View 4 Replies
View Related
Dec 13, 2007
I'm working on a workbook that already exists and that contains multiple worksheets (A,B,C,D,...), each with specific info (each row consists of an item name (item 1, item 2, item3,...)and all characteristics of this item).
We now want to add a help page for each worksheet :
A => A help
B => B help
...
So for each item (=row) in sheet A, there should also be an according row with additional info in A help
First, I used static hyperlinks to switch from the item in A to the corresponding help in A help. However the item list can be extended in the future (meaning rows inserted) and this will cause these links to fail. Therefore I switched to dynamic hyperlinks by inserting names (in stead of the original cell names) for each item in both A and A help. This involves a lot of work since you have to give all the involved cells a unique name and you have to refer to this unique name when you set up the hyperlink.
View 2 Replies
View Related
Feb 12, 2008
Want to remove choices from excel autofilter drop down list like (non blanks) or (custom...).
Excel 2000 and above. Using vba is OK.
View 3 Replies
View Related
Feb 12, 2008
My Main data in my workbook is copied from another workbook. From there it is sorted into separate worksheets for each customer via a macro. I want to be able to set something up so that I can list the customers and then list job descriptions, products and pricing from the correct sheet and return the selected data back to its original form in sheet1.
I am also trying to run the macro that sorts the data in this workbook from another workbook.
I have attached the file so you can see what i am trying to explain.
View 9 Replies
View Related
Oct 26, 2009
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies
View Related
May 9, 2009
I'm working on making a spreadsheet that allows me to select an item type from a dropdown list (through validation) and then would copy in a range of values from another worksheet based on my dropdown selection.
The set-up: I am primarily concerned with two sheets in my workbook, BiS and Weights. I have a database of items and their associated values listed in Weights. I have already managed to get dropdown lists of my specific item types using named lists and have put that in the BiS worksheet.
What I would like to do is have the values in the Weights worksheet copied over in the same order and number of cells based on what item I select from the dropdown list.
View 6 Replies
View Related
Apr 6, 2008
I'm building a Excel 2003 workbook that needs more than 3 conditional formats. I've tried the code given in the FAQ: Get Around Excels 3 Criteria Limit in Conditional Formatting but this only works on cells being modified directly. I've also tried the code in the post: VBA Conditional Formatting - Refresh when data changes but neither does the job I need.
I'm using a Format Control to present the user with a list of choices. When the control changes the target cell I want to apply a conditional format to that cell. This image shows the form. When the Format Control selection (in B2) is changed I want to apply conditional formating to the target cell (A2).
There will be up to 10 choices and therefore conditions.
View 9 Replies
View Related
Jul 23, 2009
I am somewhat of a novice with Pivot Tables. One problem I am having that no one seems able to help me out with is that when I update and refresh my pivot table, it seems like it has a muscle memory with the drop down choice boxes.
For example:
Lets say I have three customers that I have labeled 01 Target, 02 Walmart and 03 Bed Bath and Beyond. If I want to change 01 Target to 02 Target and 02 Walmart to 01 Walmart, it works in the pivot table and shows the proper data but if I try to choose the customer from a drop down box, it remembers the customers as labeled as they were before I made the changes AND the current look.
I will see: 01 Target, 02 Target, 01 Walmart and 02 Walmart in the drop down box but the data will only show up in the pivot table associated with the proper customers as they are labeled in the data source.
View 9 Replies
View Related
Dec 28, 2007
I have three lists in the first sheet 'Food', 'Quantity', 'Points Value'
Each food (i.e. tuna, pasta, anchovies) has a quantity (i.e. 40g) and a points value (i.e. '50'). The first list ('Food') has been copied as a drop down list in the second sheet. When selecting a food, I would like the cells 'quantity' and 'points value' to pre-fill - taking the data from the first sheet.
i.e. if say Anchovies has the quantity value '30g' and Points Value '2' on the first sheet, then in the second sheet if anchovies was selected from the drop-down menu, these values would add themselves. how to do this? I have attached a sample file shwoing what I want to do.
View 2 Replies
View Related
Jul 6, 2009
I'm using Excel 2007.
I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line.
OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part.
If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again.
I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.
View 9 Replies
View Related
Dec 4, 2008
When someone makes ANY selection from my drop-down list, I would like my macro to run so that the user can continue to fill in the rest of the form. My macro simply unhides some rows as below:
Sub UnhideForm()
Application.EnableCancelKey = xlDisabled
ActiveSheet.Unprotect Password:="Testing"
Rows("15:112").Select
Selection.EntireRow.Hidden = False
Range("Name").Select
ActiveSheet.Protect Password:="edit"
End Sub
Here's some info you might need:My sheet is called "Form"
My macro is called "UnhideForm"
My drop-down list is in the array called "TypeOfMoveCell"
I have 5 dropdown options (which includes an option called "Please select from list...") These options are located in cells BW145:BW148.
I would also like the macro to re-hide the data rows if someone deletes the value in the drop-down list, or if someone re-selects "Please select from list..." option.
View 9 Replies
View Related
Mar 30, 2009
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down.
My drop-down list is created through "Data->Validation->List box"
View 9 Replies
View Related
Oct 19, 2006
I'm able to create a simple User form Drop-down list that references a cell range list. ( as a test for myself) I have 4 separate macros I would like to run individually. Rather than create 4 separate buttons, I'd like to be able to select each one via Drop-Down list.
View 4 Replies
View Related
Jan 17, 2008
I have tried to create a macro which prints the results of each heading in a drop down box. The listfill range is from B2 to B5 and the linked cell is B1. When manually recording the macro, i copy the heading from B2 into B1, the drop down list updates which in turn updates the cells which are linked to that, i then print out the page. The same happens when i copy-paste cells B3, B4, & B5 into B1. I then return finish the macro by copying B2 into B1.
But when i run the macro, the copy paste works but the drown down list doesnt update, therefore it doesnt update my linked cells so i acutally end up printing 5 indentical pages with the same info.
Is there anything i can do to get around this?
Macro listed below:
Sub copypasteprint()
Range("B2").Select
Selection.Copy
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("E5").Select...........
View 9 Replies
View Related
