I have the code below that runs the macro BUYSELLSORT, this runs as soon as I click in the relevant cell BEFORE I have chance to enter new data into that cell. How would I alter it so that it allows me to select the relevant cell,enter the new values in that cell and THEN run the macro.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row = 3 And Target.Column = 58 Then BUYSELLSORT End If End Sub
I have a time entry spreadsheet where I total hours worked based on the time code entered in the cell next to the hours. I use a formula such as the following: =SUMIF(F11:AS19,"=AN",E11:AR19) which checks cells F11 thru AS19 for the timecode AN and when found, takes the hours in the cell before the code and adds them to the totals way off to the right.
The problem is that when someone does a cut and paste to the time data within the range of F11:AS19 and pastes it somewhere else, like to the next employees data (no formulas are being cut and pasted, just the detail data), the formula for that new line automatically changes to exclude the cells that were pasted over. It is as if Excel thinks that you pasted over that data so it is no longer of interest to you.
It is odd because you can cut and paste smaller ranges within that range with no effect to the formula but if you include the first column or the last column of the range in your cut, then the formula gets altered. If you cut and paste the entire range, the formulas all change to #REF!
I've created a sheet and associated VBA codes and userforms for call logging in my department at work, which works beautifully at the moment, but I could do with some help with the next step.
I've a listbox at the bottom of the main userform which lists all calls received by my department, but I'd like to be able to filter this for either open, held or closed calls but can't seem to get this to go.
I was hoping that I could simply filter the worksheet it came from and it would affect the listbox, but this doesn't seem to work. The only examples I've managed to find of anything similar are a little too complex for me to work out what does each bit.
I have a list details in ranges "CV:DC" column CV has date entries so with the code given above i just select the date from combo box and check the details by pressing command button..Actually this code is doesn't work with combo box but it works with textbox except date data entries in this mode. So how can i improve this option if im going to use it in combo box.
Private Sub CommandButton26_Click() Dim a, i As Long, ii As Long, b(), n As Long ListBox4.Clear With ComboBox6 If .Text = "" Then Exit Sub If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then MsgBox "Bad Data" Exit Sub End If a = Range("cv1", Range("c" & Rows.Count).End(xlUp)).Resize(, 8).Value For i = 1 To UBound(a, 1)........................
aboute searching big database (10000 rows) with names of streets for one town. Each day I get list with 50 address and I need to look in database to see details aboute street in wich is address. I was thinking of user form with listbox for list of 50 addresses and then use first four letters of that address for advanced filter. I'm stuck with code for that four letter thing. When I see the desired information I need to insert it to 50 address table.
I'm trying to use 2 combobox's to filter a listbox with 7 columns. I need to match the 1st 3 chars. and the last 2 chars of the 1st column field to filter the list that displays in a listbox on a form. Is there an easy way to do this? I tried working with the autofilter, but couldn't get rid of the dropdown selection arrows.
I'm running reports in one workbook on different types of spend for 12 business units using 1 worksheet containing the raw data, and 6 other sheets with pivot tables showing different levels of detail.
I've used simple VBA to prepare to standardise the raw data and refresh all the pivot tables with no problem
I need to filter the data to show spend for specific business units. I'd like to do this using a listbox showing the units where I can select the unit(s) to report on, which in turn will only make the units selected visible on each table in the workbookwith no other intervention.
There is another thread on here (showthread.php?t=55041) that shows how to use the list box to control 1 pivot table on one sheet but I can't find anything which shows me how extend this to cover multiple tables over multiple sheets.
Attached small application. Open the application and click on the LISTBOX button. Code for the Filter by Item button or the Filter by Representative button. I would like to select an item from either of those dropdowns in the search box, click on the relevant button and the list box will populate to show the results.
For example, if I were to select Chocolate Bars from the dropdown and click filter by item, I want to see only the three lines [i.e. line 2, 6 and 7] present in the listbox, and I want to be able to doubleclick on any of those lines to go to the record if I wish.
Similarly, if I select Robert from the other dropdown and click Filter by Representative, I want to see the relevant three lines [i.e. 4, 5 and 8] relating to Robert, present in the listbox, where I can again double click to go to the record [i.e. the data entry userform related to particular record selected.
I have a list of customers in listbox1 (the text values of which come from a spreadsheet), that I want to refine as I type in textbox1. I'm not sure if it's possilbe, but I would like it to work similar to the itunes search (if you're familiar with it) where it searchs for any occurance of the text within the list as opposed to just searching for the letters at the beginning of the word.
I have a Pivot with a Userform containing 3 cascading Listboxes, each listbox fills down to the next. What i am trying to do is have the result of the listboxes to filter the Pivot table. Keeping it simple for a moment, in listbox1 user has a list of Departments and clicks "Liquor" then the Pivot should only show items within the "Liquor" departments. How do i achieve this? Also when looking at other treads and seeing the code offered, should i be trying to filter the Pivot Table field in the Page or Row area?
I have a line that is commented out in the bottom - that is an example of what types of filters I want to do but dynamically. Users choose multiselection from a list box then it filters the sheet for them. It is pulling the data from the listboxes but when it is more than one item it is Putting the whole thing in as if I said text = "ABERTI", "AMALON", "BCASTE", "BGELLE", "CFRANC" - No I want to filter by each of these (it believes this is one long string)
I have a spreadsheet in Excel, there are 13 columns of information being used. 3 of the columns have just data I typed in (model name, item code, original price) the other 10 have formulas (these formulas are price discounts that will be taken off of the original price. 9 of them also have a check box on the top of the column so if the the checkbox is selected, the formula will give the customer the amount discounted off the original price(keep in mind that not all of the columns can be used together, for example, on product "A" maybe only 3 of the boxes can be used whereas on another product maybe 5 can be used). I made a multiple listbox, so that if a customer selects a product or multiple products and clicks the ok button, the sheet will only show the specific products they selected. My problem is that when I press the "OK" button nothing happens. I don't know how to link all of this together.
I'm trying to Filter a list by the criteria selected in a listbox, and activated by a command button. My problem is the sort criteria. I have 11 regions, numbered 01EPS through 11EPS. Sorting by any of these criteria works just fine.
The problem is that I also want to be able to sort by ALL of the regions at once. I can do this by using a custom filter. I recorded a macro to see what the difference was, and it simply a matter of changing 01EPS to "=*EPS". However, adding that to my case list results in an error message of "Compile Error: Syntax Error".
List 1 (TV Brand) Sony LG Samsung Depended List 2 (Models) Sony LG Samsung EX420 55EB9600 PL43E450A1FXZP EX430 77EC9800 PL43E490B4FXZP EX550 55EA8800 PL43E400U1FXZP EX520 KN55S9C UN32EH5300FXZP EX645 55EA9800 PL64E8000GFXZP
I'm using two Listboxes (Form Control) with multiple selection options namely Listbox 1 (Brand) & Listbox 2 (Models). I want listbox 2 input range to be depended on selection made on Listbox 1 (Brand). For example, if user selects Sony then box2 should show only Sony's models and if user selects Sony & LG, box2 should show models for both Sony & LG.
I have a userform where I can select multiple items in a listbox and add them to another. I also have the ability to filter the first listbox to make finding items easier. The issue I am having concerns the clear filter button. As currently designed, the clear filter button will reset the initial listbox back to its default values. Ideally, I would like it to reset to the default values excluding those values that currently in the second listbox.
The entire code is below for reference, but it's the sub ClearFilter_Click that I am struggling with.
im trying to create a two column listbox that will transfer both columns to the listbox on the right and also transfer from the right to left currently right to left works but when I trasnfer from the left to right then the right to left only one column is moved.
I have 2 userforms. UF1 and UF2. UF2 has a rowsource set to its Listbox. UF1 has a search function that searches the original sheet. Now I want to double click on an entry in UF1's Listbox and select the same entry in UF2's Listbox. I want to then work with that entry in UF2.
I do all of this to circumvent Excels restriction. I can't search in a rowsource Listbox, but any edits done to my new Listbox wouldn't be made to the Excel sheet.
I have the following sheet which functions as a table to store values for files that have been created using the application which this table is in. In this app., I have a form with 2 listboxes. When the form loads, I have the first listbox list values which each of these files are listed under (i.e. - "sub-directories"). With a selection of one of the list values and clicking of a button, I want the second list box to list the values of cells listed in a range directly below where the selected value in the first listbox came from.
I'd prefer, in the first listbox, to have only the values of the ranges that have a value in them in the listbox. However, this would cause my listbox.selected(array) not function properly. But since my current offsets (in the second sub) do not seem to be working anyway, maybe I am going about this totally wrong.
I have 1 listbox (lisbox1) that retrieve it's list items from a worksheet range (imported/database query from access). This works fine.
I have a second listbox (listbox2) that should display results from clicking a value in listbox1. Listbox1 contains companynames (1 column), listbox2 needs to be populated with quotes.
Range A3:D4800 contains company ID's, Company names, Quote Numbers. When I select a company name in listbox1, I need listbox2 to be populated with all quotes for that company.
I have tried (using vba) to do a vlookup using the listbox1 value, but I cannot seem to figure out how to populate listbox2 with "all" quotes. I get 1 quote and that's it. I realize I probably need to have the vlookup loop through each cell in the range to find the value, but when I try this, I get a type mismatch when using the .additem (only for the 2nd and subsequent passes).
I cannot find this information anywhere else in this forum...
Does anybody know how to transfer an item from one list box to another using code, on the click of a button. The list box with the information in is called 'Team_ListBox'The list box i am wanting to transfer to is called 'Starting_Team_ListBox'The button to do this task is called 'AddPlayer_team_Btn'
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
Database = the named area of raw data. DATA is the name of the raw data worksheet The criteria range should be AZ1:BC3, but of course royally breaks it...