Remove Multiple Selection From A List Box After Transfer
May 24, 2006
below is an excel file with a UserForm.
The goal here is to select the worksheets in one list box and transfer it to another. Multiple Selection is allowed. I am having 2 question i am struggling with.
Question 1 -
When passing the sheet names from List Box 1 to List Box 2 (using Add button) i would like the selected items of List Box 1 to be deleted after transfer. I thought i could access this using the command below but it does not work properly -> error message.
UF_PI_LINK_1.LB_SheetList.RemoveItem UF_PI_LINK_1.LB_SheetList.List(i)
Question 2 -
When the user is done the ListBox 2 need to be stored. When I save then close my workbook and then reopen it, i would like to be able to access the selection that the user has made before closing the worksheet. How can I store the content of ListBox 2 to achieve such a result. I guess i could use a use a hidden Worksheet where i could store the selection into some cells but i would like to know if there is a pure VB solution.
View 3 Replies
ADVERTISEMENT
Dec 4, 2013
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
View 8 Replies
View Related
Feb 2, 2012
I have a list box in sheet 3 of my workbook containing a list of various vehicles and I want to be able to select certain data from it and transfer it to a single cell on sheet 2. Is there a formula for this. I tried entering the data into individual cells and then copying it to a single cell but it keeps pasting all cells seperately.
View 2 Replies
View Related
Jun 24, 2009
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
View 8 Replies
View Related
Dec 6, 2012
There are multiple training sessions that will be rolling out in Q1 13 and my manager needs a way to track which empolyees have been to which training sessions. Easy enough. He wants something extremely clean looking, but obviously efficient. What he described to me (his vision) is to have the training sessions listed into columns with drop down under each that contain every employee's name. So when a training session is held, he can easily click the drop down beneath the session title and select multiple persons that were in attendance. Right now I just have all the employees listed in column A and all the training sessions listed across the top and anticipated just putting a "x" in the box for the session that each person attends. This will accomplish exactly what he's looking for, but if what he's describing is remotely possible I would like to do that.
View 2 Replies
View Related
Oct 26, 2011
I want to filter a list. Within the filter I want to copy a selection of cells and hard copy the formula in those cells (copy paste special values). It is possible to selected the cell and copy them but one cannot paste on a filtered range. The error message I get is "The command cannot be used on multiple selection".
View 2 Replies
View Related
May 20, 2014
I have an input sheet with 3 columns: Currency pair, Tenors, and Value.
Like this:
EURUSD 1m 3
EURUSD 3m 0
EURUSD 1y 5
GBPUSD 1w 2
GBPUSD 1m 9
GBPUSD 2m 1
etc etc for about 100+ rows.
This input is exported from a system we have and may vary in length and values from time to time.
What I want to do is pull all the data for specific currency pairs (probably for 5 of the 10+ currency pairs it displays) onto another worksheet. And for each tenor for that currency pair, i want to perform 10-20 lines of analysis on the next sheet.
So, if GBPUSD was a currency pair I was interested in, I would need 10-20 lines for GBPUSD 1w, 10-20 lines for GBPUSD 1m, 10-20 lines for GBPUSD 2m, etc.
How can I pull this? While maintaining flexibility so if the tenors, currencies, and # of lines change?
View 1 Replies
View Related
Jun 30, 2014
I have created a Data Validation list in one of my spreadsheets which works fine. The list itself has no duplicates which is fine but when I select my entries in the other spreadsheet, I want to make sure they cannot select the same value twice.
I tried selecting "List" in data validation and using an AND with COUNTIF but it doesn't work.
=AND(Employees, countif($A$1:$A$20,A1)=1)
View 2 Replies
View Related
Aug 8, 2013
i have a list of 5 teams in a named range ("teams") within a worksheet.
On a userform I have 5 combo boxes.
What I would like to have is an easy way to remove a used name in the list for the next combo box.
i.e. someone selects team a in combobox1, combobox2 then has a list of team b,c,d and e. I'll be locking the combo boxes and writing code so the next one will unlock if the previous box is populated.
i also need this to work if someone puts team c in first combobox this then gets removed in combobox2 then in combobox2 they input team e then both these values are removed for combobox3.
only way I can think of doing it is creating a lot of named ranges and using a lot of if then code which I'd like to avoid.
View 2 Replies
View Related
Dec 1, 2009
I have a activex multi-selection list box (on a worksheet). It has two columns, referenced from two columns on the worksheet (state abbreviation, state code) AA7:AB58. I'm using the following code below to transfer selections to cell A1.
View 3 Replies
View Related
Jan 8, 2014
I need to transfer selection(s) from slicer Country to Country1 (they cannot be connected due to workbook functionality).
User can make a single or a multiple selection in the country slicer.
So basically using CUBERANKEDMEMBER I got the output from slicer Country (in cell A1 for now) and used it as input in VBA for slicer Country1
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]")
I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)
Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.
So, if the user selected 2 countries I would need to run a following code:
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]", "[01_Feed].[Dosage].&[" & Range("A2"]")
Now, is there any way to loop this within the array, how many cells it should take?
For i = 1 to ..
code from above
Next i
won't work because then it only takes the last value, so I kind of have to loop it within the array.
View 2 Replies
View Related
Sep 14, 2012
I have a workbook that has multiple functions. One of these function is that when the user select a key word from a drop down validation list, a VB code transfer all information from that row from Column A to column W to the next available row on another tab.
VB:
If Not Intersect(Target, Range("X:X")) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If LCase(Trim(Target.Value)) = "yes" Then
[Code] ....
Is there a possibility to transfer this information into another workbook. Instead of transferring the info to a tab name "Archives" to transfer in another workbook. This workbook will only have one tab called "Archives".
This will minimize the information within my Initial workbook in project.
The path in the new workbook where the information should be transfer should be in:
"C:Documents and Settings"username"My DocumentsMy Data Sources" to an excel workbook that will be named Archives.
I know that the "Archives" workbook need to open then close after the information has been inserted...
View 8 Replies
View Related
Jan 25, 2008
I have a macro that I run after selecting a column which removes spaces. However, it processes all 65536 rows and I only want it to process the cells with values. I need to enter into an input a column and then it just removes spaces from the cells with values
Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub
View 3 Replies
View Related
Jan 13, 2009
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
View 9 Replies
View Related
Sep 13, 2013
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
View 1 Replies
View Related
Apr 18, 2008
I have two worksheets. Both worksheets have two columns. Worksheet 1 column A looks like this:
1
4
7
8
Worksheet 1 column B looks like this:
x
x
x
x...................
What I'm looking for is a way to find that in worksheet 2 in column B a 'x' appears behind 1,4,7 and 8.
View 3 Replies
View Related
Feb 11, 2014
I am currently in the process of creating a manifest system.
I have two sheets I use (DELIVERY MANIFEST & COLLECTION MANIFEST)
What I would like to happen is, when I input information onto one fo these sheets and click the Macro button to send an email, that selected information gets automatically transferred onto the first blank row on another sheet (TRANSPORT SHEET)
The data I want to transfer is:
Manifest----LIST
J8 A1
F6 B1
G19 C1
J6 D1
H11 E1
C9:19 F1
E9:19 G1
J15 H1
For Column J on the spreadsheet I require the name of whoever input the data ( name of excel user)
COLLECTION MANIFEST.xlsm
DELIVERY MANIFEST.xlsm
TRANSPORT LIST.xlsm
View 1 Replies
View Related
Oct 28, 2012
Ok, so lets say i have one list of names (cell:value)
A1:Value 1
A2:Value 2
....
An:Value n
and create a new list
F1:Value 1
F2:Value 2
....
Fn:Value n
I want to have a list nearby and i'd like somehow for a function that will remove entries that already exist on the first list.
View 4 Replies
View Related
Feb 24, 2007
I have two lists in the same workbook:
List 1) Contains customer contact information, including an account number. These account numbers may be duplicated in the list.
List 2) Contains account numbers of customers who wish to be removed from the first list.
I need to remove the rows from the customers list (List 2) where the account numbers match, and also copy those to another list for review. So far, I can manually choose and run some code to remove one particular account number only (eg 123):
Set FoundCell = Range("A:A"). Find(What:="123")
'Locate information to remove
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
Sheets(" Deleted List").Select
'ActiveSheet.Next.Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select..................
View 3 Replies
View Related
Dec 5, 2007
I have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.
I want to remove all workstation numbers and their information which are not present on the first list from the second list.
In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).
In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.
View 9 Replies
View Related
Jun 20, 2014
I am creating a MIS for my team. But I am now struck while creating dashboard. I have a workbook with 12 raw data spreadsheets for specific months. Now what I want is that in 13th sheet if I select January then 1st cell in 13 sheet will show the value January cell 1 value. However if I select February then 1st cell in 13th sheet will show the value of February cell 1 value.
View 7 Replies
View Related
Dec 12, 2008
1) Numerous cells in columns A and B on a w/s have a combination of two fonts each.
For example, cell A1 shows # 15 on the Formula Bar. The # sign is in "Wingdings 3" Font and 15 is in "Arial".
Cell A1 correctly displays |15 (i.e.; up arrow,space,15).
2) Other cells on the w/s reference those cells.
For example, cell C1:: =IF($J$5="Vortex",A1,B1)
When the above condition is true: Is there a way to return | 15 (i.e.; up arrow,space,15) in C1 , exactly as displayed in A1 ??
View 9 Replies
View Related
Feb 26, 2013
how to Transfer data between worksheets using multiple criteria?
View 1 Replies
View Related
Mar 18, 2013
I am working with several userforms and try to transfer data obtained in one userform (lets call it Userform1) to another (Userform2).
Specifically, the idea is to have a commandboxA where the user can choose several options and the selection will define a string, and I want to use this string in Userform 2 to define a text.
Example:
The user chooses "solid" in the commandbox and I define the string as "rock"
Dim structure As String If commandboxA1.value = "solid" Then structure = "rock" etc...
And in Userform 2 I would like to combine the string structure with other strings, e.g.
If commandbox.value = "example" Then example text = "example text@ & structure & "text"
However this does not work, because the string which I have defined in Userform1 is not defined in Userform2. How would I define the string specifically, such that I have access to it in both userforms? How can I define the string as public? I tried several times, but could not get it to work.
View 3 Replies
View Related
Mar 25, 2007
I want to transfer the data of one worksheet (to be added everyday) to multiple sheets.
View 4 Replies
View Related
Oct 16, 2007
I import data into an excel spreadsheet where a unique job number may have multiple costs against it. These import as separate lines, repeating the job number.
The data imports on a sheet called 'DataImport'. From that sheet I transfer some of the imported fields onto another sheet.
What I need to be able to do is transfer the job number and a sum of the costs onto the other sheet.
The data is run regularly and I delete all the fields on the DataImport sheet in the macro each time the import is run.
View 9 Replies
View Related
Jun 12, 2008
I have two worksheets (Sheet1 and Sheet2).
For example I have three columns named Name, City and Telephone.
UserForm1 has three textbox(TextBox1, TextBox2 and TextBox3).
All three textboxes data populated from sheet1 (Name, City and Telephone) by selecting a comboBox.
The code is below... and which is working fine.. The problem is in my next code. I am trying to amend data in all worksheets but it is not working.
Private Sub ComboBox1_Change()
Dim strNamedRange As String
Dim lRelativeRow As Long
With ComboBox1
If .ListIndex > -1 Then
strNamedRange = .RowSource
'ListIndex starts at zero
View 9 Replies
View Related
May 6, 2009
I want to select items in a listbox and transfer those items via command button in a textbox. The listbox is already filled. I have no idea how to realize that.
Attached is the form I created so far. I copied everything together and matched it up for me. It's probably not the best way but it works. I marked the section where I need help in yellow.
View 9 Replies
View Related
Dec 23, 2013
I try to transfer a data from multiple worksheet in a same 2 identical workbook. One I keep for me and the other one will be update by the user. So I need the data update by the user can be transfer to my workbook and only the new data not the one that already in my workbook. I try with the code below but it seem not working. The code run but nothing being transfer.
[Code] ....
View 1 Replies
View Related
May 28, 2014
I am trying to automatically transfer data from nonadjacent cells (C1, B5,B10,B16,B22,B28) from multiple workbooks in a masterworkbook folder from A2:F2. I am a novice at VBA. I am not able to copy as Range("C1,B5,B10,B16,B22,B28") and the way it currently is coded only the last copied range (B28) is pasted to the master workbook. The data pastes to A2 in the master workbook instead of F2 where I want it. I need copying the cells from the workbooks into row 2 in the master bookbook. Here is what I currently have:
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:ToolFolderWorkObjectives")
[Code].....
View 9 Replies
View Related
