Conditional Dropdown Combobox

Aug 7, 2014

I am trying to develop an easier way for our corporate card holders to 'code' their monthly statements. Instead of each card holder receiving a statement and filling out a coding form in excel and printing it out, I would like to email them an excel doc of their specific charges for the month and allow them to use drop down lists to code the transactions. They can then email it back to me, and then we're done! Well, there would be a few more steps but basically, I'm trying to build a dynamic excel doc that would do two things:

1) Conditional drop down boxes so that, depending on their cost center, once they choose their cost center from the first drop down, it would zero them in on only their own account codes in the second box. The second account box would also be a combo box and show the account name once selected.

2) The second I'm not sure about.....is it possible once the conditional drop down box works, to make copies of the 'All Detail' sheet to then narrow down the info to only one card holder? So if the 'All Detail' sheet had all the charges for all the card holders for the month and the conditional drop down worked, could I then make copies of the 'All Detail' sheet, rename it to the one card holder, delete all the transactions except for those relevant to that card holder, and the conditional drop down box would still work? I ask this question because I build an early version (that didn't really work) based on a macro that had code specific to the sheet name. It wouldn't work once I tried to make copies of the sheet and rename it.

My example workbook is attached.

View 5 Replies


ADVERTISEMENT

Dismiss ComboBox Dropdown While ComboBox Code Is Still Running

Dec 8, 2009

When I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?

View 9 Replies View Related

Multicolumn Dropdown Combobox?

Jun 13, 2007

I have a menu with a dropdown control on it. I need it to be multicolumn (4columns, actually) but I do not know how to populate it.

View 2 Replies View Related

Dropdown With Value Equal To Combobox (real Time)

May 20, 2014

I wonder if it is possible in real time, to select a name from the combobox (Userform) still open Userfomr pass this value to a Dropdown in the tab?

View 3 Replies View Related

Combobox Multiple Selection Or Listbox Dropdown?

Oct 25, 2010

I have a userform where I want the user to be able to select multiple options, but am having a bit of a problem finding the best way to do this. If I use a combobox, I don't appear to be able to allow multiple selections, but if I use a listbox I don't appear to be able to implement a dropdown facility. I would prefer to have a single line sized box on my form, but doing this with a listbox would probably be confusing for the user as it is not very easy to see what has been selected.

View 11 Replies View Related

Two Column Dropdown List In Combobox Macro

Mar 19, 2009

I want to make my combobox will view 2 column of list and in the textbox will view "criteria" of sellected item.

View 4 Replies View Related

Dropdown Or Combobox With Static Space Between Text

Jan 13, 2010

I have a set of date of different lengths that I would like in a dropdown or combobox in a grid like fashion. Example

Apple, Banana, Orange, Kiwi
Mangos, Pineapple, Passionfruit, Guava

and I am trying to get it into the dropdown/combobox as

Apple,----Banana,-----Orange,-------Kiwi
Mangos,-Pineapple,-Passionfruit,-Guava

(without the dashes, the forum does the same thing excel does)

The data is of varying lengths, and the lists are actually parts lists, so they are long and of varying lengths each. Aside from physically going in a manually padding spaces, which isn't feasible given the number of them. The have alphanumeric characters, so jumbled all together seperated by commas is very messy and hard to read. I have tried padding with spaces using a formula but they do not line up right. I have been searching for a solution either having the entries in separate cells and concatenated entries, and still no luck.

View 10 Replies View Related

Combobox Dropdown Selection Makes Desired Value Appear In Cell

Mar 14, 2012

I have searched and have not been able to find an answer to this question so it might be a little unique possibly. I have an ActiveX combo box that I am trying to do something with. The text that I want to appear in the combo box is already there and I did this by creating a list and referencing it to the combo box. So the combo box has the following four items in its drop down:

Boat
Car
Plane
Walk

Now the part I cannot figure out is how to make a selected value appear in another cell based on what was selected in the combo box. For example, if boat is selected in the combo box, then I want the value 25 to appear in a cell. If Plane is selected in the combo box, I want the value of 100 to appear in a cell. For walk 5, and for car 22. I know this can easily be done with data validation and a validation drop down list but I do not want to use that but need to have a combo box do it instead. I just don't know if it cannot be done as a list or not and have experimented with INDEX but not having any luck there. I do not want to use any macros for this either. Basically I am wondering how I can make my combo box work just like data validation drop down menu bar with formatted values outputted into a cell.

View 3 Replies View Related

Hide Combobox Dropdown List During Long Macro

Mar 4, 2008

I've done some looking around in the object browser and in the forum, but i wasn't able to find the answer to this question:

