| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
repeating code in visual basic
I have a code that i'm using for one column but i want to use the same code
on multiple columns, how do i change the code so it repeats for three more columns, currently just 21, would like it to be for 22, 23 & 24 too. I tried pasting it 4 times but i kept on getting different error messages. Can anyone help?? Code I'm using: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 21 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
repeating code in visual basic
Maybe
If Target.Column = 21 And Target.Column <= 24 Then Mike "tobypitblado" wrote: I have a code that i'm using for one column but i want to use the same code on multiple columns, how do i change the code so it repeats for three more columns, currently just 21, would like it to be for 22, 23 & 24 too. I tried pasting it 4 times but i kept on getting different error messages. Can anyone help?? Code I'm using: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 21 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
repeating code in visual basic
Try this:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim iSect Set iSect = Intersect(Target, Range("U:X")) If Not iSect Is Nothing Then If Target.Count 1 Then Exit Sub On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then Exit Sub If Not Intersect(Target, rngDV) Is Nothing Then Application.EnableEvents = False newVal = Target.Value 'Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal < "" And newVal < "" Then Target.Value = oldVal & ", " & newVal End If Application.EnableEvents = True End If End If exitHandler: End Sub Regards, Per "tobypitblado" skrev i meddelelsen ... I have a code that i'm using for one column but i want to use the same code on multiple columns, how do i change the code so it repeats for three more columns, currently just 21, would like it to be for 22, 23 & 24 too. I tried pasting it 4 times but i kept on getting different error messages. Can anyone help?? Code I'm using: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 21 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
repeating code in visual basic
Yes, it works. Thank you very much.
"Mike H" wrote: Maybe If Target.Column = 21 And Target.Column <= 24 Then Mike "tobypitblado" wrote: I have a code that i'm using for one column but i want to use the same code on multiple columns, how do i change the code so it repeats for three more columns, currently just 21, would like it to be for 22, 23 & 24 too. I tried pasting it 4 times but i kept on getting different error messages. Can anyone help?? Code I'm using: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 21 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
repeating code in visual basic
Thanks for the reply, used Mike's answer as it required changing less of the
code. "Per Jessen" wrote: Try this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim iSect Set iSect = Intersect(Target, Range("U:X")) If Not iSect Is Nothing Then If Target.Count 1 Then Exit Sub On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then Exit Sub If Not Intersect(Target, rngDV) Is Nothing Then Application.EnableEvents = False newVal = Target.Value 'Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal < "" And newVal < "" Then Target.Value = oldVal & ", " & newVal End If Application.EnableEvents = True End If End If exitHandler: End Sub Regards, Per "tobypitblado" skrev i meddelelsen ... I have a code that i'm using for one column but i want to use the same code on multiple columns, how do i change the code so it repeats for three more columns, currently just 21, would like it to be for 22, 23 & 24 too. I tried pasting it 4 times but i kept on getting different error messages. Can anyone help?? Code I'm using: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 21 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visual Basic code | Excel Discussion (Misc queries) | |||
| I need a visual basic code....please | Excel Discussion (Misc queries) | |||
| I don't want to run visual basic editer code after saving... | Excel Discussion (Misc queries) | |||
| How do I protect Visual Basic for Application Code | New Users to Excel | |||
| Visual Basic code available for all open workbooks | Excel Worksheet Functions | |||
Linear Mode
