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


ADVERTISEMENT

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

Clear All Items From Forms Dropdown

Feb 7, 2008

Is there a way of clearing all items added to a dropdown list. The dropdown is from the form toolbar.

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

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

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

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

Forms Toolbar Versus Control Toolbox Toolbar

Oct 19, 2006

which of these toolbars provides the better 'controls' to paste onto worksheets (as opposed to UserForms)? For those wondering, both toolbars contain some apparently similar controls, e.g., combo box, radio button, spinner etc. but there are differences in their behaviour it would seem...

View 2 Replies View Related

Reset / Clear ComboBox

Jan 11, 2008

I have a userform with several combo boxes on it. I also have three command buttons, 'OK', 'Reset' and 'Cancel'. I want to code the 'Reset' command button to reset the values of the combo boxes to empty or "". I have attempted to do this simply by using the following code.

Private Sub cmdReset_Click()
cmbWaterTemp.Value = ""
cmbTypeFastIce.Value = ""
cmbIceDrift.Value = ""
cmbTrendBehavior.Value = ""
End Sub

When I click on the 'Reset' command button I get the following error.

"Run-time error '94':
Invalid use of Null"

When I use the debug tool the error pointed to is a different sub.

Private Sub cmbWaterTemp_Change()
txtTemp1Encoded.Text = Left(cmbWaterTemp.value, 1)
txtTemp2Encoded.Text = Right(cmbWaterTemp.Value, 1)
End Sub

I am not exactly sure why the error points to this sub. I know there must be a simple way to accomplish this. I am including a workbook that is similar though not exactly the same due to file constraints. What I want to happen is when the 'Reset' button is clicked is to clear all values for the combo boxes and the text boxes. I have successfully gotten the textbox values to clear although not the combo boxes.

View 4 Replies View Related

Clear Combobox Based On Checkbox Value

Jan 4, 2013

I have multiple comboboxes with correlating checkboxes. That is, checkbox 1 goes with combobox 1, 2 goes with 2, etc.

When a particular button is pressed, I would like the combobox to clear it's value if it's checkbox value is false. I was hoping there would be an easier, more efficient process than what I have, which works and is as follows:

VB:

Private Sub cmdbTest_Click()
z = "dep"
Call TestSub(z)
z = "loc"
Call TestSub(z)

[Code]...

I currently have 8 comboboxes. Tags were used for the comboboxes during another process which are not used on the checkboxes, so that option is out.

View 1 Replies View Related

Dynamic Chart - Control In Combobox

Jan 30, 2014

Trying to create a dynamic chart herewith control in combo box.

Chart 2.xlsx

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

Return Chosen Value From ComboBox Control

Oct 25, 2006

I am trying to find a formula that references a cell on a spreadsheet (H7) which is really a Combo Box that is located on a range of cells K25:K30. I want to put a formula in H18, but of course, the combo box always references the range on K. i have tried the $K$25:K430, but i don't know what i am doing. The drop down menu and everything works fine, but the data IS stored elsewhere. HOW do i tell Excel to look at K25:K30,(depending on choice within combo) and then ad H8:H19? I have looked, but all answers are for forms. I am doing this for final exercise for a university course, and (hmmhmm) must folllow obtuse instructions.

View 7 Replies View Related

UserForm Control TextBox Or ComboBox

Jun 5, 2008

If this control is a TextBox, I would like to read the Text property,
and if it's a ComboBox, I would like to read the Value property.

Public Function readValue(c As Control) As String
If (TypeName(c) = "TextBox") Then
' convert the Control to TextBox then put readValue = c.Text
Else
If (TypeName(c) = "ComboBox") Then
'convert the Control to ComboBox then put readValue = c.Value
End If
End If
End Function

View 9 Replies View Related

Clear Method Range Class Failed - Combobox Code

Apr 23, 2014

I have two separate workbooks, the first is called Job test and is to be used as a template for quoting jobs, the second is called Fixtures and is a database of fixtures that are organized in table. I keep them separate as multiple jobs will use the Fixtures DB workbook and I want to be able to update it and add new fixtures in one area. In order to facilitate this I have a macro that opens the Fixtures DB workbook anytime that the Job test workbook is opened. In the Job test workbook I have multiple dropdowns that I hope to make dependent or cascading by means of filtering the Fixtures DB workbook. The issue I have run into is with the following code.

Code:
Private Sub CLightType_Change()'Filters LampType
Workbooks("Fixtures.xlsm").Sheets("CLampType").Activate
Workbooks("Fixtures.xlsm").Sheets("CLampType").Cells.Clear
Workbooks("Fixtures.xlsm").Sheets("Fixtures").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Workbooks("Job test.xlsm").Sheets("Criteria").Range("A1:A2"), CopyToRange _
:=Range("A1"), Unique:=False
[Code] ......

