Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX comboboxes on worksheet (lost focus problems)
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX comboboxes on worksheet (lost focus problems)
Here is the code in the FindData procedu
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, befo=j NoDupes.Add Swap2, befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX comboboxes on worksheet (lost focus problems)
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!!!!!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX comboboxes on worksheet (lost focus problems)
bump
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX comboboxes on worksheet (lost focus problems)
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX comboboxes on worksheet (lost focus problems)
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 On Feb 13, 10:41 am, wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Lost with comboboxes and dropdowns in excel | Excel Discussion (Misc queries) | |||
Address of Cell that lost focus? | Excel Programming | |||
Textbox focus lost | Excel Programming | |||
Problems with worksheet containing ActiveX controls | Excel Programming | |||
Cell Lost Focus | Excel Programming |