Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
What would you like to happen?
The code executes when any cell on the sheet changes but your don't tell it to do anything. Here's a guess that does something and may get you going:- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False For Each c In Range("drw1inforng") If c.Value < "" Then MsgBox c.Value End If Next stoppit: Application.EnableEvents = True End Sub Mike "ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
I am looking for a way to run a macro when a cell in a range is changed. I
used the code that you suggested and each time a cell is changed, it goes thru all the cells in that range. i only need it to work on the cell that is change within a range of cells. How do I do this? Thanks "Mike H" wrote: What would you like to happen? The code executes when any cell on the sheet changes but your don't tell it to do anything. Here's a guess that does something and may get you going:- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False For Each c In Range("drw1inforng") If c.Value < "" Then MsgBox c.Value End If Next stoppit: Application.EnableEvents = True End Sub Mike "ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
Try the UNION method with Target and drw1inforng ranges
"ranswert" wrote: I am looking for a way to run a macro when a cell in a range is changed. I used the code that you suggested and each time a cell is changed, it goes thru all the cells in that range. i only need it to work on the cell that is change within a range of cells. How do I do this? Thanks "Mike H" wrote: What would you like to happen? The code executes when any cell on the sheet changes but your don't tell it to do anything. Here's a guess that does something and may get you going:- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False For Each c In Range("drw1inforng") If c.Value < "" Then MsgBox c.Value End If Next stoppit: Application.EnableEvents = True End Sub Mike "ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
Can you give me an example?
"SparkyUK" wrote: Try the UNION method with Target and drw1inforng ranges "ranswert" wrote: I am looking for a way to run a macro when a cell in a range is changed. I used the code that you suggested and each time a cell is changed, it goes thru all the cells in that range. i only need it to work on the cell that is change within a range of cells. How do I do this? Thanks "Mike H" wrote: What would you like to happen? The code executes when any cell on the sheet changes but your don't tell it to do anything. Here's a guess that does something and may get you going:- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False For Each c In Range("drw1inforng") If c.Value < "" Then MsgBox c.Value End If Next stoppit: Application.EnableEvents = True End Sub Mike "ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
Sorry should have used INTERSECT method:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngUnion As Range Set rngUnion = Application.Intersect(Target, Me.Range("drw1inforng")) If Not (rngUnion Is Nothing) Then On Error GoTo stoppit Application.EnableEvents = False If rngUnion.Value < "" Then MsgBox ("Not blank") End If stoppit: Application.EnableEvents = True End Sub ----- "ranswert" wrote: Can you give me an example? "SparkyUK" wrote: Try the UNION method with Target and drw1inforng ranges "ranswert" wrote: I am looking for a way to run a macro when a cell in a range is changed. I used the code that you suggested and each time a cell is changed, it goes thru all the cells in that range. i only need it to work on the cell that is change within a range of cells. How do I do this? Thanks "Mike H" wrote: What would you like to happen? The code executes when any cell on the sheet changes but your don't tell it to do anything. Here's a guess that does something and may get you going:- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False For Each c In Range("drw1inforng") If c.Value < "" Then MsgBox c.Value End If Next stoppit: Application.EnableEvents = True End Sub Mike "ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
will this doit
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("drw1inforng")) Is Nothing Then MsgBox Target.Value End If End Sub Mike "ranswert" wrote: I am looking for a way to run a macro when a cell in a range is changed. I used the code that you suggested and each time a cell is changed, it goes thru all the cells in that range. i only need it to work on the cell that is change within a range of cells. How do I do this? Thanks "Mike H" wrote: What would you like to happen? The code executes when any cell on the sheet changes but your don't tell it to do anything. Here's a guess that does something and may get you going:- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False For Each c In Range("drw1inforng") If c.Value < "" Then MsgBox c.Value End If Next stoppit: Application.EnableEvents = True End Sub Mike "ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell change
Works fine for me! Good luck.
"ranswert" wrote: I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("drw1inforng") If .Value < "" Then MsgBox ("") End If End With stoppit: Application.EnableEvents = True End Sub 'drw1inforng' is a named range on my sheet. When I make a change in this range, nothing happens. What am I doing wrong here? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to trigger one cell change to change another cell using VB in excel? Please help! :) | Excel Programming | |||
making copied cells change with change in original cell | Excel Worksheet Functions | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |