ComboBox - VBA - Unique Views...

Jul 11, 2009

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..?

View 7 Replies


Add Unique List To ComboBox

Jul 12, 2007

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

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.

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

View 8 Replies View Related

How To Select Unique Record For Combobox

Jan 25, 2013

I have a combobox on a Userform, when the userform initializes i want it to populate Combobox1 with all the Unique values from SHeet 1 Column A (variable length to the column)

I dont know how to select a unique record only.

*thinking a bit further ahead i have a second combobox that i need to populate with unique values from column B on sheet 1, where in column A matches what was selected in combobox 1

View 5 Replies View Related

Combobox On Form With Unique List

Jan 24, 2008

I am trying to create a form with a combobox drop down menu. The list I need displayed is in cells C4:C20 but I only want to show unique values not all of them.

View 9 Replies View Related

Add Unique Items To ComboBox Control

Aug 30, 2006

I have a database of data that looks like this

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.

View 2 Replies View Related

Display Unique List In Combobox Of Userform

Feb 11, 2012

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.

View 7 Replies View Related

Populate Combobox List With ONLY Unique Items ()

Nov 16, 2006

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.

View 9 Replies View Related

Fill Combobox With Unique List Range

Feb 25, 2008

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

View 2 Replies View Related

Fill ComboBox With Unique Values From Column

Jun 22, 2008

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:


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.

View 3 Replies View Related

Populate Userform Combobox With Unique Values From A Range

Jul 26, 2012

I found this thread which deal with populating unique values in my listbox.

Here is the thread link. [URL] ........

What these line of code mean (the ones in red).

Private Sub UserForm_Initialize()

Dim v, e
With Sheets("maintenance").Range("c2:c500")
v = .Value
End With
With CreateObject("scripting.dictionary")

[Code] ......

Note : I simple tried to check the value of v by giving msgbox (v), just below the line v = .Value.

But I got run-time error 13 : Type mismatch error.

View 5 Replies View Related

Adding Unique Items To ComboBox From Another File/Workbook

Aug 31, 2006

I need to add about ~200 items to Combo From Other Excel File (with no duplcates).
How to make it.

Private Sub UserForm_Activate()
With cboComp
.addItem "item"
End With
End Sub

View 3 Replies View Related

Fill ComboBox With Unique Entries Based On OptionButton

Feb 5, 2008

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

View 3 Replies View Related

Auto-populate Combobox - Extracts Unique Distinct Values From Column

Jan 11, 2013

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,

View 1 Replies View Related

Multi Views On Same Worksheet

May 20, 2013

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.

View 1 Replies View Related

Pivot Table 'views'

May 11, 2008

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.

View 9 Replies View Related

Different Users, Different Levels Of Views

Nov 22, 2006

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()
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..............

View 3 Replies View Related

Turn Off Custom Views

Apr 19, 2007

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

View 7 Replies View Related

Run Custom Views From Worksheet

Jun 8, 2007

I setup custom views for my worksheet

I dragged the custom view command to my menu bar

Everything works great on my machine

But I would like to send to other users and for them to be able to use the custom views w/o having to help them setup on the menu bar

Plus I've had problems in past where custom view tool is not persistent on the menu bar
Sometimes there, sometimes not

Is there a way to add to say cell A1 on the worksheet instead?

View 3 Replies View Related

Print One Worksheet In Different Filtered Views As One PDF?

Jan 18, 2013

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.

View 2 Replies View Related

When Using Custom Views - Get Error Message

Aug 13, 2014

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.

View 1 Replies View Related

Shared Workbook Scrolling And Views

Oct 6, 2009

1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?

2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?

View 10 Replies View Related

Use Checkbox To Control Custom Views?

Sep 14, 2012

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
End If

End Sub

View 5 Replies View Related

Query SQL 2005 Views With Excel

May 20, 2009

I've posted this on Experts Exchange too, specifically trying to target the SQL Server experts.

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...

View 9 Replies View Related

Force GetOpenFilename To Views - Preview

Nov 4, 2006

I copied below code from one of Andy Pope's thread answers. Thanks Andy.

Private Sub CommandButton1_Click()

Dim vntFile As Variant

vntFile = Application. GetOpenFilename("Graphics Files (*.bmp; *.gif; *.jpg; *.jpeg),*.bmp;*.gif;*.jpg;*.jpeg," & _
"All File (*.*), *.*", Title:="Select Picture")

If vntFile <> False Then
ActiveSheet. Cells(27, 1).Value = vntFile
Image1.Picture = LoadPicture(vntFile)
Image1.PictureSizeMode = fmPictureSizeModeStretch

End If
End Sub

I would like to force the box to open in Views - Preview instead of List or Details or whatever it is at. I tried the following (and a bunch of other things), but I could not get it to work

With FileDialog
.InitialView = msoFileDialogViewPreview
End With

View 9 Replies View Related

Custom Views Lost After Cut/Move/Insert Etc

Aug 26, 2006

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.

View 9 Replies View Related

Show/Hide Specific Columns For Custom Views

Jun 12, 2008

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.

View 2 Replies View Related

Populating Combobox 2 With Items That Match Criteria From Combobox 1

Mar 30, 2009

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.

I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

View 9 Replies View Related

Auto Populate 2nd Combobox Based On 1st Combobox Data

Mar 30, 2014

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

View 1 Replies View Related

Dismiss ComboBox Dropdown While ComboBox Code Is Still Running

Dec 8, 2009

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?

View 9 Replies View Related

Filling Combobox Based On Another Combobox.text Value.

Jun 20, 2009

try and achive is when a user selects a item from the 1st Combobox the 2nd Combobox is the populated with the cell that is to the right of the selected item.

for Example if a user selects AAB from combobox1,, Combobox2 should populate with Belly.

Maby using combobox1_Exit for the excercise.

View 14 Replies View Related

Copyrights 2005-15, All rights reserved