Inter-relating Combo Boxes In Userforms Based On Data In Tables
Oct 4, 2009
I have worked out (through reading and what not) how to link a combo box to a range in a worksheet. What I need to do is relate what is selected in that combo box, to another combo box which picks from another range. I have a data spreadsheet called "Offices". It's set up as follows
(Sorry, it's really frustrating trying to format it to a table type thing but you get the gist)
I want to populate the values of two textboxes that we will call TextBox5 and TextBox6 from columns W and X of a spreadsheet called "PowerAnalysis" when a selection is made form ComboBox5.
The ComboBox5 works perfectly now. I just need it to populate the other two text boxes.
All the data resides in the same row on the same sheet of PowerAnalysis when the selection is made in ComboBox5.
I hope I have been able to give a clear picture of what I am wanting to do.
I have two combo boxes that both contain 7-12 separate search criteria for the user to choose from. The other includes months and the other value ranges in text form. Based on the selections, e.g. "August" from other and "increased by more than 5 %" from other, I'd like to have a command button to execute the appropriate macro. I've already compiled the macros for each occasion but I just can't figure out how to get the button to execute them. Can I use the Select Case statements? If so, how?
Oh, and whether it's relevant or not, the boxes and the button are from the control toolbar.
I'm trying to create a workbook for tracking costs and change orders for multiple subcontractors. My main question is about combo boxes. I would like to create a master list of subcontractors on The masterlists tab of the attached workbook. I would like to use this masterlist as the data source for a combo box I'm going to use to replace the subcontractor txtbox on the SubCoEntryFrm. I figured getting away from a user typing something in would benefit me if I wanted to use that subcontractor name field to analyze data.
I am developing a spreadsheet with numerous information on our different suppliers offices in the country for each of our outlets. I am tryin to define a way to do the following:
If a colleague selects a company another list will appear with the region and when the region is selected a list of the offices will appear.
I designed some userforms on a worksheet for users to click and input information. However, i am wondering if it is possible to have an additional feature to the userform.
Currently the userform only have labels such as Title, Dept, Branch, Unit and the respective textboxes for user to input information. As it might be unclear to some users i would like to have an additional feature such that when user roll their mouse over the label "Branch", the user will see a box where more information is given or rather an example such as "Dept XYZ", allowing user to have a clearer idea of what to fill in. Is there any way for me to add this feature?
I am close to finishing making up an Excel tool that takes data input from the user, does some analysis, accepts further input then prepares a report. My coding skills are not what you would call high level, so my code could probably be more efficient, but it works (well, except for this issue).
The tool opens a main multipage userform for data entry, then allows the user to view the data in the spreadsheet tables with the ability to call separate userforms to modify, delete or add records. The user then calls another userform to do some calculations and enter the appropriate results from a separate piece of software, then presses a button to prepare a report. All userforms are called from buttons in a separate userform (I'm not proficient enough and haven't had enough time to create a new ribbon in excel). At various times, messageboxes are used to convey information or prompt the user to do something - these are all simple ones with either only OK button, or OK and cancel buttons.
My problem is that when userforms are called, they sometimes appear but are totally blank (white) - see image.
blank_form.jpg
Clicking anywhere on the screen or pressing any key will bring them up properly and they seem to function normally thereafter.
In addition, the messageboxes sometimes do not appear - pressing any key will make them do so. Other than this they function normally.
The frustrating thing is that these problems occur inconsistently. I can add or change some code and it stops happening, then after testing (entering data and using the tool) starts happening again. Sometimes it just stops happening with no apparent reason, but starts again later after fixing some code or just entering some data. Not all userforms are affected at any one time and not all messageboxes have the problem at any one time.
The problem seems to occur in those subroutines where I access or manipulate data from other sheets before showing the userform or message box (but in most instances, I need to do this manipulation in order to present the correct information in the userform).
Some thought processes I have had, and unsuccessfully tried to fix the problem with (some coming from various web forums):
I thought I may have done "Application.ScreenUpdating = False" without resetting it to true, so I commented out all the "Application.ScreenUpdating = False" statements - problem stopped for a bit then started again.
Some of the userforms were modal, and even though they were not showing were still loaded so thought that this may be impacting on other userforms/message boxes, so changed all userforms to non modal - still had the problem when I opened the tool again.
Tried using "RePaint" and "DoEvents" at various points in the code after opening a userform or message box - no change.
I have tried exporting a form and its code, removing from the tool, then re-importing it. No success.
Tried the tool on another computer and the same problem occurred.
I have a manualy constructed user form which has 3 data validation lists.
Sources: B7 = List 1 B12:B15 = List 2 B17:B20 and C17:C20 = List 3
These operate fine but I wish to replace the dropdown validation boxes and replace them with Combo boxes so that users are able to view the complete lists and also offer the option of inputting an item not included in the validation list if required.
I have tried many variations of input but my knowledge base is inadequate!
Mainly I end up with the combo box working on one cell only!
Can tell:
1. Do I have to raise 1 combo box per cell?
2. What vba code is necessary to make them all work?
I created a combobox on the fmEdit userform that I want to use to select a name from the worksheet and display on the Userform the data from that row for editing, which can then be submitted to make changes accordingly on that row.
I did away with the merged cells, and now I have a combo box in every cell that I want it in.
My problem is, I would like to format each combo box seperately. Every cell that has a combo box in it has data vakidation activated. I copy and pasted code to get it to work. It works great, but I would still like to change the combo box size and tab and enter functions for each individual one. I created more than one combo box but I kept getting an error on them. Any help or knowledge would be greatly appreciated.
I am posting just the code that I copy and pasted.
I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu at the top of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. Is this possible?
Is there a way to add comboboxes to a userform on the fly.
For example there are twenty different categories. I want the user to be able to say he wants 2 of these categories and 2 combo boxes pop up (each combobox has a list of the 20 categories). Then next to the categories, text boxes to type the quantity of each category.
I have quite a few combo boxes on my Excel worksheet. These combo boxes are from the Control Toolbox. Right now, the drop down lists work great, however, if the user wants, they can just type in a value. How do I stop them from doing this, and make it to where they can ONLY select one of the choices from the drop down.
I am trying to use a vlookup from input from a combobox. In other words, if a user selects "Los Angeles" from a combo-box titled "city", I want the state of California to appear in a cell labeled "State" (A3).
is there a combo boxes limitation in a certain worksheets or workbook.? if there is then how many? coz i got a problem , i have to make around 2000 combo boxes, if i save it , i got no problem , but when i closed it and then reopen , the problem occurs..
I have an excel database with comboboxes. I want to be able to filter the data, therefore I am using the autofilter function.
1) When I filter, the comboboxes above a data section that is to be viewed end up at the first row, and comboboxes below end up in the first row following the viewed data set. Is there any way in which I can hide comboboxes when their rows are hidden? Today I use the form comboboxes, but I can use the control toolbox ones if necessary. Also, I can use some other function than the autofilter function if necessary.
2) Is there any way to make the filter function take information in comboboxes into account as well? For example, if I filter by weight and one option is available in three different weightes (displayed in a combobox) I still want that to be seen following filtering (can I perhaps write information in the cell under the comboboxes? If so, can the filter function take several data in the same cell into account?).
I have in a user form 2 combo boxes. In one of my sheets I have the data that I want to be loaded in the combobox depending on the other combobox. In other words if combobox1 = white, then combobox2 needs to have the following items to be picked from "large", "medium", "small". If combobox1 = "blue" then I combobox2 needs to have another 4 items and so on. I have about 8 different options in combobox1.
What code could I use on that?
Also in that same project, I have a spreadsheet that has all my data; such as a inventory. How can I do, when I pick the SKU# to load the other fields, such as Description, qty on hand, price, etc?
How do I change the below code to fill all combo boxes all on sheets. All the names of the controls should be the same on each sheet since each sheet was created from a copy of the original.
I’ve created two combo boxes and what I seem to need are “cascading combo boxes”
I have named ranges on a worksheet called LookupLists Column A has Areas and Columns H to L has services within those areas. Column headers, i.e. Row 1 of the columns, H to L are Serv1, Serv2 and so on.
Each area, in column A, has different services, in column H to L.
I have two combo boxes, what I’d like is depending on which Area is chosen in the first combo box, that the correct column is chosen which lists only those services available in that area; for example maybe Area A has services 2. Area B has services 4 …
… so in my “logical” mind if Combo box 1 I choose “Area B” then I want Combo box 2 to look at Column K and show the services from there..
I trying to do a sheet with one combo box (dropdown list) where I want the user to be able to choose "add rows" to make more boxes appear. My idea to solve this was to put all the boxes in the sheet, and then create a macro that either hides or shows the rows with the extra boxes. Now I have a problem that hiding the rows just does that, and only that. The rows disappear, but the combo boxes stay visible (but ends up on top of eachother).
I'm trying to link two combo boxes via a user form. I'm trying to select a city in one combobox which in turn would provide a listing of zip codes for that particular city in the next combobox.
I'm building an application that uses a large number of activex combo boxes. These boxes are getting loaded from specific fields in an associated database.
I know ActiveX combo boxes can be buggy; I'm wondering if anyone has found a way around the text appearance inconsistencies seen in these controls? e.g., sometimes the text is smaller, sometimes it's spaced wider, sometimes it appears at the top of the combo box sometimes in the center.
I put a screen shot of what I'm talking about in a word doc along with a control properties screen shot, All controls have the same properties except for the text property.
I have 4 combo boxes that allow me to make selection from pivot tables. As you know in the pivot table there's the "All Selection" which does not exist in combo boxes (I think), so I created a macro to reset all the pivot tables with the "All" option selected in all of them. However the values in the combo boxes are not changing.
I'm trying to sync a common field in two different pivot tables.
I have 2 pivot tables on a single worksheet. Each table has its own data source - 2 different databases (had to set it up that way to present all the data requested).
The 2 data sources have one column of data in common. This column is called Projects and all project names are the same in both databases.
BUT...
Pivot Table 1: Projects is in the ROW area (multi-select dropdown)
Pivot Table 2: Projects is in the PAGE area (single select drop-down)
I would like to link the Projects data items so that when I select a project name in the Combo box, the same project name would seamlessly be selected in both tables.
Each table would populate with its own data based on the project selected.
Basically, I'd like to use the method illustrated in this Flash file: [url]
This method would have worked beautifully if it weren't for this reason:
Table 1: Project data is in the PAGE field (single selection)
Table 2: Project data is in the ROW field (multi-selection)
If the Projects data was in the PAGE field in both tables, my code would look like this:
At the moment all our work is logged by codes eg. A1Misc, B5Change, N9Important. As well as the codes, we enter the date in the column next to the code along with another Column to show the amount.
I need a Macro to look at the date column and only take dates which have Sept then go to the column next to it and pick up the codes individually and sum up all the amounts for the codes.
I have attached an example which shows from A13 downwards what I need the macro to show when finished.
I have a list of stores (A1:AX) and corresponding customer affinity numbers (B1:BX). Each time a customer visits a store with her affinity car, a record is generated. So the data is quite simple - two columns.
The tricky part (as always) is the output. I would like to divide the customers into three groups:those who visit only 1-3 stores those who visit 4-7 stores those who visit more than 8 stores
I would like to create a way to view a list of stores in column A and see how many of its customers are in each of the three groups listed above. Does that make sense? Output might be:
