Order Selections Contigously
Dec 15, 2008
On one sheet I have a long list of items which I need my users to scroll down and, when needed, fill in a value next to the appropriate item in the list. Most of the list items will not need anything inputting against them. Then on a different sheet I need a nice tidy summary list which only shows those list items which have an input against them.
The bit I can’t do is getting these few items to display (on a separate sheet) in a nice neat all together, contiguous way - ie with no gaps. Also I need to populate this summary list automatically. ie after each entry.
View 2 Replies
ADVERTISEMENT
Jul 7, 2013
I am trying to take two random Mouse click selections and swap a set of ranges associated with the cells that are selected. For example if the user clicks on A1 it will resize(3,22) and store the selection as a variable to be swapped with another selection. Here is what I have so far but I keep getting a object required error on rngEmp1.
VB:
Sub SwapGroup()
If Selection.Cells.Count < 8 Then
MsgBox "Please Select two Groups to swap. Press and hold 'Ctrl' in between your selections", , "Swap Groups"
[Code] .....
View 4 Replies
View Related
Nov 29, 2008
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
View 9 Replies
View Related
May 8, 2008
I'm trying to use combobox1, say, department, to filter items listed in combobox2, say, employees. I don't want all employees from different departments listed in the combobox2 so as to make the user spend time looking, but only names of a particular department once that department is selected in combobox1.
View 9 Replies
View Related
Mar 1, 2008
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
View 3 Replies
View Related
Apr 19, 2006
I have a userform with multiple buttons and a listbox. When any button is clicked on, a listbox is created. The user selects various items on the listbox. Then the user clicks another button and selects other items based on a new list. If user goes back to click the original button, the original list shows up, but the selected items are not highlighted. Is there any way to keep those original selections highlighted (selected)?
View 9 Replies
View Related
Jun 13, 2013
What I'm trying to do is to select multiple groups of cells (say 5x4) which are below each other, but then paste them next to each other
For example, copy cells A1:E4, A8:E11, A15:E18 and paste them to A20:E23, A23:E26, A27:E26
View 2 Replies
View Related
Mar 26, 2009
how to track selections from a dropdown menu, I have 8 selections under the dropdown that are being selected from a list. Next to the list I want to have the total of the number of times it was selected on the dropdown. I've tried the data>subtotals, but It only seems to create 0's under the dropdown box.
I dont want the totals in the dropdown box I want it next to the list of 8.
View 3 Replies
View Related
Sep 18, 2009
I have not used Inputboxes or Userforms much. I have read through some of the messages here, but I need to learn more about them. I need to create some type of user interface, where the user would be asked to select between two choices. Based on the choice made, a macro would be executed. The two choices run different macros.
I need direction or an example of an Inputbox that shows two choices instead of a blank input space.
View 3 Replies
View Related
Oct 13, 2009
This should be very easy, but I am not getting it to work. I have a userform with a combo box and I just want to hard code the values to it in VBA. I want the value in the combo box to be 1 - 10. Here is what I have so far.
View 3 Replies
View Related
Nov 15, 2011
Any way to sort by more then 3 selections.
I want to sort by Date, Team, User, Item, then Sub iTEM.
View 9 Replies
View Related
Jul 10, 2012
We currently have a macro that has a limitation that it only works on 8 sizes, and only upto 12 Colours. We want to change this to make the number of sizes and colours totally variable. how to change this bit of code below so that the cells selected are not fixed but are variable depending on the number of sizes and colours as entered in sheet 2.
Current Code for When 8 Sizes and 12 Colours
Code:
Sheets("Sheet2").Select
Range("B30").Select ' This is Fixed
Selection.Copy
Sheets("Sheet3").Select
Range("G2:G109").Select ' Want this to be variable based on the number of rows needed starting at G2, this can be found out from Sheet2, Cell G28
ActiveSheet.Paste
[code]...
On Sheet 2 Cells G23 is used to enter number of Sizes, and G24 is used to enter number of colours. Cells G25, G27 and G28 then have formulas to work out other values that will be needed in the macro.
View 1 Replies
View Related
Jul 19, 2013
I am working on a macro, which should cycle through a column and find all rows with a given value. Then for these rows it should paste values from other columns into another worksheet.
Example:
1 2 3 4 5 6
Find all cells in row 6 with "yes" and paste the value from row 3 into another workbook.
Issue:
I cant seem to get the the value from row 3.
ActiveWorkbook.Worksheets(datasheet).Cells(rowtocopy, 6).Value.Copy has an error. Currently "Object required". Ive tried: .cells().value.copy that gives me a different error.
Script is started in the workbook where the extracted values should go.
Below its called "Template (2)".
The workbook with the rows is called "Datasheet" (which may be "Volume" or "SNiC data" depending on what is written in Worksheets("Template (2)").Range("B4").Value
Code:
Sub copychanges()
'################ EXTRACT ORIGINAL PAYMENT DATA
originaldata:
Dim currentwb As String, SuppXls As Excel.Workbook, datasheet As String, i As Long, targetcolumn As Range, modcorrect As String, fromcolumn As Range, rowtocopy As String
currentwb = ActiveWorkbook.Name
SuppPath = ActiveWorkbook.path & "" 'file path (assuming all file are in the same folder
SuppName = Worksheets("Template (2)").Range("D4").Value 'filename of corrected file
[Code]....
View 2 Replies
View Related
Aug 26, 2002
I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.
Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)
View 9 Replies
View Related
Jul 25, 2007
Sheets("Data").Select
Range("F5").Select
mycheck = ActiveCell
Sheets("Data in").Select
Selection.AutoFilter Field:=10, Criteria1:=mycheck, Operator:=xlcontains
xlAnd
The problem is that I have a value that is collected and put in to MyCheck. This value is only contains a few letters that I want the autofilter to filter.
e.i: The value i want to filter contains following.
01jan2007
What the autofilter is supposed to do is to show all cells that contains the value 01jan2007.
The cells that are supposed to be autofiltered looks like this.
2143 01jan2007 and so on...
I can not use "=" course the value is not the same as the others.
View 9 Replies
View Related
Dec 22, 2008
I execute custom Insert and Delete macros whenever when the user has selected a single contiguous range of rows to insert or delete.
But I don't know how to detect if the user has selected multiple, non-contiguous rows. Is there a way to detect this situation, and if so, is there a way to "loop" through the individual selections?
View 9 Replies
View Related
May 2, 2006
On a multiselect listbox, is there any way the user can select a "block" of selections, instead of having to select them one by one? The list box I have has about 100 choices and it would be possible to select blocks of choices at a time. This is similiar to MS Outlook email where you can push the shift key to isolate a block of emails to move to a different folder.
View 2 Replies
View Related
Jul 31, 2006
I have a workbook with 3 listboxes: Listbox1, Listbox2 and Listbox3. Selecting an item in Listbox1 will query a database which populates a hidden worksheet. Listbox2 then populates itself by drawing on entries in the hidden worksheet. Selecting an item in Listbox2 populates Listbox3 in the same way.
At the moment I have the spreadsheet so that nothing is cleared when I close or open the workbook, allowing the listboxes to remember what they were populated with when I closed the workbook. However, the listboxes will not remember what was selected when I closed it. Is this something which I can easily fix with a listbox property? If not, what is the easiest way for each listbox to remember what was selected when I closed it?
View 6 Replies
View Related
Sep 25, 2006
updating validation selections. I have attached an example of a bigger worksheet I use.
Sheet data: this sheet contains the raw- date. Data will be added and changed continiously.
Sheet pivottable: this sheet is used to sort the drowdown list and filter out double entries. A Dynamic named range is set to the pivottable
Sheet form: in this we will choose a code by a dropdown list and the column data 4 is filled by a vlookup formula.
If the data is changed in the sheet data and we’ve refreshed the pivottable, the code on sheet form contains previous data, so the vlookup formula can’t find the data (resulting in n#a depending on using a ISERROR formula)
The following solutions I’ve already tried:
- A userform with a lisbox/combobox in combination with listindex and an index formula. The problem is that when I sort the pivottable the index and data do not match.
- I also used the information in this thread but this didn’t work.
Maybe I have to change the construction of my worksheet, but it is important that it works.
View 8 Replies
View Related
Oct 9, 2006
I'm trying to see if it's possible to create a list box where I can select multiple values. For example, if a create a list box with the alues, "ant", "dog", and "cat". Is it possible for me to give the user the option to choose, "ant" and "cat"?
View 2 Replies
View Related
Dec 13, 2006
I have a form with 3 checkboxes. When any of the 3 checkboxes are checked, a set number of items are selected from a listbox. If a checkbox is checked, and then a new checkbox is checked, the listbox selection is cleared and selected with the new values from the new checkbox.
Problem: How can I code this so that if I check a checkbox and then deselect that same checkbox, my selected listbox items are cleared? Only when I select a different checkbox are they then cleared and repopulated.
Private Sub CheckBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim lngitem As Long
For lngitem = 0 To ListBox1.ListCount - 1
ListBox1.Selected(lngitem) = False
Next
CheckBox1.Value = False
End Sub..............................
View 2 Replies
View Related
Jun 21, 2007
Here is what I'm trying to create: I've put a command button on my worksheet that pops up a user form with a list box, an "insert" command button, and a "close" command button. I want to allow a user to make multiple selections in the list box, click the "insert" button, and have the selections inserted into the worksheet at a specific cell and fill down. I'd also like for it to allow the user to re-open the user form and add to the list after closing it.
I've hap-hazardly set up the list box for multi-select, but I'd like to see how it's "supposed" to be done! Also, as it stands, my list box selections will populate any cell that happens to be active at the time the initial command button is clicked. I've searched this forum and found that folks have posted similar questions. Those who replied posted a link, which I tried to follow but the page that followed could not find the file.
View 5 Replies
View Related
Feb 4, 2008
Is it possible to make multiple selections from the drop down box when using an autofilter?
View 2 Replies
View Related
Apr 22, 2009
Is there a way of selecting several print areas on several worksheets and printing it on ONE page? Selections will not be the same size.
Also is there a way to lock your printareas? I would like to save the spreadsheet and open it up next month and the printareas are still there ready for me to print. It seems to reset after i have printed it
View 3 Replies
View Related
Jul 12, 2013
I have created a named range that selects about 30 different cells (non-contiguous) and I am trying to copy over all the formulas and paste with values. However excel is not letting me to do this. It says this command cannot be applied to multiple selections. Can I get around this?
View 3 Replies
View Related
Jun 29, 2014
sample.xlsmI am trying to create a sheet with dynamic lookup based on selections.
So if a user selects planning or costing and then the state then click on search then it cell B13 it should lookup from data sheet and give full state name and in cell C13 give the document name and in D13 give the print rule.
If in the data sheet the document type says planning/costing and there is no corresponding state to that document then it should be incliuded in all searches.
When I click clear then it resets the search. So if i select Planning and state as AL then the display should be
Alabama ABC DEF
A12 AA1 (aligned to previous row)
C12 AA3 (aligned to previous row)
E12 AA5 (aligned to previous row)
Also is it a better option to do planning and costing as a radio button or drop down list. Is there a way in the state selections to show the drop down list arrow at all times. Currently when the cell is not selected it disappears.
View 10 Replies
View Related
Mar 13, 2014
FFLX9g.jpg
What I'm after is an easier way to get data combined without having to type it all out. If you look above I would like to have A-C actually as dropbox's or listbox's, something I can click, then drop down to the proper name and select it. Then on E I'd like a timestamp for when this action was done with the date and time as shown along with the reason. Then in F I'd like the Name fields combined so I just need to right-click on F and copy/paste into the other program I use.
Maybe one column can just be the names of everyone with a selection box and I click this stuff then when I am done it resets?
View 1 Replies
View Related
Jan 2, 2009
I'm trying to create a macro that will copy and then paste a range of cells to another worksheet but in one row. The cells that I would like to copy are in 3 different rows. They are B7:F7, C12:D12, C17:E17. Now I've managed to find a macro through this forum that will paste the particular cells but they I can't seem to find a way to record a macro that will paste them in one row. In addition to that I also tried copying and pasting each of the ranges of cells individually it kind of worked but it came up with an error box.
The problem is, even if I can find a macro to paste those cells it has to be able to paste them underneath the previous row. So e.g it should paste the cells in row 17 and then when i press the command button again it should paste them to row 18.
View 2 Replies
View Related
Jul 23, 2009
I have a MultiSelect Listbox with two columns of data and I want to enter the listbox row data into different cells of a sheet. For example, the selected listbox first column data to be entered to Sheet1, activecell and rows below (for other multiple selections), and the selected listbox second column data to be entered to Sheet1, activecell(0, 2) and rows below (for multiple selections).
View 2 Replies
View Related
Feb 20, 2014
I have a very vast code with several subroutines based on selections from a userform.
I believe I have identified a loop that might be slowing down the process in the below:
Code:
Dim n As LongFor n = 23 To 65
If ThisWorkbook.Worksheets("record").Cells(ComboBox2.ListIndex + 3, n).Value = "INT" Then
UserForm2.ListBox2.AddItem ThisWorkbook.Worksheets("record").Cells(2, n).Value
End If
If ThisWorkbook.Worksheets("record").Cells(ComboBox2.ListIndex + 3, n).Value = "EXT" Then
[Code] ......
Basically the code will go through each value (that can be only of those four instances) and put the title inside a different listbox.
This works, but it seems to be maybe too "step by step" and direct? Is there a way for it to skip after it found the corresponding value to the next N without checking for a match with the other items?
View 1 Replies
View Related
