I have a column that is updated several times a day, I need to be able to view that list in a userform combo box and that box to be able to react like an autofilter list. I've tried the advanced filter thing but as th elist is contantly updated it makes this very hard to handle as I have to set macros to compile the list and then delete it once it's been used. Is there a way I can set vba code so the ComboBox populates unique fields, sorted AZ and removed the blanks without using the advanced filter..?
I have 2 columns of data on Sheet2. The first is Product in column B. The second is Benefit in column C.
I need to look at product column B on Sheet2 and add unique items to my first Combobox after the command Userform1.show.
The person using the form will select a product from Combobox1 and then Combobox2 should add the corresponding Benefits from the benefits column on Sheet2.
As a side note: I expect products and benefits will continue to be added to columns B and C in the future so I can't have a "hard-coded" set of rows if that makes sense.
I've pasted a small 2 product example of Sheet2 below.
ProductBenefit Account DefenderLoss of Life Account DefenderDisability Account DefenderInvoluntary Unemployment Account DefenderLeave of Absence Account SecurityLoss of Life Account SecurityDisability Account SecurityInvoluntary Unemployment Account SecurityLeave of Absence Account SecurityHospitalization Account SecurityNursing Home
Centre Name Training Type Expert 1 Expert 2 Expert 3 Expert 4 MyCentre MyTraining Me MyCentre MyTraining Me MyCentre MyTraining Me MyCentre OtherTraining Me NewCentre NewTraining NewPerson
I am trying to create a user form (in VBA) with a combo box that has each centre name appear only once (despite the fact that in the database each occurs multiple times). When a specific centre is selected, this brings up all the different training types associated with that centre in a separate list box. When that training type is selected, it should bring up all of the experts in that training type (for the specific centre). I should mention that the range is static (though the data is always contiguous) as the database is updated on a going forward basis.
I have five comboboxes in userform1, they are link to Column A, Column B, COlumn E, Column G and Column Z of "Reference" worksheets respectively. The values in each column could contain duplicate values. However, I'd like to only display the unique values in those combo boxes and I have no clue how to achieve this.
Populate Adv Filter Criteria from Inputbox with valid data
I've set up a macro to extract 3 different sets of data using Advanced Filter (same data range, different criteria & extract ranges)
There are multiple options (different individual dates) to satisfy the criteria (a date) for each of the 3 extracts and I want the user to select the criteria (a specific date) from the range of unique values in the data range upon which the Advanced Filter criteria is going to be applied. (A bit like the effect provided by Autofilter)
My initial attempt was to capture the criteria date from the user and populate a cell, and then have a vlookup function in another cell check if that crieteria value exists in the specified range. I then ran a loop in the VBA code until the value is valid (ie. exists in the range). However, to streamline this I was hoping to allow the user to see a list of the valid (available) dates when they respond to my Inputbox request for the criteria.
i have a list of surnames in column A in a spreadsheet i want to use this for the source of the combo box also i want this list to be unique and if the surname is not present i want to be able to put the name in the combo box and add it to the sheet
I need to create a drop-down (or a combo list) list based on a column in a separate worksheet (in the same workbook). The values in the column have many duplications, and I simply want the combo box to return a unique list.
After trying this using various options like drop-down boxes, combo boxes etc it does not seem to work. A drop-down box will return a complete list (including all of the duplicates). In addition the drop-down was linked to a cell and the value returned to the cell was a number based on the position in the list when what I actually wanted was the text. For example - my list in Worksheet 2:
PETS Cat Dog Dog Cat Horse Rabbit...
In Worksheet 1 if I choose 'Dog' from my Combo box (or drop-down) along with showing all of the duplicates it returns '3', instead of what I want 'Dog' and no duplicates.
I have a user form with a group of several option buttons. When an optionbutton is selected, programatically, I want a worksheet's named column to be parsed through to find unique entries and then have those entries passed to the form's combobox. I've spent the last hour searching for an answer but to no success
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData() Dim Lrow As Long, test As New Collection Dim Value As Variant, temp() As Variant ReDim temp(0) On Error Resume Next With Worksheets("Sheet1")
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue() With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value) End With End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$A:$A")) Is Nothing Then Call FilterUniqueData End If End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
Is there a way to have 2 views of the same sheet that will enable either user to only see their own requirements (columns, row)s but that will update the overall worksheet. The users would not be accessing simultaneously. Complex sheet so just need to have user access to a subset of data.
I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot.
Right now, I've this pop up box which prompts users for passwords and user name for log in. They are two main users : Admin and User. Admin is the user which is allowed to see all sheets. While User is only restricted to user interface sheets. The problem is for the "User" I want to restrict a view in the sheet "ELEMENT". I wish to hide columns L to AI if "User" logs in. But if "Admin" logs in, I want no columns hidden. Everything must be visible.
Private Sub CommandButton1_Click() Sheets("Main").Select Dim strUser As String, strPword As String, strWs As String Dim w As Worksheet, c As Range, r As Range strUser = Me.TextBox1.Value strPword = Me.TextBox2.Value Select Case strUser Case "User" If strPword = "User" Then Sheets("SMXINVENTORY").Visible = xlSheetVisible Sheets("SMVINVENTORY").Visible = xlSheetVisible Sheets("SMIINVENTORY").Visible = xlSheetVisible Sheets("SMF1INVENTORY").Visible = xlSheetVisible..............
I'm dealing with a spreadsheet that uses custom views. Before I do anything with it, I want to make sure that there is no custom view enabled (e.g. just display all). I've tried Chip Pearson's ThisWorkbook.CustomViews("View1").Show suggestion, but it just throws "Runtime Error 5: Invalid procedure call or argument". My actual code is: ThisWorkbook.CustomViews("All (Est)").Show
I have a worksheet set up with a number of pivot tables to show job summaries for each project manager. I created buttons labeled with each project manager that trigger a macro to filter the results to only show jobs for that specific project manager. There's one button for each project manager.
I'd like to create a macro that will essentially show each project manager's report and compile the result into one PDF. I only have 5 project managers so I don't need an array or anything if that makes it easier, I could write the code manually for each one.
So basically, it would do this:
1. Run macro to filter results to only show Project Manager #1 jobs. 2. "temporarily" print to PDF. 3. Run macro to filter results to only show Project Manager #2 jobs. 4. "temporarily" print to PDF. 5. Run Macro for Project Manager #3, etc..... ............... Final step. Compile all the "temporary" pdfs into one file.
I have created a report with 22 sheets as different pages to be included or hidden depending upon the type of report needed. I had found several sources saying to use the Custom Views function. So for example I will unhide all the sheets and add as a Custom View "All Sheets" and then whittle it down to the 5 I might need for B type inspections and add that view as B Inspection. But when I try and toggle between them I'm getting an error message that says "Some view settings could not be applied" - and thew saved view I was looking for is not the same sheetwise.
I am trying to use a checkbox to control the custom views that i have set ("Normal", "Hide") However after i entered it in VBA, it just does not work. After i check or uncheck the checkbox, it just keep going back to the "Normal" view.
Sub CheckBox1_Click() If CheckBox1 = True Then ActiveWorkbook.CustomViews("Hide").Show Else ActiveWorkbook.CustomViews("Normal").Show End If
I've posted this on Experts Exchange too, specifically trying to target the SQL Server experts. http://www.experts-exchange.com/Soft..._24423637.html
SQL Server isn't really my thing. I have been asked to quote on a job to build some models. One of the components is to pass parameters to sql server views and return the data into a table in Excel. I never done this.
1. Is it possible to pass parametrs to a view thru Excel? 2. Will the view execute based on the users permissions (different users using the Excel template have different access rights)? 3. Any other things to consider?
I don't need specific solution, only really some broad advice. I really only want to know how feasible this is...
I have a large spreadsheet that I am managing and there are many users who access this for information. Because of this, we have all created "custom views" for our departments.
I continue to struggle with how to save changes in these custom views. Yesterday, I added and moved some columns to the spreadsheet and now all the custom views are messed up. The only way we have been able to work through this is deleting and creating new views.
I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
When I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?