I have some code that creates multiple new menu bars for a user which each have there own controls. I need to know which control is selected by the user for onaction. The example below is where there are up to 20 new menu bars with names Tract 1, Tract 2, etc. For each Tract, there is a controlbutton titled Absoprtion and I need to know which "Absoprtion" button was selected to run the "ViewAbsorption". I need to use th For next function because the number of menu bars could vary.
Dim objMenu As Object
Dim objViewMenu As Object
For y = x To 39
Name = "Tract" & y - 19
With Application.CommandBars("Worksheet Menu Bar").Controls
Set objMenu = .Add(Type:=msoControlPopup, temporary:=True)
objMenu.Caption = "&" & Name
End With
With Application.CommandBars("Worksheet Menu Bar").Controls(Name).Controls
Set objMenu = .Add(Type:=msoControlButton, temporary:=True)
objMenu.Caption = "&Absorption Schedule"
objMenu.OnAction = "ViewAbsorption"
Next Y
This is the code I use to temporarily remove menu barsfor a spreadsheet that is managed by user forms. I have code at the close of the program to replace the menu bars. The code work well although and it places a list of enabled bars in the range C1:C50 on the sheet "Bars". The range C1:C50 is not locked before the sub routine 'Hide Menus' runs. However after the sub routine 'Hide Menus' runs, the range C1:C50 is always locked and I don't understand why this is so, I would prefer it not to be.
Sub HideMenus() Application. ScreenUpdating = False i = 0 Worksheets("bars").Range("c1:c50").Clear On Error Resume Next For Each Allbars In Application. CommandBars If Allbars.Visible = True Then i = i + 1 With Worksheets("bars") .Cells(i, 3) = Allbars.Name If Allbars.Name = "Worksheet Menu Bar" Then Allbars.Enabled = False Else Allbars.Visible = False End If End With End If Next Application.DisplayFormulaBar = False On Error Goto 0 Application.ScreenUpdating = True Opening.Show End Sub
I have a workbook that I created as an interface for my workplace. I disabled the File, Menu, Edit, etc...menu bars through .Control("x").Visible = False and .Enable = False as well
Now when I open a new workbook all the menus are there still which is good but when I open a previously saved workbook all the menus are missing. Is there a way where I can have ONLY my workbook have the menus gone and for them to appear in other workbooks that were saved?
I was working on an Excel 2003 workbook with 3 worksheets. The first sheet is the 'Menu' sheet on which I placed buttons to take me to the other two sheets and back. I wanted to hide the Excel menus, toolbars, scrollbars, etc. on the 'Menu' sheet so that no user can alter that 'Menu' sheet. The 'hiding' code was not meant to affect the other two sheets.
The code worked fine. However, when I closed the workbook and tried to open a NEW workbook so that I could do some other work, I realised that the new workbook and its accompanying sheets had the menus, toolbars, scroll bars, etc. hidden. I closed the workbook and tried opening a new workbook. It was the same experience.
I called up the file with the code, deleted all the codes attached to that file, and re-saved the file. It did not solve the problem. And when I tried many more new workbooks and got the same problem, I deleted my entire Office 2003 installation and re-installed Office 2003. It still did not solve the problem.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
I am trying to derive the EMI for a Loan Amount and also chart a PIE graph for the same..
Instead of Manually feeding the values such as :
Variable 1 : Loan Amount : Min : Rs 50,000/- Max :Rs 5000000/- Each Spin (1 Unit) -Rs 25000/-
Variable 2 : Interest Rate : Min : 5% Max : 30% Each Spin (1 Unit) -0.25%
Variable 3 : Tenure/Duration : Min : 6 months Max : 30 years Each Spin (1 Unit) - 6 months
I want to have a Scroll-Bar where the values can change Automatically but am not aware of using the same...
Can someone please help me with a small attachment with the above variables attached to it..The values drawn should be displayed as well above the Scroll-Bar and maybe can make the Min and Max more dynamic by storing it somewhere in the Sheet..
I am looking for Non-VBA solution to do the same..
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
I created a right-click menu for userform textboxes from a code I found through googling. It works perfect, however, I don't know how to get it to work for more than one textbox.
I have a bunch of cells that use a drop down menu validation from a different set of cells so
Legend (this are in cells $A$7:$A$11) NA - Not Applicable 0 - Not attempted 1 - Not Yet Competant 2 - Satisfactory 3 - Exceedes Expectations
Other cells have Data Validation using Allow List =$A$7:$A$11 to make the drop down menu option appear. What I would like to do is add a 'hidden number' on cells A8:A11 (A8=0, A9=1, A10=2, A11=3) A7, being NA isn't included in the formula I want to set up.
In cells later on I'd like to make a formula that takes those 'hidden numbers' into account and works out an average. So 'hidden number' / 'potential total' and make the cell display as a percentage, the 'potential total would have to take into consideration that a value has been set, but not use that value, and times the number of values set by three.
I've got several add-ins that I'd like the end user to install and have accessible under one menu item ("Assessments") in Excel 2003. So far, the code below successfully adds the item, but it only shows the item of one add-in. How can I get it to show all the items under the "Assessments" category?
Private Sub Workbook_Activate() Run "AddMenus" End Sub
Private Sub Workbook_Deactivate() Run "DeleteMenu" End Sub
Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCutomMenu As CommandBarControl
is there any way in excel of making multiple pages with a drop down menu. Let say I choose 3 within the drop down, I'd like excel to make 3 pages (with the same settings of the first one) having a head/foot head pages. If you have a excel example(xls)
I just added a picture of what I actually want to do. I'd like to have the same design but adding pages between the top and bottom of the pages with a drop down or via an input box.. doesn't matter.
I am trying to a input a list of cells in a column depending on what option i select from a drop-down list (or validation list) eg. If in A1 I select "fruit basket" (from a drop down or validation list) I then want this option to input in Column B Items located in another dynamic range eg. Apple, Banana, Orange, Grapes.
I have attached a sample of what I am trying to do to help explain my situation.
I find these forums a great key to learning the intricacies in Excel.
I'm attempting something new, a dropdown menu using vlookup to find data . If i use the simple example below, using data from B-D on 1 worksheet, where i want to find people by county:
A B C D Search by County Name County Town John Sussex Brighton Eric Yorkshire York Jim Cheshire Manchester Liz Sussex Hove Sarah Avon Bristol
I've created a dropdown menu on a second worksheet,A2, by county search, to return a persons name,county and town. The problem is if i select Sussex i will get 2 names who live in different towns within Sussex. I've created a simple vlookup which works fine, but because of Sussex having 2 results, i created a second vlookup for the second result to appear in the cell underneath.
The problem is that for the results that only have 1 result, the second vlookup returns a the same result as the first result, which i dont want.
Is there a way where multiple results will be shown if they exist, but for one result to appear if only one result exists?
I have a workbook with multiple sheets. Is there a tool that creates a menu bar or code that I can add that would support navigation?
My goal is to have something with approximately 4 options (buttons?). They would link to 4 different sheets that serve as indexes within the workbook.
I know I can add a control like a button to a given sheet that would take the user to given sheet when clicked, but can I make the button 'float' above all the sheets, or add to a menu bar, or something? I just don't want to have to add the button to every sheet in the workbook.
I am looking for something simple/easy, and am not looking for anybody to do the work. I just have no idea on how to approach the problem.
I have a column with various drop down menu boxes and I need to add another selection to the menu list. My problem is, it's been so long since I did the drop downs, I have forgotten how to do it..
I'm adding a menu selection to the right-click menu that you get for cells. I can add and remove the menu option but, when I try to execute the macro tied to the menu, I get "the macro...cannot be found". I can't figure out what appears to be a "pathing problem". I'm sure it's simple but...
Now this is something which I dont even know if it can be done, but its something which I would use, a lot. When clicking on a button, like a normal button from the control toolbox, is it possible to open a menu from this? So I click on the button i made called "accounts" and this opens a menu next to it with different options.
my worksheet menu bar is displaying the ' Chart' menu no matter what I do. Not only do I have no charts in the workbook, (verified this by: )
For Each ws In Worksheets
ws.Select MsgBox ActiveSheet.ChartObjects.Count
Next ws
but adding worksheets, selecting various parts of a worksheet, creating a chart and deleting it, and everything else I've tried has no effect.
One interesting thing - the first chart I added (to test if it would 'unstick' itself upon deletion of the new chart) was named 'Chart 2', implying there was a chart1 that existed previously.... although I am fairly certain I never added a chart to this workbook at any point.
Also, the menu bar is working fine in other workbooks, and changes to chart and back to data like normal.
Suppose I delete a (unknown) number of items from the Worksheet Menu Bar. This can vary because of the fact that my excel sheet is being used by a different number of people, all with a different Worksheet Menu Bar configuration. Is there a way of counting how many Menu items are still left in the Worksheet Menu Bar?
So, suppose all that is left (after using the program) in the Worksheet Menu Bar are Excel, File, Tools, Help. How can the porgram find out that there are only 4 items in the menu?
I am taking an EXCEL(online) class and in my current assignment i am to create a 'list' for sorting and querying. However i cannot find the 'list' option, under my DATA option on the toolbar. I have tried all my toolbars and shortcuts but I am unable to find it anywhere.
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
I have a bar chart which compares two columns of data. the data in one column is fixed to 100. the data in the other column varies. a third column states whether each entry is "A" or "M". Is there a way to include the A or M on the bar chart? Perhaps by having a set colour whether the data is A or M?
What I need to do is add a scroll bar to my text box in my form. I used the properties of the text box to add a scroll bar but when you preview it you can't see it. When you click somewhere inside the form it appears and is workable but not when it hasn't been clicked on. How do I make the scroll bar appear from the start. this is a vertical one ad is on a text box within the form
FYI This is cross posted on the VB Forums at [URL].....
I know that you can use the below code to add buttons to 2 of Excel's menu systems,
Code:
Application.CommandBars("Worksheet Menu Bar").Controls.Add Application.CommandBars("Cell").Controls.Add
And this should add a command bar control of whatever type specified to either the Add-Ins tab of the ribbon or the right-click menu when clicking on a range of cells.
However, I would like to be able to add some command bar controls to the right-click menu when you click on a group of columns. Is this possible?