I've got a combobox (dropdown list) in a userform which i use to run macros. soft of them are fairly long (2-3seconds), and during that time the dropdown list remains visible.... is there a way to force hide it?

View 6 Replies View Related

Adding Text Entered In Userform Combobox Into Dropdown List

Aug 1, 2012

I am not sure if this can be done easily but essentially what I have is a userform that fills out a form that will used by several different people and saved to a central location.

There is a blank combobox currently labeled "Supplier:" that each person fills out each time on the userform (there are no dropdowns yet).

What I am wanting to do is for every person who saves (I have coded the save button) with the "Supplier:" combobox filled out to save what they entered and input that into the combobox dropdown so when the next person uses the userform the entry the previous person entered would be in the combobox as an option to select.

Essentially, a combobox populates itself based on what previous users have entered into it.

View 8 Replies View Related

VBA Userform Combobox - Select Starting Point Of Dropdown Data?

May 22, 2014

Is there any way to set the list from a Combo Box to automatically start in the middle?

Whenever I select a Combo Box, the drop down list always automatically starts at the top of my selected range, ideally I would like it to start in the middle.

For example my Range that I am filling the Combo Box with lists dates: Today()-7 to Today()+7.

At the moment the first item in the combo box list is Today()-7, I would like it to be Today().

View 2 Replies View Related

Changing Background Colour Of Items In A Dropdown / Combobox On Userform?

May 23, 2014

I would like to know whether it is possible to change the background colour of items in a dropdown/combobox on my userform?

I have a series of times at 15 min intervals and i want, if possible, to distingish between 'working hours' and 'non-working hours' by making the background of these numbers 'grey'.

Maybe looping through from 00:00 to 07:00 i.e 28 times (28 x 15min intervals)?

View 2 Replies View Related

Copy Named Range Data To Worksheet Based On Dropdown Or Combobox

Feb 10, 2013

I have a worksheet called "Lookup" with several dynamic named ranges (each is 1 column wide) including facility, department, shift, etc. On a summary worksheet in the same workbook I want users to use a dropdown or combo box (don't care what type) in cell B2 to select a facility and then based on their selection, copy the department named range data and paste it into the summary sheet beginning in cell A5 and paste the shift named range data into the summary sheet beginning in cell B5.

Example: user selects "AR Plant" from the dropdown or combo box and the data from the "AR_Rpt" named range is pasted into cell A5 and the "AR_Shift" named range is pasted into cell B5.

View 9 Replies View Related

Can One Item Be Made To Stand Out In Dropdown List Combobox On Custom Userform?

Oct 12, 2013

I have a list of employees that can be assigned to tasks, the list is large enough that I cannot remember who is assigned to what.

All the employees assigned major tasks are automatically filtered out but these are employees with administrative tasks that should only be assigned as a last resort. I would like to assign the employees with administrative tasks a gray, italic, strike-out or some other font to indicate that they are different.

I assume this is done during the Add Item process...

View 2 Replies View Related

UserForm - Conditional Combobox?

Mar 3, 2014

I need to base a combo box / TextBox on the result of the previous combo box.

1. The first combo box is a simple 2-option: "New" or "Exisiting" choice

2. If the answer is "New"; I need a TextBox to show which would allow the user to input a code

3. If the answer is "Existing"; I need a second combobox instead of the text box which will allow a selection of codes.

View 3 Replies View Related

Conditional Combobox BackColor

Dec 11, 2006

As Im struggling to find a resolution to my protection problems from another thread, I thought id try to work a way around it. Now I am having a problem with what i thought would be a relatively simple issue: When contents of cell A1 is word "TRUE", then ComboBox1's backcolor is white. Else ComboBox1's backcolor is grey
THis is the code I have been trying to manipulate, by placing it in the worksheet coding:

Private Sub ComboBox1_Change()
With ComboBox1
If Cells(32, 5) = True Then
.BackColor = &HFFFFFF
Else
.BackColor = &HC0C0C0
End If
End With
End Sub

View 2 Replies View Related

3 Layer Conditional Dropdown List

Jul 16, 2013

I am creating a template with 3 layer drop down list where the selection list in second column is dependent on the first column and the third column selection list is dependent on the selection made in second column. The current template is prepared manually and are prone to errors.

The first column: Region Name
Second column: Province
Third column: Municipality

View 5 Replies View Related

Excel 2010 :: Conditional Formatting And Dropdown Box?

Jan 5, 2013

I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.

View 2 Replies View Related

Excel 2007 :: Conditional Dropdown Lists?

Mar 29, 2013

I'm using Excel 2007 and am trying to set up 3 columns of drop-down lists each dependent on the choice selected in the previous column's selection. It seems like it would be easy to figure out but I can't seem to do it.

I've already set up the arrays for the lists and "named" them but can't get the cell to be formatted automatically according to the previous columns selection.

View 6 Replies View Related

Dependent Dropdown Lists And Conditional Formatting?

Jun 14, 2013

I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.

I have an issue with over-type but I can solve that with protection. Again fine.

BUT:

1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)

