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:

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


ADVERTISEMENT

Clear Forms Control ComboBox

Sep 25, 2009

I have a combobox from the Forms Toolbox inserted in a worksheet and have a macro assigned to run when the user selects a value. After the value is selected in the combobox I'd like the combobox to clear itself (show a blank). Currently the selected value stays highlighted in the combobox after the selection is made. I know how to do this with a Controls combobox, but cannot figure out how to use it with a Forms combobox. (The reason I am not using the controls cb is because there seems to be an Excel2007 bug that causes the properties of the displayed text to randomly change when the control is activated. When a selection is made in the controls cb, the text displayed in the cb turns bold, and either grows or shrinks in size to the point that it is unreadable. Same thing seems to happen with all other ActiveX controls...)
Sample attached.

View 4 Replies View Related

Value From Forms Combo Box Control

Mar 26, 2008

I have a (form control) combo box in my worksheet. Inside the combo box are several different ways a data set might be sorted. I would like to refer to the current selection of the combo box in my VBA code because I have a macro which executes differently based on the selection in the combo box.

I have found this piece of code which I know executes when the combo box selection is changed.

Sub DropDown1_Change()

However, I still do not know how to refer to the value of the current selection.

Below was my attempt to assign the current selection to a name, ComboValue, that I created:

Sub DropDown1_Change()
Names("ComboValue").value = ComboBox1
End Sub

Strangely, this code executes once, but removes the name I created. It then returns an error on subsequent runs.

View 4 Replies View Related

Fill Forms Control ListBox

Jul 29, 2006

I created a list box from Forms toolbar (instead from VBE). I would like to add items to this list box using VBA, but cannot find right qualifier to access this object. When I tried to use " recording macro," the code that was generated was

ActiveSheet.Shapes("List Box 7")

but if I try something like

ActiveSheet.Shapes("List Box 7").addItem "hello"

it doesn't work, because well, addItem method belongs to ListBox object, not to Shape. So if I want to add an item to this list box, how should I reference this list box so that I can call addItem() on it?

View 6 Replies View Related

Set Variable To Forms Control & Get Value Property

Mar 22, 2008

How do I reference a spin button from a variable that I set.

e.g

Dim objSpn As Object
Dim y As Integer

Set objSpn = ActiveSheet.Shapes("spnWCDate")

y = objSpn.Value

This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it.

ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?

View 4 Replies View Related

How To Use ActiveX Control Checkbox

Jan 9, 2012

I am trying to create a workbook where the first worksheet has a list of Checkboxes and each checkbox 'unhides' or 'hides' subsequent worksheets within the workbook, depending on whether the box is checked. I can make the checkbox, I can record the macro.

Sub Test_checkbox()
' Test_checkbox Macro
Sheets("Dist").Visible = True
End Sub

How do I link them together? Ideally, I'd like to have my boss be able to 'play' with which sheets are visible (check, uncheck, check, uncheck, etc) .

View 4 Replies View Related

Max Length Of ActiveX Control Name

Mar 16, 2012

What the max length of an ActivX Control name would be. I mean the name that you would use in coding something, not the actual caption name of the button. IN this case I am using a radio button.

Based on that I would like to create random IDs to assign to the button. I know how to do this in the cell formula but not in code.

View 3 Replies View Related

Transfer Control Values Between User Forms

Jan 9, 2007

I am using a calendar control 11 in a user form. I would like to create a combobox on a user form that when you click the drop down button it opens the calender then the user can select a date which is then returned to the combobox. I beleive the way to do this is to trap the dropbuttonclick event. Tho it dosent seem to work.

Private Sub ComboBox2_DropButtonClick()
Calender.Show
ComboBox2.Value = Calender.Calendar1.Value
End Sub

View 5 Replies View Related

Combobox In Forms

Oct 13, 2008

I have created a form which is called up via a macro and then shows a combobox which contains the names of the worksheets in the workbook.
I am trying to enable the user to select one of the worksheet names from the combobox and then store that name (the book does not have fixed sheet names) and continue with the original macro using the stored name.
I just can't figure out how to use the selected name in the original macro.

