| 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
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell change
Thanks I'll give it a try
"SparkyUK" wrote: 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 |
|
#9
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 |
| 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 | |||
Linear Mode
