I have a named range on a hidden sheet which populates data validation lists on 'sheet 1, cells b5-b55. My question is this...Is it possible to, upon entering a new item in b5 (for example), to have that item automatically added to the named range? (preferably through a button on a pop up form that opens w/ the question 'would you like to add to the list?, similar to an access form)...I've seen one example in the past which I found with a link from this site, but couldn't make it work. If the answer to my question is yes, could you also suggest a location of an example if you know of one?
In the attached workbook, I have identical sets of data in column A separated by an blank cell. I need a macro for the user form that i need to build. The code needs to find the end of each named range, (Range1,Range2 and Range3), to insert blank row at the end of each range and to copy the value of the textbox from the user form in each row so to keep the sets of data identical.
I have a COUNTIFS function that, among other criteria, only counts cells in which the value is "P". I now want to modify this function to count cells in which the value is "LA" in addition to cells in which the value is "P". I'm not sure how to work this into the argument. I tried using OR("P","LA"), but that's not working.
I'm trying to create code where a user can input criteria into a cell then run a macro. The macro would then use that criteria to filter the pivot table. I have attached an example. The first portion, using date to filter the current page - pivot item is working. I need to creating the code to use the "supplier" to filter the pivot items on the row. As a note, both the Date and Supplier cells are named references.
I want to set up a cell so that when the cursor is on the cell a drop down option appears allowing the selection of a yes or no to be shown in the cell. What's this called and how do I set it up?
I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
The below code works well. However I would like to give the users an option to opt out of running the code when the message box appears.
Code: Sub delete_sheets() Beep MsgBox "This will delete All Sheets but the active sheet"
*****this is where I would like to use an additional message box or include it in the above message box an option for the user to cancel this code****
Range("XA1") = ActiveSheet.Name ActiveSheet.Name = ("Delete_Sheets") Dim ws As Worksheet Application.DisplayAlerts = False For Each ws In Worksheets If Not ws.Name = "Delete_Sheets" Then ws.Delete Next ws
I have a userform with 5 listboxes on the first page, each with multiple options. They are all set up so any number of options can be chosen.
2 are populated by the 'add item' method, and the other 3 pull data from 3 columns of the database when it's initialised to ensure all possible options are included.
My question is this - as there may be many options in each listbox to scroll through, if someone wanted to search on all the options in the listbox, it would be easier to have a 'Select All' option at the top, but I have no idea if this is possible, let alone how to do this -
I have some old code I use to use that still works but instead of adding to the top menu, the new command button is added to the add-ins menu list and then burried with other items.
Public Sub test() Set myMenuBar = CommandBars.ActiveMenuBar Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, temporary:=True) newMenu.Caption = "Blotter"
I have an excel column that includes many numbers. I want to add them to the listbox. But there are same numbers in the list. What I want to do is to add each number to the listbox only once. Example:
This is what I have__________________This is what I want 622768____________________________622768 622768____________________________631215 622768____________________________635054 631215____________________________647461 631215____________________________681216 635054
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
I have added items to my "Cell" context menu but was wondering if there is a way to move one of the items to the Filter submenu. Below is the code I am using to customize the context menuI want to move the "Filter Status" item to the filter submenu.
Sub AddToRightClick()
Dim ContextMenu As CommandBar Dim ContextMenu2 As CommandBar
'This is for the normal view right click menu Set ContextMenu = Application.CommandBars("cell")
I'm trying to automatically add and remove one criterion from an autofilter without disturbing the previous existing criteria The autofilter code sounds like this:
How would I go about referring to this array and adding another item called "Hard" to any pre-existing elements in this array?
I plan on attaching this code to a button and use it as a filter, so when the button is pressed, the item will be added to the list of criteria, and if it is "un-pressed" the item will be removed.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB: Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then [valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
With VBA I need to add a rectangle to a sheet and have the shape named. They usually start out as "Rectangle 1", but I would like to set the name with the code.
I have 2 columns named "ASC" and "AE" which have total calculations of stores inventory data. To the right of the "ASC" and "AE" columns are store columns with (C1="store#"), (C2="state"), (C3="name"), and (C4:C14="inventory count") totals.
If at anytime a stores "name"="AE", I want the "inventory count" for that store to calculate within the the "AE" column.
Anytime a stores "name"="anything except AE", I want the "inventory count" for that store to calculate within the the "ASC" column.
A1:A3= "ASC" A4 through A14= Inventory Total
B1:B3= "AE" B4 through B14= Inventory Total
C1= Store# C2= State C3= Name C4 through C14= Inventory count
I keep losing my named ranges when attempting to reopen a particular file I am working on. I get the "excel found unreadable content....." error message and then when it corrects, the range names are all gone. is it efficient to create the named ranges at open? Otherwise how should it be done so that my workbook will definitely have these names each time its used?
I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.
1. I have a main worksheet that will have a number manually entered into a cell (lets say A1); 2. I have a 'template' worksheet; 3. I'll assign the macro to a button on the main worksheet
If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData() Dim counter As Integer counter = Sheets("Imported Data").Range("Counter")
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
I am using the data validation option on a range of cells.
On the 'Error Alert' tab I am checking the box for 'Show error alert after invalid data is entered', selecting the 'Stop' style of warning (which should completely prevent entry of invalid data), but the cells with the data validation still allow entry of invalid data. No warning is given either that the data is invalid.
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