View 9 Replies View Related

ActiveX Control Can't Create Object

Jul 28, 2009

I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my

View 14 Replies View Related

Transparent Background Of ActiveX Control

Jan 6, 2010

I have an ActiveX Label control with a transparent background. When I click on the label the background turns white and stays that way as long as the mouse is over the label, obscuring whatever's underneath. (It also very slightly alters the character spacing.) What object/property controls this? How do I adjust it?

While I'm here, is there an easy programmatic way to assign arbitrary RGB colors to ActiveX controls? The palettes available from the Properties window are fairly limited. A google search for "ActiveX colors" yields a deluge of marketed products, so it's hard to find useful information.

View 4 Replies View Related

Add Downloaded ActiveX Control To Toolbox

Jun 10, 2008

Version: excel 2003
OS: Win XP

Currently using VBA do develop a tool for Excel. I've downloaded a .ocx control and am wondering how one would go about adding it to the toolbox?

View 3 Replies View Related

Forms Toolbar - ComboBox

Feb 24, 2007

I’m trying to do a pull down with scrolling ability in a cell that references another worksheet. I want to look like the one that occurs when you use “Data Validation”. I’ve seen this in another workbook and it does not look like the used VBA.

View 4 Replies View Related

ActiveX Control Button Changes Size For No Reason

Feb 18, 2014

I have created several macros where i use an active-X control button. This button is pressed and the macro begins...........but every now and again this button changes size for no apparent reason.

I have read countless articles/posts about what "may" be causing the problem but i have never read anything which clearly identifies the soln to this problem and the problem has been out there for years. To make matters worse the problem can not be duplicated on any regular frequency that i am aware of............so its not like i can post a file that will show the problem.............

View 4 Replies View Related

ActiveX Control Button Not Copying And Pasting?

May 14, 2014

I am using the following code to copy a template to a new worksheet each month:

Code:
Sub CopyTemplateNewMonth()
Application.ScreenUpdating = False
Dim SourceRange As Range, DestRange As Range

[Code]....

Everything copies over except an Activex Control button. I also have form control buttons with macros assigned to them, and they copy & paste just fine. The code assigned to the ActiveX button also copy's & paste's just fine...but the button itself does not.

View 9 Replies View Related

ActiveX Click Control--STOPS When On Another Sheet

Nov 23, 2007

RE: Macro ActiveX Click Control--It stops when on another sheet

Macro runs OK when run from menu.

An activeX click button was put on sheet B.

Right click the control, View Code, Paste in code from macro, then click button.

It runs the part of the macro that applies to sheet B, goes to previous sheet A, and then stops. The range select line for the named range on sheet A is highlighted in yellow. It stops when selecting any range or cell on sheet A, like B2.

Any idea why this code does not work on sheet A when ActiveX button is on sheet B? Is there someplace else that the control can go? Using Excel 2000.

See part of code below, which starts on sheet B:

' Macro recorded 11/22/2007

Range("SortAll").Select
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Previous.Select
Range("FormBase").Select (STOPS HERE)
Selection.Copy
Range("Formul").Select
ActiveSheet.Paste
ActiveSheet.Next.Select
FormBase is a named dynamic range
FormBase=OFFSET(A!$A$2,0,0,1,MATCH(1E+306,A!$2:$2))

View 9 Replies View Related

VBA / Creating And Attaching New Sub To Newly Created ActiveX Control?

Jun 2, 2014

After programmatically creating a new activex checkbox, I want to create a sub for it's click event. I have given the object a name, and fortunately the code for it's click event is a one-line call of another sub, but how?

The sub would look like the following except be named after the new control (obviously), and yes, it is just the number in the name that changes:

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

View 2 Replies View Related

Multiple Instances Of Excel And ActiveX Control Object

Feb 21, 2014