What this code hopes to achieve is that when I change the value in the LightType dropdown, the Fixtures DB workbook will automatically filter the data once to a different sheet(CLampType), then get only unique values for LampType in column O. I have set up a dynamic range for column O so as to populate my next combobox, LampType.

This should all be fairly simple and straightforward, however I am running into "Run-time error '1004': Clear method of Range class failed." when I try to execute the line to clear the worksheet, and also have an error when I try to filter the data via macro. The strange part is all of this can be done manually without a problem, and moreover I have tried recording the process and using the recorded version. Even stranger yet is that when I add an "on error resume next" before everything, the code works fine but keeps looping and acts finicky(I don't want to simply resort to this as a solution). I have also tried setting this macro up inside the Fixtures workbook instead and calling it from the combobox change, to no avail.

View 1 Replies View Related

Form Control Combobox And Multiple Columns

Oct 22, 2013

I want to display mutliple columns in a form control combobox. Tried activex and the easy part was the multiple columns. I couldn't get the activex combobox to update properly when a cell value changed.

The form control combobox updates properly but it is only displaying the first column of my named range.

View 6 Replies View Related

Combobox Control (change To Respective Given Color)

Jun 1, 2009

I have a problem with combobox,
when an item in a combobox is selected (control tool box combobox)
then the color of the cell in excel worksheet have to change to respective given color

View 9 Replies View Related

Control Combobox Output (Drop Down List)

Jul 27, 2006

1.
I have a userform with a combobox that displays unique values from column A of the worksheet. I have a number of fields for each record going from columns A to J and A1:J1 is headings.

When user selects any particular record from the drop down list, it displays all the related fields on the labels on userform.

The column J is not initially completed for every record. But users put their feedback in column J (which done via userform) as they go. This does not happen in any particular order.

I am wondering if I can make the combobox pickup entries with no values in their column J.....in other words, can the combobox ONLY display the records that havent got user feedbacks in front of them in column J ?

So once a record has recieved a feedback in column J, its not seen again in the combobox.....to avoid doubling up on feedbacks.

2.
Also, what code would I use to select the row source for the combobox?
As the number of entries in the worksheet are growing the combobox rowsource range needs to grow automatically.

My worksheet is called "ComplaintData" which hidden, Combobox is called "complaintdis" and it needs to display values from column A in the dropdownlist.

View 4 Replies View Related

How To Change Font Size Within Combobox (Form Control)

Feb 1, 2014

I have a combo Box (Form Control) in my spreadsheet which is basically used as a drop down menu.

How do I change the font size of the text that appears in the box?

View 2 Replies View Related

Select Worksheet Based On ComboBox Control Choice

Sep 1, 2006

i have a user form with 4 combo boxes and one text box. what i've tried to do is make it so that if the month combo box reads january, then all of the info is placed in a worksheet called january. at the moment it is putting all the info inputted into one sheet. if some one could take a peek at the code below.

If cbomonth.Value = January Then
Sheets("January").Select

Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty.....................

View 9 Replies View Related

ComboBox Control Source Update With Multiple UserForms

Oct 12, 2006

Excel – Forms – Combo Box, cell updating.

I have a form (the main one) which accesses a second form, which accesses a third form. All forms have a combo box, control button and a text box.

The items selected in the control boxes are entered into cells (the base cells) in the spreadsheet, via “Control Source”. The text boxes access these cells and show the selected items, which are then copied to other cells via a macro on activating the control button in the main form.

The main form stays open while other items are selected and transferred. Some of the items may not be changed (reselected) as they may be common. The problem is that after a number of items have been selected and transferred, the base cells for the second and third text boxes do not update after a selection from the combo boxes, and they continue to show a previously selected item.

The problem can be solved by closing the 2nd and 3rd forms and starting again. What can I do keep the cells updating without closing the form down?

View 9 Replies View Related

Control Toolbox ComboBox To Return Chosen List Number

Aug 18, 2006

I'm using combo boxes. Initially I used combo boxes from the Forms toolbar, however the text in the combo box was to small. Now I'm using combo boxes from the Control tool bar. However, i would like the link cell to show the number of the entry in the list (like the forms control box) and not the actual entry. Is there an option I need to select in properties, or is there some VB code I can attach to the combo box ?

View 4 Replies View Related

Prevent Combobox Change Running Other Control Event Procedures

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

Avoid Hard Coding Control Name Inside Control Event Procedure?

Mar 4, 2014

Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?

It might be clearer to explain by a dummy code example:

[Code] ......

I'm seeking what I would need to replace Line1 with.

View 11 Replies View Related

Select Next Control / Cell On Worksheet After Enter In Control

Jan 9, 2008

Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?

View 9 Replies View Related

Clear Macro NOT Clear Formula?

Aug 15, 2014

I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.

here's atleast the vb for the macros:

VB:
Sub clearclientinfo() '
'
'
'
Sheet5.Select

[Code].....

View 9 Replies View Related







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