keeping an object in view

[Nigel]

I have written this code which works fine but i would like to know if it is
possible in some way to have the code execute but not lose the combo box
while the code darts back and forth across the sheet.

Private Sub ComboBox5_LostFocus()
' runs when combo box loses the focus
If Range("AP9") = False Then
With Worksheets("INPUT DAMAGE")
.Range("D2").Copy
.Range("AP12").Offset(rowOffset:=Range("AP10"), columnOffset:=0) _
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Application.CutCopyMode = False
.Range("AP13").sort Key1:=Range("AP14"), Order1:=xlAscending,
Header:=xlGuess _
, OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom
End With
Range("A1").Select
End If
End Sub

Thanks in advance, Nigel

 

[michael.beckinsale]

Hi Nigel,

Not quite sure what exactly your asking for here but if you want to
stop the code 'flashing' then use

Application.ScreenUpdating = False

however because the code is triggered when the combobox looses focus
it cant be guaranteed that it will be in view while the code is
running as the user could simply select a different sheet!

If you are a bit more sprecific l am sure we can help

HTH

Michael

 

[Nigel]

Hi Michael

Ok. The combo box is one of many and i was hoping that the page would not
scroll right and down to the location of the list while the code is running,
enabling the user to make selections in other combo boxes without waiting for
the code to finish.
It will also look more professional if the sheet remains static.

Thanks Nigel

 

[michael.beckinsale]

Hi Nigel,

inserting :

Application.ScreenUpdating = False

as your 1st line of code will stop that movement for you whilst the
code is running. Depending on which version of Excel you are running
it is set back to True when the procedure ends. Your curent code
selects cell A1 so that will be the cell selected cell when the code
ends, perhaps you could change this so that the next combo box is
selected?

HTH

Michael

 

[Nigel]

Hi Michael

It works great thankyou. Thats a great idea but how do i get the next combo
box to take the focus?

Many thanks Nigel

 

[michael.beckinsale]

Hi Nigel,

Something like this:

Sub cbxFocusTest()
ActiveSheet.combobox1.Activate
End Sub

HTH

Michael