Worksheet_Change passes a Range to another Sub, which errors out if the "change" was the Delete key

UWMmakow

New Member
Joined
Feb 25, 2014
Messages
30
I have a Worksheet_Change event that fires whenever the user makes a change to one of 18 cells:

Code:
If Not Application.Intersect(Target, Union(Range("Trend"), ... Range("Sales"))) Is Nothing Then
        If IsEmpty(Target) = True Then
            Call populateSampleInputs(Target)
        ElseIf IsNumeric(Target) = False Then
            MsgBox ("Entry must be a number.")
            Call populateSampleInputs(Target)
        End If
End If

If the user leaves the cell value as blank or not a number, the Target cell is passed to a sub that will fill the cell with a predetermined number. As you can see, if the entry is not a number, a message also displays.

The problem is that if you press the Delete key, the Worksheet_Change event immediately fires (normally you can make all manner of changes to the cell, but it won't fire until you click away), and it displays the MsgBox, and then it errors out in populateSampleInputs.

populateSampleInputs turns off screen updating, then is a series of 18 If-ElseIf statements like this:

Code:
If boxToPopulate = Sheets("Input").Range("Trend") Then
        ...
ElseIf boxToPopulate = ...

'boxToPopulate is the Target cell

The code always errors out on the first If statement; it doesn't matter which cell is the Target. It gives error 13, Type Mismatch.

In my futile attempts at troubleshooting, I can't get boxToPopulate's value to print (or Target's, for that matter). There's something special about the delete key and I don't know what it is. Anyone have an idea? It'll probably be something silly. Thanks for reading.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Regarding the Delete key triggering the Worksheet_Change event, that's because that action is the equivalent of Range.ClearContents. If you click in the Formula Bar, then back into the Cell and use the Delete key to delete all existing characters, then Worksheet_Change won't be triggered- so it's not the Delete key per se.

From your perspective, clearing the Delete key is the first of two steps of changing the value of the Cell; but Excel doesn't distinguish that from a user that intends to clear the Cell but not select another Cell; or a User that pastes a Null String into the target cell.

If you want no action unless the user has clicked away or taken some other action to select another cell, you could add a statement to the Worksheet_Change code that tests if the Target.Address=ActiveCell.Address (if so, exit Sub).


It's hard to know the reason for the Error 13 Type Mismatch without seeing how your code sets the data type and value of boxToPopulate.

It's a best practice to be explicit instead of using default properties of objects, so if you are trying to compare Values then use:
Code:
If boxToPopulate.Value = Sheets("Input").Range("Trend").Value

You might be intending to compare Addresses (which is better since a range you are not trying to match might have the same Value) in which case use:
Code:
If boxToPopulate.Address = Sheets("Input").Range("Trend").Address

Lastly, the IsEmpty function is used to test whether a variable has been initialized. The code you have returns the intended result; however it would be better to use...
Code:
If Target.Formula=vbNullstring Then
 
Upvote 0
Thanks for the tips. I replaced IsEmpty with what you suggested, and now that is where the sub errors out if you use the delete key. Even with the If Target.Address=ActiveCell.Address statement.

Here's what the Worksheet_Change looks like now, and even though it doesn't error out there anymore, here's what populateSampleInputs looks like:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Union(Range("Trend"), Range("Sales"), ... Range("SLSales"))) Is Nothing Then
[COLOR=#008080]    'there are 18 Ranges total[/COLOR]
        
        [COLOR=#008080]'if the Target cell is still active, then do nothing[/COLOR]
        If Target.Address = ActiveCell.Address Then
           Exit Sub
        End If
        
        [COLOR=#008080]'if cell is blank then repopulate with sample[/COLOR]
        If Target.Formula = vbNullString Then [COLOR=#ff0000]'Error 13 Type Mismatch here[/COLOR]
            Call populateSampleInputs(Target)
        
        [COLOR=#008080]'else if the cell contains a value that is not a number, display error and repopulate cell with sample[/COLOR]
        ElseIf IsNumeric(Target) = False Then
            MsgBox ("Entry must be a number.")
            Call populateSampleInputs(Target)
        
        [COLOR=#008080]'otherwise the user has entered a valid number, so un-italicize the cell[/COLOR]
        Else
            Target.Font.Italic = False
        End If
    End If
End Sub


[COLOR=#333333]

[/COLOR]Sub populateSampleInputs(boxToPopulate As Variant) [COLOR=#008080]'I've tried boxToPopulate as both a variant and Range[/COLOR]
    Application.ScreenUpdating = False
    
    If boxToPopulate.Address = Sheets("Input").Range("Trend").Address Then
        boxToPopulate.Value = 1.04
        boxToPopulate.Font.Italic = True
    
    ElseIf...

...
...

    ElseIf boxToPopulate.Address = Sheets("Input").Range("SLSales").Address Then
        boxToPopulate.Value = 987000
        boxToPopulate.Font.Italic = True
    End If
    
    Application.ScreenUpdating = True
End Sub




 
Upvote 0
That works in my mockup. Is there anything special about Target when that fails such as the use of merged cells?

Try adding this statement to your code to send some diagnostic information to the Immediate Window of the VB Editor

Code:
   Debug.Print "Address: " & Target.Address & " > Formula: " & Target.Formula & " > Type: " & TypeName(Target)
   If Target.Formula = vbNullString Then

Also consider using Application.EnableEvents to temporarily disable events while your code is changing the value of Target.
That will prevent the code from triggering itself.
 
Upvote 0
Yes actually, Target will always be a merged cell. It's been so long since I built the Input sheet that I completely forgot about that. I've heard of merged cells creating headaches in odd ways, could that be the cause of this issue?

With that debug line added, the macro errors out on that line.
 
Upvote 0
Yes, the merged cells are causing the problems with the previous code.

Looking at this further uncovered a different problem. If the user deletes or changes the value of an input cell without changing the activecell, nothing happens as you want. However if they then click away or otherwise select another cell, no Workbook_Change event is triggered and an invalid entry could be left in the input cell.

Below is some code that should address that issue and work with merged cells.

Paste into the Sheet Code Module...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim rPrevious As Range
 '--if previously selected cell was changed but not validated, then validate now
 On Error Resume Next
 Set rPrevious = Me.Range("ptrPreviousCell")
 On Error GoTo 0
 
 If Not rPrevious Is Nothing Then
   '--validate entry that was previously changed
   Me.Names("ptrPreviousCell").Delete
   Call ValidateEntry(rInput:=rPrevious)
 End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 If Application.Intersect(Target, Union(Range("Trend"), Range("Sales"))) Is Nothing Then
      On Error Resume Next
      Me.Names("ptrPreviousCell").Delete
      GoTo ExitProc
 End If
    'there are 18 Ranges total
        
 'if the Target cell is still active, store pointer to target in a name
 '  this allows validation to be performed when a different cell is selected
 If Target(1).Address = ActiveCell.Address Then
   Me.Names.Add "ptrPreviousCell", RefersTo:=Target(1)  ', Visible:=False
   GoTo ExitProc
 Else
   On Error Resume Next
   Me.Names("ptrPreviousCell").Delete
   On Error GoTo 0
   Call ValidateEntry(rInput:=Target)
 End If
     
ExitProc:

End Sub

Paste into a Standard Code Module...
Code:
Sub ValidateEntry(ByVal rInput As Range)
   'if cell is blank then repopulate with sample
   If rInput(1).Formula = vbNullString Then
      Call populateSampleInputs(boxToPopulate:=rInput)
      
   'else if the cell contains a value that is not a number,
   '   display error and repopulate cell with sample
   ElseIf IsNumeric(rInput(1).Value) = False Then
      MsgBox ("Entry must be a number.")
      Call populateSampleInputs(boxToPopulate:=rInput)
   
   'otherwise user has entered a valid number, so un-italicize cell
   Else
      rInput.Font.Italic = False
   End If
   
End Sub

Sub populateSampleInputs(boxToPopulate As Range)
 Application.EnableEvents = False
   
 With Sheets("Input")
    Select Case boxToPopulate.Address
      Case .Range("Trend").Address
        boxToPopulate.Value = 1.04
        boxToPopulate.Font.Italic = True

      Case .Range("Sales").Address
        boxToPopulate.Value = 987000
        boxToPopulate.Font.Italic = True
      
      Case Else
         '-if no matches
    End Select
 End With
 
 Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top