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
Loop
View 5 Replies
ADVERTISEMENT
May 17, 2008
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies
View Related
Aug 19, 2008
I am trying to determine if it's possible to control the order of like events. In other words, if I have set up two Worksheet_Change events, can I control which one fires first? Or, as a second best, can I determine which one will fire first?
I am using Office XP on Windows XP, but I'm looking for a generic solution if possible.
View 5 Replies
View Related
Mar 27, 2008
what do people meen when they say target and how is it used from what i can gather its somthing to do with the active cell?
View 9 Replies
View Related
Apr 27, 2008
I have CheckBox1 (.Value = FALSE) on the UserForm and when I run my sript ... this change Value to TRUE and run the subrutine Private Sub CheckBox1_Click. I need block this step with using VBA code.
View 4 Replies
View Related
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
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
Worksheets("Criteria2").Activate
Cells.Select
Selection.ClearContents
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
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
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
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
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.
Code:
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"
Next
View 6 Replies
View Related
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
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
ActiveSheet.OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Value
Sheets("mySheet").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
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
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:
tbl_arrays!$I$3:$I$6
Those values are, from top to bottom: "select distance", "50 Miles", "100 Miles", "150 Miles".
The following macro is also assigned to the combobox:
[Code].....
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
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
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
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.
Code:
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
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
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
[Code]....
How to verify that this code is a working code and not just bogus one?
View 1 Replies
View Related
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
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
Apr 22, 2013
=AVERAGEIFS(MFI,DATE,">="&JanStart,DATE,"<="&JanEnd)
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.
=AVERAGEIFS(A1,DATE,">="&JanStart,DATE,"<="&JanEnd)
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
Jul 30, 2013
Here is my code:
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
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
Apr 17, 2009
Dim bfr As Long
bfr = ComboBox1.Value
ComboBox1.Value = bfr: Exit Sub
how come this gives me a "cant change property" error!?
The code is inside my combobox change event...
View 9 Replies
View Related
Mar 3, 2014
I have attached an example set up with a user form I am building. I currently have the first combo box loading upon the initialize of the user form an from that I choose one of the product types and it gives me a list with all product names associated in the second combo box. Upon a change event in the second combo box I want to populate the 3rd Column with the count of how many of that Product type.
I have a couple different code set ups in the attached sheet and neither works.
CmboBxtoTextbox.xlsm‎
View 7 Replies
View Related
Jan 3, 2013
The following code works fine, but when I put the code in an ActiveX Combobox Change Event it gives a run-time error 1004. ("Select Method of Range class failed")The error occurs on the following line
Code:
Worksheets("SAVED").Range("A" & l).Select
Code:
Dim l As Long
Application.ScreenUpdating = False
l = Application.WorksheetFunction.Match(Worksheets("DATA").Range("O34"), Worksheets("SAVED").Range("A1:A10000"), 0)
Worksheets("SAVED").Activate
'ROSTER===
Worksheets("SAVED").Range("A" & l).Select
Selection.Resize(1, 739).Offset(1, 2).Copy
[code]......
View 2 Replies
View Related
Jul 10, 2013
Using Excel 2010. I have a ComboBox (Not ActiveX) on a worksheet
I'm trying to add an event so that when user click on a choice a macro is fired using the selected item as citeria for a filter When I click on the drop down and make a selection I receive this error msg:
Cannot find the macro "cboUserSelection_Change" The macro may not be available in this workbook or all macros may be disabled
I also tried _AfterUpdate - similar error message.
I added the Combo Box code to the Worksheet code, not a standard module
I was able to run a quick test macro so I do not believe "all macros are disabled"
View 1 Replies
View Related
Nov 2, 2006
Is it possible for a combo box selection, linked to a cell on the same sheet to fire a worksheet change event? I can't seem to find a way to do it.
View 3 Replies
View Related
Dec 4, 2006
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1, 1)
If Not Intersect(.Cells, Range("b3:b6")) Is Nothing Then
Range("b7") = "Not Found"
For i = 3 To 6: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m3", Cells(3, Columns.Count).End(xlToLeft))
For i = 0 To 3: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b7").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b16:b19")) Is Nothing Then
Range("b20") = "Not Found".....................
I'm working with this code right now. The problem is the macro will only work if i type the numbers manually. if the values are retrieved from a combobox, the code above down not work as it cannot read the values.
View 9 Replies
View Related
Jul 25, 2008
I have a VBA question. So I have a radio command control in my excel spreadsheet that is linked to a particular cell. What VBA code would I enter into that control that would make the control visible = false based on whether or not the cell to the right of linked cell meets a certain criteria (= 1 for example.)
View 9 Replies
View Related
