| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pressing a CommandButton on a Userform via Code | Excel Programming | |||
| CommandButton code help | Excel Programming | |||
| ? activate a commandbutton in code ? | Excel Programming | |||
| CommandButton Code | Excel Programming | |||
| Can You Change A CommandButton Code Using A Macro? | Excel Programming | |||
Hybrid Mode
