After I've typed data + return key to a specific cell, say C5, I want the
focus to be automatically transferred to a combobox in the same worksheet.
What code do I need and where -in what event handler- must it be placed?
Then, after I've selected one of the combo items (and maybe -maybe not- hit
the return key as well) I want the focus to be automatically set to some
other cell, such as C8. Same question as above.

The idea is to cycle through a number of cells and comboboxes so that no
cells can be selected/activated that don't require input data.

Lluis,

Place the following code in the sheet module. You will need to change the
"ComboBox1/ComboBox2" references in the code to match the name of your combo
boxes. Play around with the 'Activate' and 'DropDown' methods for the
desired effect (ie. delete 'ComboBox1.Activate:' and ': ComboBox2.DropDown'
to see what changes).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ComboBox1.Activate:
ComboBox1.DropDown
If Not Intersect(Target, Range("C8")) Is Nothing Then ComboBox2.Activate:
ComboBox2.DropDown
End Sub
Private Sub ComboBox1_Change()
Range("C8").Select
End Sub
Private Sub ComboBox2_Change()
Range("C11").Select
End Sub


Mike

"Lluis Escude" wrote:

After I've typed data + return key to a specific cell, say C5, I want the
focus to be automatically transferred to a combobox in the same worksheet.
What code do I need and where -in what event handler- must it be placed?
Then, after I've selected one of the combo items (and maybe -maybe not- hit
the return key as well) I want the focus to be automatically set to some
other cell, such as C8. Same question as above.

The idea is to cycle through a number of cells and comboboxes so that no
cells can be selected/activated that don't require input data.

Using the Worksheet_Change event handler does not seem convenient as I often
don't need to change the contents of "C5". So in this case the event isn't
triggered. I'm now experimenting with the Worksheet_SelectionChange event
sub, and I'll be playing around for a while. If I make it I'll drop by to
post the final code.

"crazybass2" wrote:

Lluis,

Place the following code in the sheet module. You will need to change the
"ComboBox1/ComboBox2" references in the code to match the name of your combo
boxes. Play around with the 'Activate' and 'DropDown' methods for the
desired effect (ie. delete 'ComboBox1.Activate:' and ': ComboBox2.DropDown'
to see what changes).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then ComboBox1.Activate:
ComboBox1.DropDown
If Not Intersect(Target, Range("C8")) Is Nothing Then ComboBox2.Activate:
ComboBox2.DropDown
End Sub
Private Sub ComboBox1_Change()
Range("C8").Select
End Sub
Private Sub ComboBox2_Change()
Range("C11").Select
End Sub


Mike

"Lluis Escude" wrote:

After I've typed data + return key to a specific cell, say C5, I want the
focus to be automatically transferred to a combobox in the same worksheet.
What code do I need and where -in what event handler- must it be placed?
Then, after I've selected one of the combo items (and maybe -maybe not- hit
the return key as well) I want the focus to be automatically set to some
other cell, such as C8. Same question as above.

The idea is to cycle through a number of cells and comboboxes so that no
cells can be selected/activated that don't require input data.

This is what I've done so far.

I have placed this in a module:
Global oldtarget As Range

Then , in ThisWorkbook:
Private Sub Workbook_Open()
'StartCell is a named cell where I want to start entering data
Set oldtarget = Range(ThisWorkbook.Names("StartCell"))
End Sub

In Sheet1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
'If the MyCell has just lost the focus, activate the combo
If oldtarget.Address= Range(ThisWorkbook.Names("MyCell")).Address Then
ComboBox1.Activate
Set oldtarget = Target
End Sub

It works fine, but I still have to solve the second part, select a specific
cell after an item from the combo has been selected.

I finally have the code up and running (Sheet1 is protected so that only a
few unlocked cells can be selected):
========================================
In a module:
------------------------------------------------------------------------
Global oldtarget As Range
========================================
In ThisWorkbook:
------------------------------------------------------------------------
Private Sub Workbook_Open()
Range(ThisWorkbook.Names("StartCell")).Select
Set oldtarget = Range(ThisWorkbook.Names("StartCell"))
With ThisWorkbook.Sheets("Sheet1")
.EnableSelection = xlUnlockedCells
.Protect
End With
End Sub
========================================
In Sheet1:
------------------------------------------------------------------------
Private Sub ComboBox1_Click()
Application.Range("NextCell").Select
End Sub
------------------------------------------------------------------------
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = vbKeyReturn Then Application.Range("NextCell").Select
End Sub
------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect
If oldtarget.Address = Range(ThisWorkbook.Names("StartCell")).Address
Then ComboBox1.Activate
Set oldtarget = Target
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
========================================