Replacing All Cleared Cells with Default Content

[FracinDean]

Hello Forum, I'm trying to use the Worksheet_Change event to replace all cleared cells with a default formula. This allows users to override the default, or restore the default when they need to:

If Not Intersect(Target, Range("Data")) Is Nothing And Target.Value = "" Then
Range("DefaultFormula").Copy
ActiveSheet.Paste
ActiveSheet.Application.CutCopyMode = False
End If

Here are the results with various different regions selected:</SPAN>

• Single cell selected and cleared -- works. </SPAN>
• Multiple cells selected and cleared -- doesn't work if the first area selected contains more than one cell. Otherwise, it works, no matter how many other areas are selected.</SPAN>

Example1: Selected range is (D2:E2, G2) in that order, it does not copy the formula into the cleared cells.
Example2: Selected range is (G2, D2:E2) in that order, the code does copy the formula into all the cleared cells as desired.

The "IF" condition is not TRUE when the initial area has more than one cell.

Any suggestions would be appreciated.</SPAN>

 

[FracinDean]

I got an idea from Peter_SSs when I read the thread Username entered in a cell when validation list is used tonight. I wrote the following code and solved my own problem, but if anyone else needs to know one way to git 'r done in the future, here it is:

Dim Changed As Range, c As Range
Set Changed = Intersect(Target, Range("ScheduleData"))
If Not Changed Is Nothing Then
Application.EnableEvents = False
For Each c In Changed
Range("Formula").Copy
c.Select
ActiveSheet.Paste
ActiveSheet.Application.CutCopyMode = False
Next c
Application.EnableEvents = True
End If

 

[phantom1975]

Nice job looking around for solutions! I can tell that you have learned to write code by recording macros and seeing what they do. That is a GREAT way to learn. I'd like to throw a shred of advice your direction for the sake of saving space in your coding. I would suggest changing c.Select to c.Paste and then get rid of the ActiveSheet.Paste and the ActiveSheet.Application.CutCopyMode = False lines. Essentially all you want to do is paste the "Formula" cell into the target cell. c.Select does just that -- it selects the cell in question. This is basically just like clicking on a cell. It doesn't really do anything. ActiveSheet.Paste is more or less like hitting CTL+V to execute a paste command. This will apply the paste to the cell that was selected. You know when you copy a cell and the outline of the cell "dances" once you copy it so that you have a visual means of identifying the cells you just copied and then the "dancing" stops once you have pasted what was copied? The ActiveSheet.Application.CutCopyMode = False line is what causes the "dancing" to stop. So you see, there really is no need to select the cell, send a paste command, and then turn off the "dancing" when you can add .Paste to your range object. Same thing goes with .PasteSpecial. Good luck and good job!

 

[FracinDean]

Thanks phantom1975, great tips! Did you notice that I forgot to test to make sure the cell was blank before I pasted the formula in? Easy enough to solve, but if someone is following, that would be the last thing. Here is the final code with phantoms last tips and the test for an empty cell:

Dim Changed As Range, c As Range
Set Changed = Intersect(Target, Range("ScheduleData"))
If Not Changed Is Nothing Then
Application.EnableEvents = False
For Each c In Changed
If c.Value = "" Then
Range("Formula").Copy
c.Paste
End If
Next c
Application.EnableEvents = True
End If

 

[phantom1975]

So here's some more to chew on!! You could also change the copy portion of your code to Range("Formula").Copy c

Basically hitting a space after .Copy allows you to identify the range that you want the value pasted to. You may also want to consider changing If c.Value = "" to If Len(c.Value)=0

The advantage is just that it is slightly easier to identify what you are trying to accomplish since you are trying to determine the length of the value of the range. It also might be slightly faster because c.value is evaluating a string value whereas the Len function is not. OK, it's late and I really shouldn't be in front of a keyboard!

 

[FracinDean]

Thanks phantom1975, it works:

'
' Restore default formula when schedule data cells are cleared
' Ask the user to enter a comment when they enter "MISC"
'
Dim Changed As Range, c As Range, myComment As String
Set Changed = Intersect(Target, Range("ScheduleData"))
If Not Changed Is Nothing Then
For Each c In Changed
If Len(c.Value) = 0 Then
Range("Formula").Copy c
ElseIf c.Value = "MISC" Then
myComment = Application.InputBox("Please enter a comment for MISC:", "Comment Needed", "Please Describe")
With c.AddComment
.Visible = False
.Text Environ("Username") & Chr(10) & myComment
End With
End If
Next c
End If

 

[phantom1975]

It's amazing how much fine tuning you can do with code. There are many times I fail to sleep at night because I am working out more efficient ways to do the same task in VBA. You're doing a great job so far!