I want to have a spread sheet where a user will pick an option from a drop down list, then based on that option choice, have a different cell populate with a specifc drop down menu.
Example:
Cell A1 has a drop down with three choices (1,2,3)
User selects option 1
Cell B1 will now have a drop down with options A,B,C
However:
If user selects option 2 from A1
Cell B1 will now have a drop down menu with options X,Y,Z
what its called when you have drop down menus that correspond with other drop down menus.
View 2 Replies View RelatedI would like to make a form where the user has to select from a drop down list several options, and that depending on the pick the drop down list below shows different options. example: For the question "what food do you want to eat?" you can select pasta, pizza or dessert.
Now if that person pics dessert, the 2nd drop down menu should shom him again a number of desserts he has to pick. And again below another one depending on what he chose.
If I have a cell that uses a list for drop downs (ie "Make" can be any of several different Fire Alarm Panel Types) and a second cell that I want to use to represent "Model Number" is there an IF the model is A from the Make list, THEN the next cells drop down list represents the model numbers under that Make?
View 2 Replies View RelatedI am trying to program a drop down box at the top of a column so that when one of several options in the drop down are selected, a corresponding price list is displayed in the column below.
View 4 Replies View RelatedI am trying to force movement between multiple drop down menus. The menus are independent of one another. I want the user to only be able to select the information in the menus.
View 9 Replies View RelatedI have an excel sheet with Column H having drop down menus for each cell In the column, starting with H5 going downwards.
The drop-down menu contains around 50-70 options, and what I have to do manually is go to h5 and click on the drop-down menu and select the first value. I then go to h6 and click on the drop-down menu and select the second value. I then go to h7 and click on the drop-down menu and select the third value. I then go to h8 and click on the drop-down menu and select the fourth value.....and so on and so on.....until I have chosen all values in the drop down-menu!
Is there a macro that will just do this automatically? Starting with cell h5 get the first value of the drop down menu, go to h6 and get the second value of the drop-down menu and so on until all the values are chosen?
If you manage this one, I also need to do something similar for column K, but this time select the first value of the drop down menu for k5 AND k6, then get the second value of the drop down menu for k7 AND k8, then get the third value from the drop-down menu for k9 AND k10 and so on.
I am trying to create 4 pull down menus. Menus 1, 2, 3, 4. Menu 1 is the top menu, menu 2 will only show certain information based on what was chosen on menu 1. Menu 3 will show certain information based on what was chosen in 2 and 4, again, will only show certain information based on what was chosen in 3. Sort of how the menus on autotrader work, if you chose audi it will only show audi vehicles, although not for the same purpose, or even the same industry. I am using Excel 2007, it might matter. I have attached an xls file that shows the information I am using and below that the basic progression, I haven't listed it all, but you should get the general idea.
View 5 Replies View RelatedI have a very large database, filled with every job we're working on and all the details about it - duties, employees, vehicles, material-costs, etc.
I am currently working on a 1-page "Summary Sheet", that would be used to wade through all of this info and only give me the information I am looking for on one job. I would like to be able to provide drop-down menus on this "Summary Sheet" so that management can pick the information they'd like to see, and not be bombarded by everything else.
Ideally, what I am trying to do is to have a space on top where somebody could enter the name of the job, and then when they pick an item from the drop-down list, it would pull that info from the database via an "Index/Match". When somebody enters a job-name and picks "Joints Tested" from the drop-down, it will only display how many joints were tested on that job.
I already have the drop-downs created, and have other Index/Match formulas in other spreadsheets (they trip me up a bit, but I can generally fiddle with them until they work correctly), but can't figure out how to make it work this way. My database has headings above each column, and I'm writing those same headings next the different options in the drop-down list, but I can't figure it out.
I have two drop down menus: Class Name and Class Period.
Class name returns reading, math, science, or social studies.
Class period returns 1,2,3, 4,5
I need a formula for a cell that will return the name of the first student in whatever criteria is selected by the two drop down menus.
Example: If reading 2 were selected, I would want the name of the first student in reading period 2.
I did start out with nested If's combined with ands, but that is too many nested ifs. I figured there was a better or easier way to do this.
Formula I thought of: =IF(AND(G3="Reading", H3=1), '[Reading 8.xls]Sheet1'!$A6, IF(AND(G3="Reading", H3=2), '[Reading 8.xls]Sheet1'!$D6)) etc.....
But if I use that there would have to be 20 nested ifs, which I think I read someone nested ifs cap at 9.
I am wondering how to create a macro that will untick boxes as well reset drop down menus.
I receive inspection sheets from mechanics and I need to start tracking trends. So, let's say the mechanic has marked an "interior" issue and of the 10 potential items that could related to "interior", he has chosen "dirty upholstery" -
Column A is drop down menu with four categories listed:
Interior
Exterior
Transmission
Undercarriage
So in Column A from the drop down menu I pick the main category of Interior. Then it's on to Column B to choose the exact issue of the Interior. When I choose "Interior" in Column A I would like Column B to automatically show me another drop down list with issues that relate only to Interior (i.e. glass, dirty upholstery, torn upholstery, etc.)
that is question #1....
If there are multiple issues on one vehicle, I would then go to Column C, pick another category (i.e. Transmission) and then in Column D, I would like it to automatically display the drop down menu that relates to Tranmission issues (engine oil, air intake, engine wiring, etc.).
I am using the following code to create a custom command menu.
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error Goto 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")..........................
I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this.
Open Net 2 Access
Add Employee
Edit Employee
Delete Employee
Holidays (3 options on sub menu)
*New Control
From this link: [url]
there is some clever code to create a customized menu in a dynamic Add-Ins section of the Excel ribbon; this section disappears when the workbook is deactivated and re-appears when it is activated. The custom menu has 2 main items each of which can launch a macro. There is also a 3rd main item called "Next Menu" which cascades nicely to a sub-menu. That sub-menu can have several items of its own. Very cool code.
However, I am trying to add a 4th item in the main menu, say, "Next Menu2" which also cascades to a sub-menu. The problem I'm having is when I try to replicate this code to accomplish this, "Next Menu2" appears in the sub-menu of "Next Menu", rather than as a separate (4th) main item below "Next Menu".
I believe there was another post (not sure of the date, but has since expired) which asked basically the same question as mine. There was a reply with code saying "This should work" but when I tried it, it didn't help.
I want to create a submenu to an already existing user defined menu.
After creating and saving the sub menu using Tools ->Customize ,it works fine. But when
I reopen the Excel sheet it is not found.
I could not find any macro related to the original User menu.Also this menu gets displayed after the AUTO OPen macro in my excel has worked. This menu gets displayed for only that workbook and not for others.
I have been trying to find out a way to create Sub menu for that ,but could not?
How to get the number of menus present in the excel?
View 9 Replies View RelatedHaving recently started to get involved in Visual Basic I have managed to write some code that has removed all of my menus except for File and Help. In a blind panic I deleted what I think was the offending VB code in a vain attempt to return the menus. This didn't work. I am now stumped.
Also, I managed to change the way the Cell selection works when I press Return. When I press Return, Excel now selects the cell to the right of the one I was in rather than the one below it. Is there a way I can change this back?
I have a workbook which uses the code below to create a new menu upon opening. I have now used this same code in a second workbook to create a different menu. both work OK on their own However if by chance both workbooks are opened at the same time only one menu is shown. Is there a way I can change one of these to create a second menu if the first one is already there?
View 4 Replies View RelatedI have this code which adds one menu at the top of the worksheet and I want to add more. My current sheet has a large number of scripts liatsed under it for different purposes and I'd like to divide it into different menus.
View 3 Replies View RelatedI have a problem I am working on (see attachment for better understanding).
I have 4 sheets
1) quote
2) invoice
3) delivery note
4) products and settings
Now in the quote sheet I am trying to add a way of from a drop down menu, selecting the product from the products sheet and then a new line will be added automatically.
Then what ever is in the quote sheet is automatically copied to the invoice and delivery note sheet.
Ok, so I have this neat little macro that on right-click brings up a custom context menu that displays a few different options - for example one such function is simply copying the selected cell content to a (specific) different sheet. It is a very useful feature in the context I am normally using it to merge information from different locations in a new structure (well, I think this is irrelevant, but just letting you know basically what I am doing).
Anyhow, to implement the custom context menu, I am using events in the ThisWorkbook object, namely Activate to add context menu entries and DeActivate to remove them if switching workbook.
Now, if I receive any workbook from someone else, I'd like to add this feature to get the custom context menu easily without having to copy the received workbook into a new workbook (it's a bit of a hassle, and ideally, if the received workbook has any other macros, this function could just be merged). I was thinking using an add-in to achieve this, but using the ThisWorkbook events in the add-in doesn't trigger the events (I guess because the hidden workbook in the add-in is never "Activated" or "DeActivated").
I am trying to graduate out of the crayon age of menus on a worksheet, to using a User Form Menu. Problem is, I don't understand how they work. I've created a test program using a simple form with an option button, a combo button, and a command button. The goal is if the option button is true, it places the color selected in the combo button on the worksheet. Here is my code, what am I missing to make it work?
Corrected code from my original request
Module 1
Public ClrCd As Integer
Sub OpenForm1()
UsrFrm1.Show
End Sub
I have the following fields:
Manufacturer:System:Part Number:Material Description:
I want to make each field a "pulldown menu".
For simplicity, lets say I have three Manufacturers "A", "B" & "C"
Each Manufacturer has three systems "1", "2" & "3"
Each System has 100 parts "1-001" thru "1-100", "2-001" thru "2-100" & "3-001" thru "3-100". Each part has a corresponding description such as "Horizontal", "Vertical", "cover", etc...
I need to keep all of the manufacturers, systems, parts and descriptions in one excel workbook. I have no preference to how that information is built (tabular, list, etc..) I would like the workbook in which i want the fields above to be separate, so I only have to keep updating one master file that all of our takeoff sheets would be linked to.
when I select a manufacturer, I only want the available systems for that manufacturer to show up in the next pull down menu, then for that system I select I only want the available parts for that system to show up in the next menu. the descriptions need to correlate with the parts I select.
There was an article in ozgrid new about excel custom toolbars (<<hover over). The article addressed making the custom menu option available in one workbook with Private Sub Worksheet modules as follows
Private Sub Worksheet_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls _
("My Menu").Enabled = True
End Sub
Private Sub Worksheet_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls _
("My Menu").Enabled = False
End Sub
How do you get the first module to run apon opening the workbook and the second module to run when you close the workbook.
I have noticed that the submenus for the following main menu items are disabled
Edit Insert Format Data
The submenus for the other main menu items are fine.
I have not run any macros that alter the menus.
I have tried Tools>Customise Reset Menu and Toolbar usage. I then exit the Excel session, but the problem still remains,
I was reviewing the "Create Custom Menu Items in Excel VBA" code located at [url] and cannot figure out something. How do I add a menu dropdown that contains MORE than one submenu item? This is the code my question pertains to: ...
View 9 Replies View RelatedHere 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.
Im a fairly basic VBA user who has just purchased the code for J-Walk's Enhanced Data Form.
On other threads in this forum I have noticed a lot of people asking how to make the contents of one drop-down box dependant on the selection in another. I am trying to do the same thing but with the criteria section of the EDF form.
Is this possible? What does give me hope is that a simple Autofilter - whilst not as aesthetically pleasing, does the job well.
Currently I have columns fixed or assigned to the names of insurance providers/companies.
In each column below the name of the Insurance Company, a combobox containing the plans that company offers. Choose a plan, and the rest of the column is filled in using vlookup.
Now to save space, IE opposed to having 13-15 columns one assigned to each Insurance Company/Provider. I am looking to have only 3-5 columns with a first initial combobox in which one chooses the Name of the Company/Provider, and in turn populate the original combobox and vlookup functionality I already have built in.
Here is an example of what I am working with.
[url]
I am compiling questionnaires in Excel and need to constrain the answers. I've successfully used validate, list and directed it to a list (containing, for example, 'USB', 'Firewire', 'SCSI'. When I first set this up, the drop down menu appeared (and the prompt message I had created). After I copied the first worksheet to use as the base for my next questionnaire, the drop down menu disappeared, although the prompt still shows and the validation still works.
Have I missed something fundamental?
I've tried creating one on a brand new workbook and copying the worksheet and it seems to function correctly. Why has my drop down menu vanished???