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. |
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. |
Change event
WOW! That was fast
Brilliant Thank You |
All times are GMT +1. The time now is 09:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com