I have an excel application which works extremely well in one single instancec. It has a bunch of ActiveX control objects on the worksheet. Generally I call them by convention Sheet1.Cmd.show.

I have since experimented with having multiple instances of excel running on the machine. I start to notice something very strange. The first instance I open and run all the activeX buttons are all clickable. As soon as I open a second instance of an excel program all the activeX buttons on the worksheet are not even clickable. They are enabled by my program, but not clickable as if they were not there at all.

I also try open a normal excel workbook then followed by my excel program in a second instance. Even though the first excel file does not utilize any activeX objects all my buttons on the subsequent instance are not clickable.

The weird thing is all my activeX control objects on a userforms are still clickable and call to all my planned events in my program. It is just all the activeX controls on my worksheet that do not have any reaction at all.

I always think when one opens a new instance it will be totally indpendent and would even occupy another CPU if my machine has more in it. If they are truly independent then I am at a loss to understand why my activeX controls would fail to work at all.

View 8 Replies View Related

Excel 2007 :: Insert And Link ActiveX Control From VBA

Mar 15, 2012

I have a form that collects four pieces of information from the user and places each into columns A, B, C, D on a sheet. I would like to automatically add an option button to column E so that a user could select it and then click a control button to delete the corresponding row.

Is this possible or is there maybe a better way to do this in Excel 2007.

View 1 Replies View Related

Can ActiveX Control Button Macro Be Placed In Regular Module

Feb 20, 2014

Can Active-X control button macro be placed in a regular module, or does it always have to reside in the sheet module?
Or is the only way to accomplish this is to have the macro in the regular module being called from the button macro in the sheet module?

I somehow seem to remember a way to move/assign an Active-X ControlButton macro to a regular module, but maybe I'm wrong?

View 1 Replies View Related

Determine Original Dimensions Of ActiveX Image Control

May 12, 2014

I have a worksheet that contains a number of ActiveX image controls, which have been scaled to a uniform height to serve as thumbnails.

I have also created a class, where each instance is initialized with one of these image controls, and events (specifically the on click event) are captured. When the user clicks the picture, it opens a userform containg the same picture. However, I'd like that picture to be at its original size, not the scaled-down size.

how can I determine the original dimensions of a picture used in an image control? I can tell that the original resolution is there (by changing the PictureSizeMode to clip), but I can't find any properties that show me how big that image actually is, only ones that return or set the size of the object itself.

View 1 Replies View Related

Disable Or Stop ActiveX Control Events Firing

Sep 8, 2006

I've several check boxes on a worksheet. Some of the check boxes control the values of other through the _click event. However, when I try to change the value the event attached to the control in questions fires.

application.enableevents = False

would prevent events being fire from within the macro but this only seems to work when I step through the code.

I understand that I could set a global variable to do this and will probably go down this route anyway - but I am interested to know how/when I should be using the EnableEvent method (as I am sure this should be the way that I use it)

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

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

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

Text Box Forms Combobox Initialize Private Sub

Jun 18, 2012

I am trying to work out how i can run a macro on an 'initializing event'...which is caused by when I click on a certain Forms ComboBox.

This drop down box is a shape and it is called "Drop Down Box 11"

so was hoping to write something like

The box is linked in with Cell E56

so have put in my code as

VB:

Private Sub Worksheet_selectionChange(ByVal Target As Object)
If Target.address = "$E$56" Then range("f56").value = "Yes" etc...

but it still allows me to click on the combo box and change my pick but it still doesn't initialize the event.

View 2 Replies View Related

Changing The Font Size In Combobox In Forms

Dec 11, 2007

im using combobox from the forms toolbar. and i have tryed and searched lots of forms and sites but no results, so what im trying to do is change the font size to 12 in my combobox using VBA.

-im using xl2000

-i dont wanto switch to the combox in the active x becouse i have more than 70 comboboxs in this spread sheet. and it will also be very complicated to change the comboboxes due to all the link cell's and there values.

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

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

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







Copyrights 2005-15 www.BigResource.com, All rights reserved