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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 30, 2014
Trying to create a dynamic chart herewith control in combo box.
Chart 2.xlsx
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