2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).

View 2 Replies View Related

Multiple Conditional (Dynamic) Dropdown Boxes

Jun 5, 2014

I'm trying to incorporate multiple conditional (Dynamic?) drop down boxes to enable me to get an appropriate code at the end of the process. This will be used to product data entry and the code will tell Amazon where to post the product. I have many thousands of codes that i need to access and the easiest and most accurate way is to break down everything in to category and sub categories until I eventually get the code I want for that specific product.

for Example:

1730708031Clothing/Baby/Baby Girls 0-24m/Hoodies & Tracksuits/Tracksuit Bottoms

To return this code I would want to select Clothing, which returns a list which includes baby which in turn will give me a list that includes baby girls and so on and so on until I can select Tracksuit bottoms that will return the code.

Each list is dependent on the previous choice

As I have thousand and thousand of potential codes creating range list for each potential category and sub categories is not really feasible and would be very difficult to maintain when changes occur.

With this in mind I have created a code list that breaks the above example so that each category is now in it's own cell and there can be upto 8 (as I've seen so far) potential conditional categories before you return the code and ideally I would like to use this as it very easy to create and update. (see attached)

I might be able to use filters to return the appropriate list each time but don't know where to begin.

ClothingCodes.xlsx

View 4 Replies View Related

Copy Conditional Format On Dropdown List?

Mar 5, 2013

I have a drop down list in one column which I have formatted to change colour when chosen. What I want to do is to copy the colour on the other two columns beside it when it change. I manged to do one row by adding a new rule using "use a formulat to determine which cells to format" with the formula =$D$29="Awaiting Approval" (This is just one option on the list.)

Now my problem is how I am going to copy the same format to other rows without doing it one bye one or manually i.e. changing $D$29 to $D$30 I did the format painter but it does not reflect the lists on their respective rows/list/cell.

View 2 Replies View Related

Cell Change Based On Dropdown And Conditional Value

Sep 19, 2013

I have a value in Cell B2, a drop down value in Cell C2 and a conditional value in D2. I will very much like to have Cell A2 change it's value automatically if the correct if and then statements below are met.

Below are my if and then statements for the values I want in Cell A2.

Cell A2: ADD

If Cell B2 has a value of 1 or more and Cell C2 has a text value "No" and Cell D2 has a conditional value that's "Green"
Then change the value of A2 to Add

Cell A2: Delete

If Cell B2 has a value of 1 or more and Cell C2 has a text value of "No" and Cell D2 has a conditional value that's "Red"
then change the value of A2 to Delete

I haven't used If and then statements in a while and the example I gave above is just to get an idea of what I'm looking for to change in my cell.

View 4 Replies View Related

Userform - Conditional Transfer Of Data Based On Combobox Value?

Mar 17, 2014

I have created a userform and it works fine.Following code is assigned to commandbutton to transfer data to sheet "FT".

[Code].....

I want to add that : If value in combobox2 is “ KT” then all entries are copied to sheet3

Otherwise copy everything in sheet”FT”

Other parameters remain same.

View 4 Replies View Related

Conditional Formatting Of Adjacent Cells From Dropdown List?

Jan 9, 2013

In one column I have a drop down list of a dozen different initials with conditional formatting on those cells. I would also like to conditionally format an adjacent text string column based on those initials, but don't want to set up a dozen rules on each cell, one by one. If I set up the top cell with the correct rules I cannot fill the formatting in because the formula is not updated on a row by row basis, but en bloc for the range.

View 3 Replies View Related

Conditional Formatting For Highlighting Cells With Dropdown Values

Mar 19, 2014

I want to highlight all the cells in a worksheet that have dropdown lists in blue color. Not sure how to do it.

View 13 Replies View Related

Conditional Formatting Based On Selected Values Of Two Dropdown Menus

Dec 9, 2011

I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.

In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.

I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:

If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100.
If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80.
If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60.
If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95.
If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75.
If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55.
If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92.
If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72.
If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.

How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.

I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.

View 1 Replies View Related

Indirect Function And Substitute In Order To Create Conditional Dropdown Menus

Jun 26, 2014

I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.

However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.

It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.

Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.

My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.

View 6 Replies View Related

Getting Conditional Formatted Dropdown List Items To Keep Their Formatting In List

Feb 12, 2014

I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.

View 1 Replies View Related

Populating Combobox 2 With Items That Match Criteria From Combobox 1

Mar 30, 2009

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.

I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved