Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event
Hi,
I'm trying to write some VBA so that I can autopopulate several cells at once, unfortunately I'm not very good at it and wondered if anyone could help? What I'm trying to do is if a group of cells has the name "CLOP" if one of that group is changed they all change and then if they're part of the group "MISC" and one of them changes, then all of the MISC group changes. This is what I have. Unfortunately if I change one of the CLOP group it changes all of the CLOP and all of the MISC and then just keeps on going! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "Required" Then Range("[Test1.xls]Sheet1!CLOP").Value = "Required" ElseIf Target.Value = "In_Progress" Then Range("[Test1.xls]Sheet1!CLOP").Value = "In_Progress" ElseIf Target.Value = "Complete" Then Range("[Test1.xls]Sheet1!CLOP").Value = "Complete" Else Range("[Test1.xls]Sheet1!CLOP").Value = "" Exit Sub End If If Target.Value = "Required" Then Range("[Test1.xls]Sheet1!MISC").Value = "Required" ElseIf Target.Value = "In_Progress" Then Range("[Test1.xls]Sheet1!MISC").Value = "In_Progress" ElseIf Target.Value = "Complete" Then Range("[Test1.xls]Sheet1!MISC").Value = "Complete" Else Range("[Test1.xls]Sheet1!MISC").Value = "" Exit Sub End If End Sub Any help would be massively appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range("CLOP")) Is Nothing Then Me.Range("CLOP").Value = Target.Value ElseIf Not Intersect(Target, Me.Range("MISC")) Is Nothing Then Me.Range("MISC").Value = Target.Value End If ws_exit: Application.EnableEvents = True On Error GoTo 0 End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MAT" wrote in message ups.com... Hi, I'm trying to write some VBA so that I can autopopulate several cells at once, unfortunately I'm not very good at it and wondered if anyone could help? What I'm trying to do is if a group of cells has the name "CLOP" if one of that group is changed they all change and then if they're part of the group "MISC" and one of them changes, then all of the MISC group changes. This is what I have. Unfortunately if I change one of the CLOP group it changes all of the CLOP and all of the MISC and then just keeps on going! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "Required" Then Range("[Test1.xls]Sheet1!CLOP").Value = "Required" ElseIf Target.Value = "In_Progress" Then Range("[Test1.xls]Sheet1!CLOP").Value = "In_Progress" ElseIf Target.Value = "Complete" Then Range("[Test1.xls]Sheet1!CLOP").Value = "Complete" Else Range("[Test1.xls]Sheet1!CLOP").Value = "" Exit Sub End If If Target.Value = "Required" Then Range("[Test1.xls]Sheet1!MISC").Value = "Required" ElseIf Target.Value = "In_Progress" Then Range("[Test1.xls]Sheet1!MISC").Value = "In_Progress" ElseIf Target.Value = "Complete" Then Range("[Test1.xls]Sheet1!MISC").Value = "Complete" Else Range("[Test1.xls]Sheet1!MISC").Value = "" Exit Sub End If End Sub Any help would be massively appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event
WOW! That was fast
Brilliant Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Change event and calculate event | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
change event/after update event?? | Excel Programming |