0

I want to clear the contents of the cell after clicking the ok button in a message pop up window.

When the pop up window disappears, after clicking ok button umpteen times, the script terminates by throwing the below error

Run time error '-2147417848(80010108)':
Method 'Range of object'_Worksheet'Failed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("N4:O4")
If Not Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    If (Range("E9") = "" Or Range("F9") = "") Then
    MsgBox "Reopen Date and Closed Date should not be populated before calculating the Benefit Begin Date and Maximum Benefit Date"
    Sheets("Calculation Tool").Range("N4").Clear ----->Code written to clear the cells
   Else
      If (Range("N4") = "" Or Range("O4") = "") Then
         Set b1 = Sheets("Calculation Tool").CommandButton22
         b1.Enabled = False
      Else
         Set b1 = Sheets("Calculation Tool").CommandButton22
         b1.Enabled = True
      End If
  End If
End If

End Sub
Community
  • 1
  • 1
Sayanthan
  • 67
  • 1
  • 1
  • 7
  • try this; `Dim Output As Integer If Output = 1 Then Sheets("Calculation Tool").Range("N4").Clear` – GMalc Sep 18 '18 at 20:45
  • You don't need the () around your two If/Or ranges – GMalc Sep 18 '18 at 20:48
  • Is "Calculation Tool" the name of the sheet containing this code? – Tim Williams Sep 18 '18 at 23:39
  • Yes, it is name of the sheet containing this code. – Sayanthan Sep 19 '18 at 09:17
  • What action should be done to set the variable "output" = 1 in the above suggested code please? or should I need to just assign the value "1" to the variable "output" after the "msgbox" statement? – Sayanthan Sep 19 '18 at 09:23
  • If this code is in the `Calculation Tool` sheet module, then you'll need to disable / re-enable events - https://stackoverflow.com/questions/15761530/vba-worksheet-change-event-bypass – BigBen Sep 19 '18 at 13:59
  • Hi @BigBen should this enable/disable events be written inside the Worksheet_Change sub or should it be written as a separate sub? Can you able to help me on this please? – Sayanthan Sep 19 '18 at 19:53
  • Within - See [here](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) – BigBen Sep 19 '18 at 19:56
  • Hi @BigBen Thanks for the input.. I will get this tried and get back to you if I encounter any issues while implementing the same. – Sayanthan Sep 19 '18 at 20:31

1 Answers1

0

I wanted to tell @BigBen that his suggestion worked for me, but my low rep won't allow me to comment. The answer field is the only way of expression for me! So I might as well formulate a valid answer, here it goes. :)

So I had the same problem within a Worksheet_Change event macro, in this casual event macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RangeToCheck As Range

    Set RangeToCheck = ActiveSheet.Range("O3:O32")

(above is the line that triggered randomly that Run time error '-2147417848(80010108)' you encountered; on with the script)

    If Not Application.Intersect(Target, RangeToCheck) Is Nothing Then
        Target.Value = VBA.Replace(Target.Value, ".", ",")
        Debug.Print Target.Address, Target.Value
    Else
        Debug.Print "Not in RangeToCheck"
    End If

End Sub

Following BigBen's link, I found that the following code works fine :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RangeToCheck As Range

On Error GoTo enableEventsOn:
    Application.EnableEvents = False
    Set RangeToCheck = ActiveSheet.Range("O3:O32")
    Application.EnableEvents = True
On Error GoTo 0

    If Not Application.Intersect(Target, RangeToCheck) Is Nothing Then
        Target.Value = VBA.Replace(Target.Value, ".", ",")
        Debug.Print Target.Address, Target.Value
    Else
        Debug.Print "Not in RangeToCheck"
    End If

enableEventsOn:
    Application.EnableEvents = True

End Sub
mll
  • 44
  • 7