Data Validation With ActiveX ComboBox With VB Code

Sep 19, 2013

I found a code on [URL] that claims that it will do what I want to achieve. I tried it but it is not working for me. Here is the code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String


How to verify that this code is a working code and not just bogus one?

View 1 Replies


ActiveX Combobox Code-run

Jun 4, 2009

I've created a workbook on whose main sheet there are three activex comboboxes. The first one contains a list of years, and the second one contains a list of months. The user selects the year, and it goes to its linked cell. The user then selects a month, and using the Combobox_Click event, should run a sequence of 7 macros (i.e. using the "call updateinformation1" macro, which is the first of the seven updateinformation macros).

The sequence of macros ran fine before I added the comboboxes. After adding the comboboxes, the final (7th) macro somehow goes back to the first macro and then causes a "ClearContents method of Range class failed" error on the "Selection.ClearContents" line. These are the first and seventh macros:

Sub updateinformation1()
'clears previous information to make way for new information
Application.ScreenUpdating = False
Application.EnableEvents = False


I can't for the life of me figure out why it's looping back to the first macro instead of just stopping after the seventh. I have other workbooks that do something similar, and I don't have this problem. Am I missing something?

if I need to post all 7 macros?

View 9 Replies View Related

ComboBox For Data Validation?

May 3, 2013

I am working on a project where I have a data validation list box in column A B and C on the same row. The selection in column A determines what can be selected in column B, and column b effects column c selection. The data from the list is located in another sheet and dynamic named ranges.

The data validation source is as follows:

Column A:
Column B:
Column C: =OFFSET(ProjectNoList,MATCH(D11,OFFSET(ProjectNoList,MATCH(C11,ProjectNo,0)-1,2,COUNTIF(ProjectNo,C11),1),0)-1+MATCH(C11,ProjectNo,0)-1,3,COUNTIFS(DisciplineCode,D11,ProjectNo,C11),1)

This works fine, however I need the drop down menus to be longer as there can sometimes be a lot of data to scroll through. To do this I have used the instructions at Excel Data Validation Combo box Click to make a combobox data validation.

It works fines for column A, however I can't get the list for column B to populate in the combo box.

The code looks like this:

Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("ComboName")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""


I suspect that column A works because the data validation formula is only referring to a named ranged, ProjectNolist, however columbs B and C use a few in a formula.

.ListFillRange = str
is empty when column b is selected

is it possible to get the data validation list from column b and c into a combo box and if so how?

View 6 Replies View Related

Data Validation VS Combobox

Mar 17, 2009

I am using Excel 2007 and I've been working with a spreadsheet that utilizes several Data Validation to allow users to make different choices and depending on those choices the next options in line change. My problem is that I have so many different options, so long of formulas, that I run out of space to type in formulas in the DV source field. One way I get around this is to drop down to the next cell and start again, but I really need all the choices to stay in one row. Somebody suggested using VLookup, but I am not sure if that is the right path to take because a few of the final drop down choices have multiple options, and I'm not clear on how to use VLookup.

I am strongly considering using a Combobox Form Control or Combobox ActiveX control because, from what I have learned, they wouldn't be restricted by formula space. However, being fairly new to Excel I do not know what the codes would be to allow the comboboxes to change options based on previous selections. I've searched the message boards extensively and haven't been able to find an answer.

View 9 Replies View Related

Use Activex Combobox In The Worksheet

Jan 5, 2008

successfully use Activex combobox on the worksheet, (excel 2003)
without the code from other boxes being triggered at random.

Or should I give this up, and use a form to house these controls? ( I have no Knowledge on this)

I've tried certain IF then statements to protect the excecutable, but it seems, that, even boxes in other books are looked at, with a simple call statement (to a macro in module)

Having a real problem in Isolating these things.

Try to get the code to activate when you select the same Item twice, It seems none of the procedures do this. I made a workaround with using two procedures, but it's a bit clumsy.

View 9 Replies View Related

Another Dependent Data Validation Combobox

May 5, 2009

I'm still curious about the sample from Contextures, (DV0032 - Dependent Data Validation Combobox)

I have made a modification from its file (sample is attached). First drop list can be autocomplete, but the second cannot. For the comparing, I made row 2 in ValidationSample sheet is my modification, and row 3 is the original.

View 10 Replies View Related

Add Scroll Arrow To ActiveX Combobox?

Mar 20, 2013

I have an application were I would like to use an Active X combo box instead of a regular dropdown list. The list contains 64 items and the text becomes very small in a standard dropdown list. Using an Active X Combo Box allows me to enlarge the text for easier viewing but with 64 items the list tends to run off the sheet. Is their a switch or some other way (scrolling) to make it more viewer friendly?

View 5 Replies View Related

Filter Column Using ActiveX Combobox?

Aug 1, 2013

I am trying to use a combo box with a defined list I have in another worksheet.

I was able to name and populate the list into the combo box, but am trying to work a macro into it.

I have a large data sheet with with many columns. In this worksheet, I also placed the combo box. What I would like to do is to be able to click on a name in the combo box and have it filter Column H, where there are many data points.

For example, In column H I have Apples, Watermelons, Peaches, etc.

In the combo box list, I would like to click on Apples and Column H list only the Apple names. Then if I want to click on Watermelons, it would filter Column H to only those which have Watermelons listed...

View 1 Replies View Related

Populating Combobox (ActiveX) Using Variable

Mar 19, 2014

I have 100 combo boxes on an excel sheet, they are divided in three sets (1-25, 26-50, 51-100). Each set has the same value within the set, but is different from the other.

Right now I am able to populate the combo boxes using the following code

ActiveSheet.ComboBox1.AddItem "Value1"
ActiveSheet.ComboBox1.AddItem "Value2"
ActiveSheet.ComboBox1.AddItem "Value3"
^ This is time consuming and error prone. Is there a way I can use a variable to populate the combo boxes?

I have been able to find the following code, but it does not work.

Dim ComboBox As ObjectFor ComboBoxCalc1 = 1 To 25
Set ComboBox = ComboBox(ComboBoxCalc1).Select
ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value1"
ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value2"
ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value3"

View 6 Replies View Related

Can't Change Combobox Properties (activex)

Apr 24, 2009

How do I set the font size property and special effect property in this code?

I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.

Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rVals    If rCell.Validation.Type = 3 Then        lTop = rCell.Top        lLeft = rCell.Left        lHeight = rCell.Rows.Height        lWidth = rCell.Columns.Width            With ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight)                .Name = "NewCombo" & lCount                .ListFillRange = rCell.Validation.Formula1                .LinkedCell = rCell.Address(0, 0)                '.SpecialEffect = fmSpecialEffectFlat //doesn't work //                '.FontSize = 14 //doesn't work //                '.Font.Size = 14 // doesn't work //            End With                        lCount = lCount + 1    End IfNext rCellEnd Sub

View 9 Replies View Related

Reference ActiveX ComboBox Value On Worksheet

Apr 10, 2008

How do I reference the value contained within a combobox using VBA?

I have tried the following:

ActiveSheet. OLEObjects("cmbSearchType").Object.Value

I'm wanting to use the drop-down combo box as a simple way for the user to choose what type of search they wish to perform on the spreadsheet. The search algorithm chosen depends upon the current value show in the combo box.

I am using Excel 2007.

View 7 Replies View Related

Data Validation Autocomplete Via Combobox Modification

Jun 7, 2007

I'm using a script that when you double click on a cell containing Data Validation > List it changes to a combo box that has an autocomplete/increased columns view...It's working great, however there are two small modifications I would love to do and am not having much success with. Currently, you double click the cell w/the Data Validation > List in it, it then changes to a combo box and you can auto complete using the keyboard or select the dropdown and choose from an increased list. However, I would like to be able to simply single click on the cell, have it change to the combo box and auto expand the list if possible.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Rep Assist Report")
Cancel = True
Set cboTemp = ws.OLEObjects("RepName1")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error Goto errHandler
If Target.Validation.Type = 3 Then.....................

View 6 Replies View Related

Linking Sheets To ActiveX Combobox In Excel

Sep 13, 2012

I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").

View 2 Replies View Related

Replacing Forms Control Combobox With ActiveX

Aug 13, 2014

Since Form Controls comboboxes don't allow font editing, I need to switch to ActiveX...

The combo box values come from the following cell range:


Those values are, from top to bottom: "select distance", "50 Miles", "100 Miles", "150 Miles".

The following macro is also assigned to the combobox:


I have two issues. The first is that the macro doesn't seem to be working. It is supposed to filter a list of cities within 50, 100 and 150 miles of a user input zip or city. The original Forms Control combo works but not ActiveX after I put the code in the change event:

[Code] .........

The second issue is that the last clicked value in the ActiveX comboBox replaces the top one.

Ex: from top to bottom, the combobox should display:

[Code] ........

but if the user clicks "100 Miles", the combobox is then going to display

[Code] ......

If the user then select "50 Miles", the box displays:

[Code] ........

View 4 Replies View Related

Copy And Paste To Worksheet Using Combobox (ActiveX)

Jan 22, 2009

I managed to put the names of my other worksheets into some comboboxes in the main worksheet by using this ....

View 12 Replies View Related

ActiveX Combobox With Dynamic Linked Cell?

Feb 4, 2012

I have a golf teetime reservation spreadsheet. For each tee time there can be up to four players names so I use 4 columns. Times are listed in rows every 10 minutes beginning 6am until 5pm for a total of 66 rows per day. I have a member/customer list that I have on a separate sheet that I've named. I am using the ActiveX combo box next to each possible entry (so 4 columns times 66 rows = 264 combo boxes). It's a little slow and takes a long time to open. I like the ActiveX combo box because it allows me to define the box format and display and it allows search/read/write capability. The idea is the customer calls, I ask what time they want and we arrive upon an available time. I ask for a last name, I click the combo box, type the first letter of the first name and then scroll if necessary to the name and click it to add the name to the time/player cell. If no name in my list we simply type it in and I'll capture it next time I refresh my customer list.

way to dynamically define the linkedcell in the properties so that instead of 264 combo boxes, I might have one combo box that I can click on and then direct my selection to the cell I want it to go into.

View 5 Replies View Related

Activex Combobox Change Font Size - VBA

Sep 13, 2012

I am trying to change the font size on my combo box using VBA.

I create the combobox dynamically, so need to be able to change the font too.

This is how I create the combobox, however the .Font.Size = 10 does not work and the default font size is 11.

With ActiveSheet .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=159.75, Top:=80.25, Width:=75.75, Height:=19.5) _
.Font.Size = 10
.Name = "cmbBaseD"
.OLEObjects("cmbBaseD").ListFillRange = "DynRng"
End With

View 2 Replies View Related

OleObject ActiveX ComboBox Event Procedures

May 23, 2009

I've searched the web and OzGrid all day, and still have this question: what is the equivalent for .OnAction when using a ComboBox as shown in the code below. Everything else works as desired.

Loopcntr = 1

Set xyz = ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)

'''??? xyz.OnAction = "TDListing"
xyz.Object.BackColor = RGB(204, 255, 204)

Do While Loopcntr <= TaskListArraySizeHolder
DDholdName = TaskListArray(Loopcntr)
xyz.Object.AddItem DDholdName
Loopcntr = Loopcntr + 1

View 5 Replies View Related

Excel 2010 :: Combobox Or Data Validation With Macro

Apr 9, 2013

I am using excel 2010. I have 3 separate form control each with its own macro. I would like to create a combo box with a drop down with a list of each form control case and be able to perform the same function as the individual form control when a specific name is chosen in the list of the combo box.

View 3 Replies View Related

ActiveX Combobox Date Format And List Index

Mar 30, 2014

I have an activeX combo box with dates in the following quarterly format (dec-14, march-15, july-15 etc), with the following code:

[Code] .....

However' while each code works fine standalone, the two codes wont work together - and the listindex reverts back to 0.

View 3 Replies View Related

Excel 2007 :: ActiveX Combobox And Shared Workbook

Jul 15, 2014

I have a workbook with a combo box.

ActiveX control? (=EMBED("Forms.ComboBox.1","") They are filled using named ranges on another worksheet and work fine. The problem happens when I share the workbook the combo boxes become completely non-functional. They don't even drop down when clicked.

View 2 Replies View Related

Have Output Cell Of ActiveX Combobox Link To Named Range?

Apr 22, 2013


MFI is the named ranged that is referencing a column of data. This works, but I want to be able to change the MFI to a different named ranged.

So what I am trying to do is using an ActiveX Combo box so I can reference the named ranges that I have set, for example MFI, so each time a user chooses MFI or another named range, it will output to a linked cell (A1). Then the linked cell will be referenced into this forumla.


Is it possible for A1 to be linked to the named range, so if the user choses MFI in the combo box, it will change all AverageIFS formulas to look at a different named range.

View 3 Replies View Related

Unable To Set Hidden Property Of Range Class ActiveX Combobox

Jul 30, 2013

Here is my code:

Private Sub ComboBox1_Change()
ActiveSheet.Rows("3:62").Hidden = False
Range("D1").Value = ComboBox1.Text
End Sub

I get a runtime error '1004' when I make my combo box selection. I don't have any security or password protection type stuff. The combo box is part of a more complicated spreadsheet and I don't want to switch to a data validation type drop down and I really want it to be activeX.

View 4 Replies View Related

ActiveX Combobox - Linked Cell Reference Lost After Save / Close And Re-open Workbook

Feb 11, 2013

Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.

View 13 Replies View Related

Insert Data Validation Via Macro Code

Mar 1, 2008

I have written the below code to enter some formulas, validation, and formatting. I keep running into a problem when trying to add the first validation.
The debug msg is...

Run-time error '-2147417848 (80010108)':
Automation error
The Object invoked has disconnected from its clients.

Debug highlighted portion is...

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PRODUCT" ....................

View 9 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

VBA Code To Add Option Buttons To ActiveX Frame?

Jun 10, 2014

I want to write a VBA code that will place ActiveX Frame directly onto the worksheet and then add 3 Option Buttons from Toolbox into that frame.

I could not find an answer searching through the web... Also when trying to record macro and see the code it did not record placing the buttons from the toolbox or any formatting (color, caption, etc.) via properties...

When formatting the buttons it would be useful if the selected radio button would have different color, bold font, etc.

View 5 Replies View Related

Create Range List Code For Data Validation

Sep 29, 2007

I am using following code to apply data validation list. However this code gives error Intermittently. The error message is 'Application defined or Object defined error'. Also this code looks little cumber some. Will really apprciate if any one can put some ligh on why is this error appearing sometimes. Do I need to change something in the code or add few extra lines. I feel following points will be necessary when you check my code

1. The data of validation list is stored under the column heading 'Reported_By_List'
2. Validation is applied on the range under the column heading 'Reported Through'
3. Start and last Cell Address Of the cells of the column in which data of validation list is
stored are extracted into the variables 'StartCellAddress1' & 'LastCellAddress1 '
4. Start and last Cell Address Of the cells of the column on which data validation will be
applied are stored are extracted into the variables 'StartCellAddress2' &

Sub Validation1()

Dim wsName As String
Dim RValue As String
Dim WorkBookName As String
Dim StartRow As Integer
Dim StartColumn As Integer
Dim LastRow As Integer
Dim CellAddress As String
Dim ColumnAddress As String
Application. ScreenUpdating = False
WorkBookName = ThisWorkbook.Name
wsName = ActiveSheet.Name...........

View 3 Replies View Related

Form Or ActiveX Button To Stop / Start Code Temporarily?

May 12, 2014

I would like to know the easiest way to temporarily keep a worksheet code from running while I am editing, then turn it back on when I am done. I was thinking a button with these commands(?)>

Application.ScreenUpdating = False

Application.EnableEvents = True

but I don't know which button to use, or if I would need a button for each.

View 2 Replies View Related

ActiveX Listbox Does Not Work With VBA Code For Changing Chart Axis Scale?

Jun 5, 2014

I have some code for changing the scale of my chart axes so that when new data is inserted into the column which the chart sources from (say column B), the axis scales on my chart will automatically adjust (as the Excel algorithm sometimes leads to charts which are a bit squashed). This code works fine, and I have included it below.

I have then tried to introduce an ActiveX listbox so that when you select an item in the box, the reference number generated (1,2,3...etc) then determines which data series is inserted into column B using a vlookup function. However, when I do this, the original VBA code for automatically adjusting the chart axes no longer works.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects(1).Chart


View 4 Replies View Related

Copyrights 2005-15, All rights reserved