Combobox Value

[Amoni]

Hi!!

I´m having trouble with subtract the value of the current item in my
combobox, I´ve created an combobox with to items A and B in excel. Later I
need to reach the the choosen value. So how to i get the value that is
displayed in the combobox. Im using value now. But this gives me the offset
value doesnt change when I change comboitem.

Here is my code:


Sub testCombo()
Dim oWs As Worksheet
Dim oOLE As OLEObject
Dim combo As ComboBox
Set oWs = ActiveSheet
Dim cell As Range


'To set with a cell
With Range("F8")
Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.combobox.1", _
Left:=.Left, Top:=.Top, Width:=.Width,
Height:=.Height)
.ShrinkToFit = False
.MergeCells = False
.WrapText = True

Set combo = oOLE.Object
combo.Font.Size = 8
combo.ForeColor = RGB(255, 255, 255)
combo.AddItem ("A")
combo.ForeColor = RGB(0, 0, 255)
combo.AddItem ("B")
combo.ListIndex = 0
combo.Placement = xlMoveAndSize
combo.PrintObject = True


End With

Set cell = Cells(13, 5)
MsgBox combo.Value

End Sub
...............................................
Can somebody plz help me, thx

 

[Tom Ogilvy]

You code worked fine for me. It displayed A in the msgbox.


Later I changed the value manually and then ran:

Sub showbox()
MsgBox ActiveSheet.OLEObjects(1).Object.Value
End Sub

and it returned the correct value.

 

[Bob Phillips]

Amoni,

What do you mean that you are getting the offset, do you mean you get its
index not the A or B? Msgbox .Value is giving A in your code as would be
expected.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[K Dales]

Are you trying to get the selected value to show in the cell (13,5)? You
have done nothing to link the combobox to a cell; you would need to add that
and by using a forms combobox you would need to programatically add the code.
It would be easier to use the Excel Dropdown; oWs.Dropdowns.Add.

 

[Amoni]

It didn´t work for me. So do u mean that after running my code that creates a
combobox with two alternative A and B. You can change form A to B and reach
the current Value in the combobox using:

Sub showbox()
MsgBox ActiveSheet.OLEObjects(1).Object.Value
End Sub

and it will return B?

 

[Amoni]

With offset I meant the first item added to the combobox and that i A. But I
want to when changing manually in excel from A to B, my msgBox to display B
and not still A. Do u understand my problem?

//Amoni

 

[Amoni]

Hi !
Yes Im trying to get the selected value to display in cell(13,5) just to
notify if the action is correct.
Didnt understand what u meant with "to add that
and by using a forms combobox you would need to programatically add the code.
"

 

[Tom Ogilvy]

If B is selected and your combobox is the first in the OleObjects
collection. My suggestion is illustrative. I have no idea how you have
your sheet set up, but the suggestion could be altered to specifically
address your problem.

 

[K Dales]

It would have been easier to set up as the built-in Excel .Dropdown object -
the code would have been easier, in my opinion. But what you need to do with
your combo is to set the LinkedCell property to be the address of the cell
you want.

put this line in the code where you are setting the combo's properties:
combo.LinkedCell = Cells(13, 5).Address
Then I think it will behave the way you want.

 

[Bob Phillips]

Yes, and I still don't see the problem. If you add this to the combo click
event

MsgBox Comobox1.Value

You get either A or B when you select from it.

--

HTH

RP
(remove nothere from the email address if mailing direct)