CODEWITHSUNDEEP
vbaexcel
jz3
jz3

Reputation: 521

VBA controlling user focus during iteration

quick question that shouldn't really require any of my code. During my application, I use a 'for each' loop, that loops through a range of cells, and right now when the user runs it, the focus of the screen follows the selection as it jumps from cell to cell within that range. Is there any way to prevent the focus from following the path of the loop during the iteration, maybe have the user simply see something that says "processing" until it is complete?

Thanks in advance, I appreciate any and all help.

Code:

Dim iLoop As Integer

For iLoop = 5 To lastRow

 Sheets("Planners").Activate
 Range("J" & iLoop).Select
 Range(Cells(iLoop, 9)).Select

 With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="Yellow, Orange, Green, Red"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = "Invalid Entry"
    .ErrorMessage = "Please choose from dropdown"
    .ShowInput = True
    .ShowError = True
 End With

Next iLoop

Upvotes: 0

Views: 418

Answers (2)

GSerg
GSerg

Reputation: 78185

Apparently you are using .Select in your code. There is a chance it's a right thing to do, but most of the time it isn't.

So stop using Select and ActiveCell and refer to cells using indices/references.

The above would be the correct solution.
The wrong solution would be to use Application.ScreenUpdating = False before the loop and Application.ScreenUpdating = True after the loop.

Edit:

Dim iLoop As long
dim w as worksheet

set w = Worksheets("Planners")

For iLoop = 5 To lastRow

  With w.cells(iLoop, 9).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="Yellow, Orange, Green, Red"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = "Invalid Entry"
    .ErrorMessage = "Please choose from dropdown"
    .ShowInput = True
    .ShowError = True
  End With

Next

But given this code, you don't need a loop at all:

dim w as worksheet
set w = Worksheets("Planners")

With w.Range(w.cells(5, 9), w.cells(lastRow, 9)).Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  xlBetween, Formula1:="Yellow, Orange, Green, Red"
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = ""
  .InputMessage = "Invalid Entry"
  .ErrorMessage = "Please choose from dropdown"
  .ShowInput = True
  .ShowError = True
End With

Upvotes: 1

Gaijinhunter
Gaijinhunter

Reputation: 14685

All you need to do is add application.screenupdating = false at the start of you code (after the sub definition of course) and application.screenupdating = true at the end before 'end sub'.

It's good practice to add this to all your sub and functions as it will make them run faster as well.

Upvotes: 0

Related Questions