MrExcel Publishing
Your One Stop for Excel Tips & Solutions

ComboBox or DrowDown Selection to ActiveCell


Posted by Al on December 26, 2001 2:10 PM

I am using the follwing simple VBA

Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Value
End Sub

and lets assume my ComboBox list includes

Apple
Orange
Pear

the problem i cant solve follows:

Say Im in A1 (active cell) and select "Orange" from the ComboBox or DropDown list, works fine so far. Next I go to A2 (new active cell) if I want this activecell
to be "Orange" again, first I have to select another item from the ComboBox list (either Pear or Apple) and come back to select "Orange" otherwise it wont work...probably a focus problem...Can anyone help me with this ??

thanks

this mailing list excellent !


Posted by Joe Was on December 26, 2001 3:31 PM

This will add a drop-down list to all the cells in range A1:A25 and the source list is in column G.

Range("A1:A25").Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=G:G"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

You can add new items to the list and it will still work, its not a ComboBox but it works. Just add it to a Sub. You can also modify the code to work on a selection, it will add the drop-down list to the selected cell if the Range("A1:A25") is changed to Selection. JSW

Posted by Joe Was on December 26, 2001 3:36 PM

Selection codel

Note: to get the code below to work on the selected cell, just delete the first line: Range("A1:A25").Select and it will add the dropdown list to the active cell. JSW

Posted by Al on December 26, 2001 5:33 PM

Re: Selection codel

Hi Joe,

1) it is an excellent Sub., thanks alot.. but when i placed the sub in Sheet1 and used Formula1:="Sheet8!G:G"
it doesnt work. I know that Excel cond. Format cant reference to another worksheet. But is there any way to go around this problem ? just FYI, I placed this sub in 7 different worksheet, and they all supposed to reference to a 8th sheet where the list, say G:G, resides.

2) Also, is there any any to control the font size, fore/background color, etc. of the dropdown list ( Im talking about the characters that slides down when i click on the down arrow.
Note: I kow im asking too much but this will the last one on this subject. Thanks a bunch again

-al


Private Sub MySub()
Worksheets("Sheet1").Range("A2:A25").Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,Operator:=xlBetween,Formula1:="Sheet8!G:G"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub

Posted by Joe Was on December 27, 2001 9:46 AM

Re: Selection codel

No Al,
The list should be on each sheet. I write code to update each sheets list from a user utility on the main menu, at the same time. the code is then run from the current sheet to add a list box to the selection.

Like you I think there must be a way to have one master list on one data page, I just have not cracked that nut yet!

No again the DropDown list object was not written to adjust poperties, sorry I would like to change font color myself. JSW

Posted by Al on December 27, 2001 2:25 PM

Still Cant change the fromat of Option Button Label(FORMS)

Joe,

I cant still change the color and font of the Option Button from FORMS. I am talking about the Option Button label that reads Option Button1 when you drag a new option button from FROMS.

Formatting doesnt apply to the text.

thank
-al No Al,


Posted by Al on December 27, 2001 2:25 PM

Still Cant change the fromat of Option Button Label(FORMS)

Joe,

I cant still change the color and font of the Option Button from FORMS. I am talking about the Option Button label that reads Option Button1 when you drag a new option button from FROMS.

Formatting doesnt apply to the text.

thank
-al No Al,