WorkSheet_Change code from a CommandButton
 

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

WorkSheet_Change code from a CommandButton
 

Figured it out now!

"Mark Dullingham" wrote:

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

WorkSheet_Change code from a CommandButton
 

can you post it (the solution) please?
thanks
susan

On Mar 11, 10:34 pm, Mark Dullingham
wrote:
Figured it out now!



"Mark Dullingham" wrote:
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- Hide quoted text -

- Show quoted text -

WorkSheet_Change code from a CommandButton
 

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

"Susan" wrote:

can you post it (the solution) please?
thanks
susan

On Mar 11, 10:34 pm, Mark Dullingham
wrote:
Figured it out now!



"Mark Dullingham" wrote:
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- Hide quoted text -

- Show quoted text -

WorkSheet_Change code from a CommandButton
 

thanks!
susan

On Mar 29, 9:14 pm, Mark Dullingham
wrote:
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



"Susan" wrote:
can you post it (the solution) please?
thanks
susan

On Mar 11, 10:34 pm, Mark Dullingham
wrote:
Figured it out now!

"Mark Dullingham" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -