WorkSheet_Change code from a CommandButton

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this bit of Code (thanks Dave Peterson) that normally resides in my
worksheet

Private Sub CommandButton1_Click()
Range("b6:b150").Select
Selection.Replace what:="??", _
Replacement:=Cells("5", "b").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub
myRow = Target.Row

If Application.Intersect(Range("b5:B150"), Target) Is Nothing Then
Exit Sub
End If

Set myRng = Me.Range(Cells(myRow, "D"), Cells(myRow, "y"))

'stop the change from firing this event
Application.EnableEvents = False
myRng.Replace what:="L??O???", _
Replacement:=Me.Cells(Target.Row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Target.Select
Application.EnableEvents = True

End Sub

What I wouls like to do is have a CommandButton on a Userform do the same
thing.

I have tried copying various bit of it and all of it into the userform
module but with limited sucess. The second part of the code doesn't work as
it's no longer part of the worksheet.

Does anyone have any ideas

Thanks in advance

Mark
 
Sorry for the delay, I stopped get reply notification for this post and only
came accross you post today.

Basically the Command button code lives in the Userform Module and the
Worksheet_change sub stays in the worksheet object
 
thanks!
susan

Sorry for the delay, I stopped get reply notification for this post and only
came accross you post today.

Basically the Command button code lives in the Userform Module and the
Worksheet_change sub stays in the worksheet object





- Show quoted text -
Click to expand...
 
Back
Top