Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change code
Hi Todd
try Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Counter As Long Dim Location Counter = Application.WorksheetFunction.CountA(me.Range ("K:K")) On Error Resume Next Set myRange = Intersect(Range("K2:K" & Counter), Target) If Not myRange Is Nothing Then target.Offset(0, 15).Select SendKeys "%{down}" End If End Sub -----Original Message----- hey guys Below is a code that is supposed to sendkeys to a certain cell if a change is made in the specified range. However, it does not work. Can anyone tell me whats wrong? When I change a cell in the specified cells, nothing happens. If I change the event to the worksheet_SelectionChange, it works but it never gives me the chance to enter anything into the cell I want to. Can anyone tell me whats wrong? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Counter As Long Dim Location Counter = Application.WorksheetFunction.CountA(Worksheets (1).Range("K:K")) Location = ActiveCell.Address On Error Resume Next Set myRange = Intersect(Range("K2:K" & Counter), Target) If Not myRange Is Nothing Then Range(Location).Offset(0, 15).Select SendKeys "%{down}" End If End Sub Thanks Todd Huttenstine . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change code
Ah yes, thank you.
Todd -----Original Message----- Hi Todd try Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Counter As Long Dim Location Counter = Application.WorksheetFunction.CountA(me.Range ("K:K")) On Error Resume Next Set myRange = Intersect(Range("K2:K" & Counter), Target) If Not myRange Is Nothing Then target.Offset(0, 15).Select SendKeys "%{down}" End If End Sub -----Original Message----- hey guys Below is a code that is supposed to sendkeys to a certain cell if a change is made in the specified range. However, it does not work. Can anyone tell me whats wrong? When I change a cell in the specified cells, nothing happens. If I change the event to the worksheet_SelectionChange, it works but it never gives me the chance to enter anything into the cell I want to. Can anyone tell me whats wrong? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Counter As Long Dim Location Counter = Application.WorksheetFunction.CountA(Worksheets (1).Range("K:K")) Location = ActiveCell.Address On Error Resume Next Set myRange = Intersect(Range("K2:K" & Counter), Target) If Not myRange Is Nothing Then Range(Location).Offset(0, 15).Select SendKeys "%{down}" End If End Sub Thanks Todd Huttenstine . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
change the name of a worksheet/update VBA code | Excel Discussion (Misc queries) | |||
Enable/Disable Worksheet Change Event code | Excel Programming | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming |