VBA - Worksheet - Change/SelectionChange: Not updating as expected

Question

Hello,

I have a sheet where I enter times (from and to) in columns H and I and columns J and K are supposed to show duration minutes and hours, respectively. Below is the code I came up with. I placed it in the Worksheet (not module) as I want the result to be volatile (instant). What it does is it updates every time I select a row of the updated cell. It also cleared contents of the header of the columns J and K. I keep it as SelectionChange. When I set it up as Change it freezes and I have to shut down excel.

How do I make it right so it returns as expected?

Thank you all in advance.

Alec.

**********************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim r, c

    Worksheets("NonWrench_Wrench Time").Select

    r = Selection.Row

    c = Selection.Column

    If Cells(r, 8) = "" Or Cells(r, 9) = "" Or Cells(r, 9) < Cells(r, 8) Then

    Cells(r, 10) = ""

    Cells(r, 11) = ""

    Else:

    Cells(r, 10) = (Cells(r, 9) - Cells(r, 8)) * 1440

    Cells(r, 11) = (Cells(r, 9) - Cells(r, 8)) * 1440 / 60

    End If

    On Error GoTo 0

End Sub

Answer 1

Alec,

Re:  changes on worksheet

There is a code module for each worksheet.

There is a standard/general code module.

There is a Class module.

And there is a UserForm module.

With code using Events (your example), it is necessary (most of the time) to turn off Event processing to prevent runaways/crashes...

A change runs the code which changes something which causes the Event code to repeat and so on.

The code statement to turn off events is:  Application.EnableEvents = False

Using this statement requires it to be set to True before exiting the code.

That means you need an error handler to trap errors and enable events.

Also, every exit point in the code needs to enable events.

That said, why not just use formulas on the worksheet to handle your calculations?

'---

Free Exce add-ins and workbooks at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 2

Alec,

Re:  changes on worksheet

There is a code module for each worksheet.

There is a standard/general code module.

There is a Class module.

And there is a UserForm module.

With code using Events (your example), it is necessary (most of the time) to turn off Event processing to prevent runaways/crashes...

A change runs the code which changes something which causes the Event code to repeat and so on.

The code statement to turn off events is:  Application.EnableEvents = False

Using this statement requires it to be set to True before exiting the code.

That means you need an error handler to trap errors and enable events.

Also, every exit point in the code needs to enable events.

That said, why not just use formulas on the worksheet to handle your calculations?

'---

Free Exce add-ins and workbooks at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Hello,

Thanks for your response. Originally I did have formulas in my spreadsheet. The table in the spreadsheet has 1000 lines with multiple conditional formats. With the empty (not filled in) state it is heavy and will likely crash. That is the reason I deiced to "codify" the columns as it will make a little lighter.

Thank you for your suggestion which is very relevant to this case, but I still need VBA coding assistance with my question.

Thanks.

Answer 3

To:  Alec

Re:  revised code

The following code appears to work, give it a try...

(code goes in the worksheet module)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo BadEntry

  Dim R As Long

  If Target(1).Column < 8 Or Target(1).Column > 9 Then

    Exit Sub

  Else

    R = Target(1).Row

    If Me.Cells(R, 8) = vbNullString Or Me.Cells(R, 9) = vbNullString Or _

       Me.Cells(R, 9) < Me.Cells(R, 8) Then

       Me.Cells(R, 10) = vbNullString

       Me.Cells(R, 11) = vbNullString

    Else

      'not really necessary as code exits if columns 10 / 11 are changed.

       Application.EnableEvents = False

       Me.Cells(R, 10) = (Me.Cells(R, 9) - Me.Cells(R, 8)) * 1440

       Me.Cells(R, 11) = (Me.Cells(R, 9) - Me.Cells(R, 8)) * 1440 / 60

    End If

  End If

  Application.EnableEvents = True

Exit Sub

BadEntry:

  VBA.MsgBox Err.Description & " - " & Err.Number

  Application.EnableEvents = True

End Sub

Answer 4

To:  Alec

Re:  revised code

The following code appears to work, give it a try...

(code goes in the worksheet module)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo BadEntry

  Dim R As Long

  Dim C As Long

  If Target(1).Column < 8 Or Target(1).Column > 9 Then

    Exit Sub

  Else

    R = Target(1).Row

    C = Target(1).Column

    If Me.Cells(R, 8) = vbNullString Or Me.Cells(R, 9) = vbNullString Or _

       Me.Cells(R, 9) < Me.Cells(R, 8) Then

       Me.Cells(R, 10) = vbNullString

       Me.Cells(R, 11) = vbNullString

    Else

      'not really necessary as code exits if columns 10 / 11 are changed.

       Application.EnableEvents = False

       Me.Cells(R, 10) = (Me.Cells(R, 9) - Me.Cells(R, 8)) * 1440

       Me.Cells(R, 11) = (Me.Cells(R, 9) - Me.Cells(R, 8)) * 1440 / 60

    End If

  End If

Application.EnableEvents = True

Exit Sub

BadEntry:

  VBA.MsgBox Err.Description & " - " & Err.Number

  Application.EnableEvents = True

End Sub

Thanks for your response.

It worked but not completely responsive. When the entries are entered in columns H and I only that row gets updated. If several lines of data are copied over to H and I, then the outcome shows up only on the first row (blue circle), the rest (below) show blank. Also, when the entry data is deleted (black circle) the results in columns J and K still show and not removed. Please see below the image of the spreadsheet when I use your code. Thank you.

https://learn-attachment.microsoft.com/api/attachments/9b94ff28-8f9f-4b2e-adac-1f121e4d0f66?platform=QnA

Answer 5

Alec,

Up to this point it was one row at a time. 

In my view you enlarged the scope.

Here you go...

Note:  rngCell is rngCell(1, 1)

          code searches thru Column 9 (I)

          rngCell(1, 0) is in column H

          rngCell(1, 3) is in column K

'2nd revision

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo BadEntry

  Dim rngCell As Excel.Range

  Dim rngAll As Excel.Range

  If Target(1).Column < 8 Or Target(1).Column > 9 Then

    Exit Sub

  Else

    Set rngAll = Application.Intersect(Target.Cells.EntireRow, Me.Columns("I"))

    For Each rngCell In rngAll.Cells

       If rngCell(1, 0).Value = vbNullString Or rngCell.Value = vbNullString Or _

          rngCell.Value < rngCell(1, 0).Value Then

          rngCell(1, 2).Value = vbNullString

          rngCell(1, 3).Value = vbNullString

       Else

          Application.EnableEvents = False

          rngCell(1, 2).Value = (rngCell.Value - rngCell(1, 0).Value) * 1440

          rngCell(1, 3).Value = (rngCell.Value - rngCell(1, 0).Value) * 1440 / 60

       End If

    Next 'rngcell

  End If

Application.EnableEvents = True

Exit Sub

BadEntry:

  VBA.MsgBox Err.Description & " - " & Err.Number

  Application.EnableEvents = True

End Sub

'---

Free Exce add-ins and workbooks at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents