| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 67680 | 2006-04-03 06:53:00 | Macro/VB help Required | snoozee (10107) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 443380 | 2006-04-03 06:53:00 | I'm currently creating an excel program and need to write some code that will enable the follwoing to happen. Using a DropBox or ComboBox i need for the dropdown arrow to appear when the user clicks in the cell. They then choose from a dropdown list and depending on their choice the fill colour of the cell changes. I have been trying to get this to work and have come to a stand still !! |
snoozee (10107) | ||
| 443381 | 2006-04-03 06:58:00 | Have you looked at using Conditional Formatting for this? Do you want the user to have the ability to enter any information in the cell, or restrict it to a specific list of values? ... and I'm not sure you can make the drop-down arrow for the combobox appear and disappear at whim. |
Antmannz (6583) | ||
| 443382 | 2006-04-03 07:11:00 | Conditonal formatting only allows three different options i need about 10. I have seen what i want to acheive in another program, yet can't get hold of the creater to see how it was achieved !! The Users won't be editing anything only choosing an option from the list. |
snoozee (10107) | ||
| 443383 | 2006-04-03 08:00:00 | My VBA is a little rusty as I haven't had need for it for 5+ years .... but from what I can remember ... You need to place ComboBox controls on the sheet, fill it with the appropriate data, and bind them to the appropriate cells (check the Excel help for this). Then on the ComboBox OnChange event (I think) you check to see which value has been chosen and change the cell colour using something like Cell("A1").BackgroundColor="Red" .... like so .... Select Case ComboBox.Value Case 1 Cell("A1").BackgroundColor="Red" Case 84 Cell("A1").BackgroundColor="Blue" End Case Good luck. |
Antmannz (6583) | ||
| 443384 | 2006-04-03 11:14:00 | Close Antmannz :-) Range("A1").Interior.Color = vbYellow This can certainly be done using a SelectionChange event for the worksheet although Im dubious of the benefit. By the time I select the dropdown option then choose the colour from the ColorPicker I could have just used the Fill Color icon in the Formatting Toolbar. So what benefit does your code have over the native Fill Color? |
Parry (5696) | ||
| 443385 | 2006-04-04 01:15:00 | Hi, I think on reflection I didnt read your post clearly. Your not wanting to choose a colour but rather change the colour when certain values are entered. OK... 1. You can use data validation to get drop down lists for the cells. See Data Validation in the help menu. 2. use a Worksheet_Change event to alter the cell colour that equals a certain colour. Right click the sheet tab, select View Code and paste this code. Notice if you enter banana in a cell the colour will change to yellow etc. If you want it to be more dynamic you can colour the cells in your data validation list then use a lookup function in code to fidn the one selected and grab the colour. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range For Each c In Target If IsError(c) Then GoTo Skip Select Case UCase(c) Case "APPLE" c.Interior.ColorIndex = 3 'red Case "PLUM" c.Interior.ColorIndex = 54 'plum Case "BANANA" c.Interior.ColorIndex = 6 'yellow Case "ORANGE" c.Interior.ColorIndex = 46 'orange Case Else c.Interior.ColorIndex = xlNone 'no colour End Select Skip: Next c End Sub |
Parry (5696) | ||
| 443386 | 2006-04-04 08:26:00 | Good stuff Parry. I was surprised I managed to get a reply in before you ... and as I said, my VBA is rather rusty. :) | Antmannz (6583) | ||
| 1 | |||||