ActiveX comboboxes on worksheet (lost focus problems)

[scott]

I have a 6 comboboxes(control toolbox) on a worksheet. All of these
boxes have a KeyDown event associated with them used to navigate
around the sheet with the TAB and ENTER keys. 4 of them also have a
LostFocus event. When I navigate from one particular
combo(cboPersArea) of those 4 to any of the other 3, my code runs but
seems to ignore the fact that I turn off the screen updating. This
only occurs when moving from the cboPersArea and does not occur if
moving to either of the combos without their own Lost Focus Events.

I assume it something to do with the code behind cboPersArea so here
it is:
___________________________________________
Private Sub cboPersArea_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
cboReasonForOpening.Activate
End If
End Sub
_________________________________________
Private Sub cboPersArea_LostFocus()
code = cboPersArea.Value

cboCostCenter.Value = ""

FindData
End Sub
___________________________________________

The FindData procedure resides in Module1 and works fine except when
moving to another combobox with a lost focus event on it. I will put
that code in the first reply so that it doesn't get in the way here. I
tried using the CHANGE event for the combos originally but the user
could only type one letter in to the combo and the change event would
take over. I keep thinking this is simple but its getting late and my
mind doesn't want to fuction anymore.

Help Please!

 

[scott]

Here is the code in the FindData procedure:

Public Sub FindData()

Application.Cursor = xlWait

Application.ScreenUpdating = False

Sheet8.Unprotect

Range("clearrange").Clear
Range("newhirepersarea").Value = code

'code = ComboBox1.Value

Thanks in advance!!!!!!!!

Worksheets("PersAreaMaster").Activate
[a2].Select

Do While ActiveCell <> code
ActiveCell.Offset(1, 0).Select
Loop

rowCount = 0
PersCodeCount = 0
test = True

Do While ActiveCell = code

PersCodeCount = PersCodeCount + 1

If test = True Then
'FIND FIRST ROW OF CURRENT RANGE
firstRow = ActiveCell.Row
test = False
End If
rowCount = rowCount + 1
ActiveCell.Offset(1, 0).Select
Loop



ActiveCell.Offset(-1, 0).Select
lastRow = ActiveCell.Row

For L = 0 To 6


Set AllCells = Range("" & Chr(65 + L) & firstRow & ":" & Chr(65 +
L) & lastRow & "")

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell

On Error GoTo 0

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

Range("clearrange").NumberFormat = "@"

Worksheets("dynrange").Activate
Range("" & Chr(65 + L) & "2").Select
For Each Item In NoDupes
ActiveCell = Item
ActiveCell.Offset(1, 0).Select
Next Item

For k = NoDupes.Count To 1 Step -1
NoDupes.Remove (k)
Next

Worksheets("PersAreaMaster").Activate


Next

ActiveCell.Offset(-(PersCodeCount - 1), 7).Select
Range(Selection, ActiveCell.Offset(0, 1)).Select
Range(Selection, ActiveCell.Offset((PersCodeCount - 1), 0)).Select
Selection.Copy
Worksheets("dynrange").Activate
[h2].Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.CutCopyMode = False

Worksheets("PersAreaMaster").Activate
Range("a" & lastRow).Select
ActiveCell.Offset(-(PersCodeCount - 1), 9).Select
Range(Selection, ActiveCell.Offset(0, 2)).Select
Range(Selection, ActiveCell.Offset((PersCodeCount - 1), 0)).Select
Selection.Copy
Worksheets("dynrange").Activate
[j2].Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.CutCopyMode = False

Worksheets("Requisition & Workflow").Activate

Sheet8.Protect

Application.ScreenUpdating = True

Application.Cursor = xlDefault

End Sub

 

[scott]

Just in case the problem resides in the code for the other 3 combo
boxes. When I comment out the lost focus events, all the code above
runs fine. Here is the code:

Private Sub cboBusSegment_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
ChangeInsiteDiv
Range("E15").Select
End If

End Sub
________________________________________________
'Private Sub cboBusSegment_LostFocus()
'ChangeInsiteDiv
'End Sub
_______________________________________________
Private Sub cboCostCenter_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
ChangeAdminCode
Range("E24").Select
End If

End Sub
____________________________________________________
'Private Sub cboCostCenter_LostFocus()
' ChangeAdminCode
'End Sub
____________________________________________________
Private Sub cboReasonForOpening_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
ChangeEDCEmpStatus
cboCostCenter.Activate
End If

End Sub
__________________________________________________________
Private Sub cboReasonForOpening_LostFocus()
ChangeEDCEmpStatus
End Sub

Thanks in advance!!!!!!!!

 

[scott]

bump

 

[scott]

Ok, new morning, fresh brain. Please correct me if i'm wrong but I'm
thinking that the lost focus event of the cboPersArea kicks in after
the next combobox receives focus. Therefore the next combo box is
essencially losing focus which kicks off it's lost focus event so the
two combos are fighting each other. I still don't know why the
screenupdating isn't turning off but, at this point, thats a moot
point. This means I'm in search of another event to make this work.
Any suggestions?

 

[Susan]

a) try putting application.screenupdating=false in the private subs,
too.
b) you might have to programatically re-focus the focus.
cboPersArea.set focus


susan, not-a-guru