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,
