Excel: Focus from cell/combo to combo/cell [SELF-RESOLVED]

[krtxmrtz]

In excel, after I've typed data into a cell and hit return I want the focus to be set on a combobox, and after I've selected an item from the dropdown list I want the focus to be transferred to a different cell . How can I do this (with code)?

[krtxmrtz]

Well, from combo to cell this code seems to work,

Private Sub ComboBox1_LostFocus()
    If CheckBox4.Value Then
        Range(ThisWorkbook.Names("Cell12")).Select
    Else
        Range(ThisWorkbook.Names("Cell13")).Select
    End If
End Sub

but I still can't figure out how to do the first part.

[krtxmrtz]

bump

[westconn1]

if you have code worksheet_selectionchange sub, you should be able to do something based on the cell you would be going to move into

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Mid(Target.Address, 2, 1) = "E" Then Stop
End Sub

dunno quite where to go from there, but at least when you press enter it will run this sub

pete

[krtxmrtz]

Finally I made it.

'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
'Sheet1 is protected so that only a 
'few unlocked cells can be selected
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