I am trying to move items up and down my listbox2 I have the code below however there seems to be an error with the moving down button
Option Explicit
'Move ListBox Items code
Private Sub BTN_moveselectedLeft_Click()
[Code].....
I have 2 list boxes on a from with data in each list. I've been able to get items to move from listbox1 to listbox2, but I want the item to be deleted from listbox1 once it's moved...and vice versa. Here is the code I'm using. .
Private Sub DeleteButton_Click()
If ListBox2.ListIndex = -1 Then Exit Sub
If Not cbDuplicates Then
[Code]....
One can create additional menu items for the right click, and these will show up always at the bottom. Is it possible to force them to appear at the top of the menu list above cut? This is specific to Macro created items.
View 4 Replies View RelatedI am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side.
There's a space with each route for the employee ID to go, depending on who's doing it.
Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.
On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.
I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.
This is about as far as I've managed to get with it.
I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.
I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.
So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.
I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.
scheduledemo.xlsx
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 RelatedI Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View RelatedI have a list of names in B8:B200 (unique)
I have another list of names in I7:I15
I want to count how many names in B8:B200 that matches any of the names listed in I7:I15.
I have extracted a report from a web package into Excel - basically a list of documents held on our website. The Cells in Column B would have the word Title or Description, Column C would have the actual title of the document, Column D would be blank on rows with Title in Column B and Column E has a Document ID (DocID)Number. To make this easier to read is it possible to filter Column B using Title and then move the DocID number from Column E to Column D.
I have attached a shortened version of the spreadsheet with the filter applied in Column B already. The real spreadsheet is 1000 lines long and would take quite a while to drag and drop each DocID number from Column E to D. I assume this can only be done with VBA.
I need to identify values which are less than 0 and move those cells to an adjacent cell.
Acct Desc Amt
2100 Acct1 -10
2101 Acct2 10
The -10 cell will move one cell to the right. I have an imported list with gl accounts, descriptions and amounts and want to have the negative values moved to a new column.
I would like to calculate the moving average for the first ten rows in a column where each new entry is added to the cell above the last entry.
For example
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
Therefore in the current list the average is AVERAGE(A5:A14)=6.5.
The next entry added to the list will be in cell A4 making the list look like this
A4, 1
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
The average of the first 10 cells is now AVERAGE(A4.A13) = 5.5
I would like to enter one formula in say cell A1 that will calculate the moving average in the first ten cells each time a new entry is added. Naturally as needed, new rows are added below cell A1. I hope my problem is clear.
as I have been unable to solve it myself. I have tried using offset but have been unable to resolve the issue.
I have been working on a few different spreadsheets, and I have gotten almost everything to work. There are just two things that I just can't get right. Here is the first problem:
It seems to me that I should be able to change where the list will display where it says: sh.Range("a10").PasteSpecial xlPasteValues
I change the range to anything in column A and it works, but If I change the column, the entire script seems to run in one row, and the days of the week don't move.
Code:
Sub Workbook_Open()
Range("a2:d300").FormulaR1C1 = Clear
Dim sh As Worksheet, dyArr As Variant, rng As Range, c As Range
Set sh = Sheets("Summary")
[Code] ....
Now the second issue, I need to know how to set the array to look at every single worksheet without listing every sheet. Here is an example:
Code:
dyArr = Array("apple", "explorer", "firefox", "google", "java", "safari", "templates")
Now, in the future I might need to add a worksheet in between google and Java, and I don't want to have to change the script every time.
I had this code working on a Range in a Worksheet. I decided I needed to move the range to another worksheet. I moved the data to column AA in a new sheet called Audit_Pool and renamed the sheet name in ThisWorkbook.Sheets, but I get a runtime error at the With Range statement.
With ThisWorkbook.Sheets("Audit_Pool").Range("AA:AA")
With Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
Randomize
With .Cells(Int(.Rows.Count * Rnd()) + 1, 1).Resize(1, 2)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
what I missed when I moved my list to a different sheet?
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
I have sheets with list of goods and in the last column I have a dropdown box you can choose - book in or booked out. See attached file.
On another sheet I want a list of all the items that has been changed to booked out.
I have a list of items in a column. I would like to have a userform pop up with a list box (?) of all the items and I want to be able to select multiple items to perform an action on the row corresponding to the selected item.
I am thinking of other examples I have seen where there are two list boxes (?) and then arrow buttons between them. When you click the arrow the item moves from one list box to the other to see which have been selected.
I have never used list boxes before so if you have a link to some sample code that would be useful.
I have the following code used to add items of expired IDs in a listBox,, it actually works well for adding the ID type, but it doesn't add the details of the expired ID completely .. It adds only the details of the last expired ID (I think the code overwrites the details)
I hope that my problem is clear for all,
Can you please find me a solution for that?
Hi all. I need to create a dropdown list that can handle more than the standard eight rows that data validation allows. My list requires 75 different items in the dropdown. Is there any way that I can do this?
View 14 Replies View RelatedI built a form with a listbox that is populated from a list of products on a worksheet. I want the user to be able to type in a product and if it doesn't match the products already listed I want it to add it to the worksheet list of available products. I keep thinking that I should form an if statement within a loop but whatever I try doesn't work.
View 3 Replies View RelatedI have a list 10 of names in Column A. ( On sheet 1 )
I then filter column A to show only three specific names, or four names etc. . .
On Sheet 2, in cell A1, I would like to list the three filtered names from column A on sheet1
e.g. if the three names showing in column A ( Sheet 1) are: john, mary, jane . . . then on sheet 2 cell A1, I would like to see: "john, mary, jane"
I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.
View 9 Replies View RelatedIs there a way than I can take a list of data and create a new list but only listing each value once if there is a duplicate value within that list?
Original List
=========
Apple
Pear
Orange
Orange
Pear
Apple
Peach
Plum
Pear
New List
======
Apple
Pear
Orange
Plum
Peach
I'm new to the forum - I've done a search but cannot find the answer to my problem.
I work in a hospital and have a list of consultants who refer for a particular type of scan over a period of time.
Each time the consultant refers their name is added to a list so I get a list like this:
Dr Smith
Dr Smith
Dr Jones
Dr Smith
Dr Paeker
Dr Paeker
Dr Jones
etc...
I have a cell "project titles" entered in sheet1 that populates into sheet 2 - column "Project titles".
I need to keep the values in this column unique. So can I create a data validation such a way that I won't be able to enter values into this cell that are already on the list. This way I can avoid entering duplicates.
I basically have lots of lists of 12 cells. how ever some of those lists contain errors (e.g instead of having 12 numbers it will only have 10)
so instead of doing =sum(a1:a12)/12 i want to divide by the amount of numbers in that area in this case 10 so i can get an average.
I would just like to know if there is a way to add items to a drop down list that cant actually be selected.
Asking because I have a table from which the dropdown list gets populated and the user then selects a particular row but would like the header fields to also be added to the list just for reference.
I have attached the spreadsheet in question for reference.
I have about 145+- employees and I made a simple spreadsheet that allows me make a timesheet for every employee. However, I do not want to sit here and select each employee's name and press print 145+ times. Is there another way to do this?
All I would like to do is press print or run a macro and 145 sheets pops out. I have to do this weekly and it would take me an hour to individually do this.
I am looking to color code items in a list box called lbActiveItemList, is this possible? If so I would like it to color code based on the value in the 3rd column as follows:
If the value = 'Receive' Color code the item line as Black
If the value = 'Return' Color code the item line as Blue
If the value = 'Relocate' Color code the item line as Green
If the value = 'Lost' or 'Damaged' color code the item line as Red