Automatically Trigger Formatting Code On Drop Down Choice
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.
I'm trying to create a drop down control for each cell that will present the user with values Red, Green, Yellow. Once selected the cell will turn that color.
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.
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.
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
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.
A1 is a drop-down list, created from Data> Validation>List, which lists 3 different words (Text1, Text2, Text3). I have recorded 3 macros (Macro1, Macro2, Macro3). Here's what I want to happen:
When Text1 is selected in A1, I want Macro1 to run (same for Text2/Macro2 and Text3/Macro3). I want the user to be able to change this value as many times as they wish and have the corresponding Macro run each time. I've tried creating the appropriate code in Editor using other threads on this forum, but I can't seem to figure it out.
I am trying to write a worksheet_selectionchange macro in which the change is initiated only when certain cells are changed (B6:B9 and X9). I do not want the change to be initiated when any other cells on the worksheet change. B6:B9 and X9 are drop down boxes and the values within the drop-down boxes is variable.
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.
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.
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]
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.
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...
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.)
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
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.
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.
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.
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.
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.
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.
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.
What I want to achive is the colouring of the cells in columns A-L using the trigger of the "Y" character in colums J-L
So when a Y is put in column J the cells to the left and including column J change to green. Then when a Y is put into column K the cells to the left and including K turn yellow. Finally when a Y is entered in column L the cells to the left and including L turn the lovely shade of Pink. It is possible the process will go from a Y in column J to a Y in column L mising out column K but I don't suppose this will matter.
I used to have it working in office 2003 to a fashion but have not yet got my head around 2010
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.
I found in this forum some vba code to insert into an excel spreadsheet a routine to hide rows if a value is < 1 in a column (Column B for this example). My problem is that it executes as you go down each cell in column B. I want to press a function key at the end of working on the spreadsheet to have it go line by line to hide rows that meet the condition. The code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range) Application. ScreenUpdating = False ' Dim R As Range ' Set R = Application.Intersect (Target, Range("B7:B7")) ' If R Is Nothing Then Exit Sub With ActiveSheet For Each Cell In Range("B5:B6") If Cell.Value < 1 Then Cell.EntireRow.Hidden = True End If Next End With Application.ScreenUpdating = True End Sub
I want add a short cut key combination to trigger to code currently in the worksheet section, at the moment it is triggered by a double clck in a row. [URL]....
I have options buttons in a userform, first is "$" the other is "€"
When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.
If numbers has been pasted in as text I cant convert them after. It convert the text to numbers and after it make a custom format with 5 zeroes.
I would really like to have the possibility to set the range with a input box and after set the numbers of zeroes/digits I want to have there. But have had problem put it in the code.
Have a look at the code below I have also pasted a test sheet to work with.
very complex spreadsheet for weight & balance calculations. It's to the point where everything works perfectly in Excel 2007, but it must be used primarily with Excel 2003. Discovered that a crashing problem had to do with condtional formatting, that's all been cleared and will soon be fixed, but there's one that I just can't quite figure out.
I'm using the worksheet change event to trigger the update of charts... In this case, it's looking at a particular cell that has data validation on a dropdown as the trigger. Works perfectly in 2007, and if I put a msgbox prompt in to be launched by a change of that cell, it launches. I can put the chart update code in a separate sub and launch it manually every time, but I cannot call it from the worksheet change.
I've included attachments showing what I believe are the relevant bits of code -
