PDA

View Full Version : [SOLVED] Convert from Worksheet_Change to manual



Dave T
10-21-2013, 06:08 AM
Hello All,
I have found a Worksheet_Change macro that I would like to convert to a 'standard' macro that I can run using a button on the worksheet.
Can someone please help me with what I need to do to change it.
I have temporarily commented out some of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'http://answers.microsoft.com/en-us/office/forum/office_2007-excel/insert-formula-for-row-using-worksheet-change/aa1d08d9-9981-4f96-ad57-b1bd1d72e7bb?msgId=1660522c-db3b-4a74-8fd0-fe4a25676dbd
Dim r As Variant, s As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(1)) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
With Target.Offset(, 1)
.Formula = "=VLookup(" & Target.Address & ", Data, 5, False)"
.Value = .Value
End With
' With Target.Offset(, 2) 'Column C
' .Formula = "=VLookup(" & Target.Address & ", Data, 6, False)"
' .Value = .Value
' End With
On Error GoTo 0
'get rid of NA
If WorksheetFunction.IsNA(Target.Offset(, 1)) Then
Target.Offset(, 1).ClearContents
End If
' If WorksheetFunction.IsNA(Target.Offset(, 2)) Then
' Target.Offset(, 2).ClearContents
' End If
End If
Application.EnableEvents = True
End Sub

Any help would be appreciated.

Regards,
Dave T

Paul_Hossler
10-21-2013, 07:40 AM
If you want to select a single cell and run the macro, this untested sub might work



Sub UsedToBe_Worksheet_Change()
Dim r As Range

If Not TypeOf Selection Is Range Then Exit Sub

If Selection.Cells.Count > 1 Then Exit Sub

If Intersect(Selection, Columns(1)) Is Nothing Then Exit Sub

Set r = Selection

Application.EnableEvents = False
On Error Resume Next

With r.Offset(, 1)
.Formula = "=VLookup(" & r.Address & ", Data, 5, False)"
.Value = .Value
End With

With r.Offset(, 2) 'Column C
.Formula = "=VLookup(" & r.Address & ", Data, 6, False)"
.Value = .Value
End With
On Error GoTo 0

'get rid of NA
If WorksheetFunction.IsNA(r.Offset(, 1)) Then r.Offset(, 1).ClearContents
If WorksheetFunction.IsNA(r.Offset(, 2)) Then r.Offset(, 2).ClearContents
Application.EnableEvents = True
End Sub


There are other ways, but this one seems to be truest to your code



Paul

Kenneth Hobs
10-21-2013, 08:17 AM
As Paul did, I would use the Selection range as the Target range. Notice that I just changed the first 3 lines of code.

When you write worksheet or workbook event code, you can send the input parameter(s) to a Sub if you ever need to reuse the code. Then just call that routine in your events as needed. Of course if you write event code to iterate all cells in a Target range, that event will fire for each cell in the target intersection. Then just cut and paste back to get data up to date.

Sub Module_Change()
'http://answers.microsoft.com/en-us/office/forum/office_2007-excel/insert-formula-for-row-using-worksheet-change/aa1d08d9-9981-4f96-ad57-b1bd1d72e7bb?msgId=1660522c-db3b-4a74-8fd0-fe4a25676dbd
Dim r As Variant, s As Variant, Target as Range
Set Target = Selection
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(1)) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
With Target.Offset(, 1)
.Formula = "=VLookup(" & Target.Address & ", Data, 5, False)"
.Value = .Value
End With
' With Target.Offset(, 2) 'Column C
' .Formula = "=VLookup(" & Target.Address & ", Data, 6, False)"
' .Value = .Value
' End With
On Error Goto 0
'get rid of NA
If WorksheetFunction.IsNA(Target.Offset(, 1)) Then
Target.Offset(, 1).ClearContents
End If
' If WorksheetFunction.IsNA(Target.Offset(, 2)) Then
' Target.Offset(, 2).ClearContents
' End If
End If
Application.EnableEvents = True
End Sub

Paul_Hossler
10-21-2013, 09:52 AM
1. Modularization is always good

2. Since any type of object on the worksheet can be selected, e.g. a textbox or other shape, I like to test the selected object's type to avoid Type Mismatch errors before proceeding under the assumption that it's a range



If Not TypeOf Selection Is Range Then Exit Sub


Paul

Dave T
10-21-2013, 05:08 PM
Hello Paul and Kenneth,

I really do appreciate your replies and also the helpful comments.
The simple additions by Kenneth to the original coding along with his comments will help me in the future.

Regards,
Dave T