Select a Combo Box when a SPECIFIC Cell Changes

**coachdave** · 11-29-2008, 06:05 PM

Hi,

I am new to this site and relatively new to programming.

I am trying to use vba to give a combo box focus only when cell D2 (a cell merge of d2,d3 and d4) changes.

So, If I enter a number in cell D2 and hit ENTER or use one of the arrows I want the combo box to get selected (get focus). I don't want this action to take place when other cells are acted upon this way. ONLY D2.

Thanks.

**Kenneth Hobson** · 11-29-2008, 06:20 PM

Welcome to the forum!

If you are using a Control Toolbox combobox named ComboBox1, right click your sheet, View Code and paste:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$D$2" Then Exit Sub
  ComboBox1.Activate
  ComboBox1.DropDown
End Sub

**Richard Buttrey** · 11-29-2008, 06:21 PM

Hi,

You'll need to go into the VB Environment and use the Worksheet SelectionChange event and use an instruction like for instance

If Target = Range("D2") Then
    'whatever you want your code to do
End If

HTH

**coachdave** · 11-29-2008, 07:32 PM

Both of you answers seem simple enough but I am getting no action ahen cell D2 is selected and I hit enter or use the arrows. The combobox is not active and the list does not drop down.

The name of the ActiveX control is cboSeasonal. When I name it in the code the option to activae and to dropdown are available.

Here is how I entered the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address <> "D2" Then Exit Sub

cboSeasonal.Activate
cboSeasonal.DropDown

End Sub

**Kenneth Hobson** · 11-29-2008, 08:06 PM

Maybe you want to use the Select event range than Change? Enter causes no change. Not sure what you mean by arrow key. Of course arrow keys do not trigger the Change event either.

Your code will always exit because you are comparing an absolute address to relative address. Change one or the other. Notice that I used $D$2.

Typically, I would use Intersect() to check for more than one cell that might change. I gave you the more simple example.

**Kenneth Hobson** · 11-29-2008, 08:14 PM

Maybe you want to use the Select event range than Change? Enter causes no change. Not sure what you mean by arrow key. Of course arrow keys do not trigger the Change event either.

Your code will always exit because you are comparing an absolute address to relative address. Change one or the other. Notice that I used $D$2.

Typically, I would use Intersect() to check for more than one cell that might change. I gave you the more simple example.