Linking Form Combo Box value and cell

[sabascal]

Hi,

I inserted a combo box in a sheet ("Sheet1&quot an I linked it to a cell "A1".
It seem that depending of what I have selected in my combo box I am getting the index number i.e. the item number in my combo box list. Any way I can retrieve the value in "A1"?

Ideally if a different value is selected in the combo list it should update A1 .
Reciproquely, if A1 is changed to a different value in the combo list, it should automatically change the selection in the combo box.

Thanks

 

[tbl]

The simplest solution is to dump the Forms version and use the OLE version from the Control Toolbox. Link the box to a cell and you get the actual output of the box, not the index. Set Locked under properties to False, (most important), and whatever you write to the linked cell will be reflected in the box.

Richard

 

[sabascal]

Thanks Richard.
Actually this is what I did. it work fine when you have one combo box. When I added a second one it started screwing up, and I don't know why.

 

[tbl]

I'm working on a project where I have 45 combo boxes, OLE type on one sheet, and they are all working fine (at last). You DO need to refer to them using the sheet("XXXXX&quot.Range("yyyyyy&quot.ComboBox2.property to have any consistent success. As soon as you leave out the sheet name, the code tries to refer to the active sheet.

Richard

 

[sabascal]

Thanks - Finally I made it.
Now here is the problem I am having.
-I have 2 ComboBoxes: Combo1 and Combo2 with 2 associated Combo#_Change() procedure
- Combo1 is linked to cell A1
- Combo2 is linked to cell A2

My 2 Combo#_Change()are updating the sheet including A#
As a result I have an infinite loop.
When I call Combo1_Change() it updates A2, which calls Combo2_Change()....

How can I solve this?

 

[tbl]

Maybe this is not relevant, but I use an if statement at the beginning of each procedure.

If situation OK as it is ,Then End ' Crude but effective !

If for example, I wish to stop interlocking boxes from looping, I make the proceduce stop when my condition is fulfilled.

Very badly explained - been a long night !

Richard

 

[sabascal]

Can I have more help on this one

 

[tbl]

Of course. Just show us your code and I'll let you know if what I'm thinking of is applicable to your situation.

Richard

 

[sabascal]

Sure Richard here below my Code:


'================================
Private Sub NameComboBox_Change()
Application.EnableEvents = False
MsgBox "In NameCombo"
Dim rowNb As Integer
Dim tempID As String
rowNb = LookupRow(NameComboBox.Value, "NameRange&quot ' Just returning a row number you can set it to 1
If rowNb <> 0 Then
tempID = Worksheets(&quot;Database&quot.Range(&quot;IDRange&quot.Cells(rowNb - 1, 1)
Call Download(tempID) ' This is updating all the cells, including the onbe references by IDComboBox_Change()
Else
MsgBox &quot;Could not find the object you are referring to in the database&quot;
End If
Application.EnableEvents = True
End Sub



Private Sub IDComboBox_Change()
Application.EnableEvents = False
'MsgBox &quot;In IDCombo&quot;
Dim tempID As String
tempID = IDComboBox.Value
Call Download(tempID) ' This is updating all the cells, including the onbe references by NameComboBox_Change()
Application.EnableEvents = True
End Sub
'================================

Keep in mind that teh probelem is due to the fact that the Combo boxes are linked to cells whom value change one calling either of the _change() procedures

Thanks

 

[tbl]

OK, I think I understand. You want box 1 to change as it selects the Name from a list , but you don't want Box 2 to respond to changes in Box 1 and vice-versa.
You could check the value of the other box at the beginning of each procedure and stop the macro if it has changed. But it is much simpler to detect the changes per cell.
Stick this code in the SHEET where the boxes are.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'watch for changes in the input cells.
If Target.Address = Range(&quot;ComboBox1&quot.Address Then Call Calculate1
If Target.Address = Range(&quot;ComboBox2&quot.Address Then Call Calculate2
End Sub

Richard

 

[sabascal]

I am afraid it is not solving the problem.
Actually when using ComboBox1 it calls Calculate1
, which changes ComboBox2 which calls Calculate2(since a new change is detected in the worksheet) , which changes ComboBox1....and it keeps looping

Anything else?

 

[tbl]

There is always something else..

Just a thought, why are you using linked cells? If you remove the linked cells and use VBA to read the ComboBox.value, you do not have any interaction between the boxes.

Richard

 

[sabascal]

Maybe I should try to explain what I want to do. I might have the wrong approach.

I have a sheet where I want a user to use 2 Combo boxes to be able to select what is displayed on the sheet.
The 2 criteria are ID and Name. Those 2 criteria are also in a hidden cells on my sheet, which are linked to my ComboBoxes.

If the user selects an ID, the sheet must be populated with the info related to this ID (I take care of that), the other combo box should have it's value changed to the Name associated to the selected ID.
And vice versa with a Name selection.

Initially I was using controls ComboBox objects and not the Form ComboBox object.
IDComboBox was asigned a macro updating my sheet cells
NameComboBox was asigned a macro updating my sheet cells
The only problem I had was: When I was doing a selection with the IDComboBox, it was updating all the cells, including the one linked to NameComboBox, but NameComboBox was still displaying the old value.

Using form ComboBox objects, I resolve this problem, but now I have interaction problem between those 2 boxes.

How do I change the value of a control Combo box in VB; this might me the solution to my problem? What do you think?

Hope it clarifies.

Many thanks in advance

 

[tbl]

Changing the value is easy and does not require a linked cell. Get rid of the linked cells and use ...

Sub ChangeBox1()
ComboBox1.Value = 2
End Sub

Richard

 

[sabascal]

Still if I use FOrm Combo Box I can only access them through events which I don't want.
Reason is: even if I use a Sub to change my comboBox value, this is still a &quot;change&quot; and this will call my ComboBox_Change() sub.

I'd like to use normal ComboBox Controls. How do I refer to them in VB? (Actually when looking at the property I don't see a name, just in the range I have &quot;Drop Down 19&quot;

 

[tbl]

The combo boxes to use are the Control Toolbox VBA OLE type which show the value of the box directly in the linked cell.

When you change the box, Private Sub ComboBox1_Change() will execute and run whatever macro you require. The linked cell does not exist so there are no changes on the sheet until the macro runs, and the sheet can change as much as it likes but will have no effect on the boxes.
Another way to look at this is to use your code as you published it and just delete the 2 linked cells. Then tell me what does NOT work so that we can solve that. If you need the contents of either box, as I have already said, this would be IDComboBox.Value and NameComboBox.Value respectively.

Richard

 

[sabascal]

I followed your advise. No more links.
It keeps looping infinit!
It seem that no matter how you change you combobox.value it is perceved as a change.
I need to use a control calling the sub without a change event. For that I can use controls from the controls toolbox; problem I don't know hos to access there value in VBA

 

[tbl]

I find this difficult to understand. If there are no links then there is no change to the box when the sheet changes. I have this working in front of me. If you are worried that changing the value of the box by VBA causes the macro to loop, then I refer you to my original answer, which translates as..

Private Sub ComboBox1_Change()
CBvalue = ComboBox1.Value
if CBvalue = 2 Then End ' macro does not loop once the desired value has been entered
ComboBox1.Value = 2
End Sub

Another solution, if you don't want the interaction, is to provide an Update button and just use your sub from the button.

 

[sabascal]

Richard thanks for your help on this.

At the end I sticked with Toolboxfrom the Form toolbar.
I still getting an infinit loop. So here below you'll find what I did.
I declare a global counter.
in each _change I test if it's >1, if yes I reset it and End.
If not i increment it.

That's very dirty, but working.

I am still interesting in referencing a Combo Boxes from the control toolbar (apparently it's different)

Thanks anyway for your time & Advise on this
Seb


'================================
Dim i as integer
Private Sub IDComboBox_change()
If i > 1 Then
i = 0
End
Else
Dim tempID As String
Dim rowNb As Integer
tempID = IDComboBox.Value
rowNb = LookupRow(tempID, &quot;IDRange&quot - 1
Call DataSelection(rowNb)
NameComboBox.Value = &quot;&quot; & Worksheets(&quot;Data Input Form&quot.Range(&quot;PropertyName&quot.Value
i = i + 1
End If
End Sub