Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
excelent
 
Posts: n/a
Default Which cell was the activecell before a keypress

Hi fellows

I got 2 sheets excatly the same except the name
when i put a number in a cell and press enter, the number is copyed to the
other sheet too
but if i put in a number, and press Tab or left/right/up arrow the sub
dosent work
haw do i get to no what key was pressed? or what cell was active begore the
action?


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveCell.Offset(-1, 0)) Is Nothing Then Exit Sub
If Not IsNumeric(ActiveCell.Offset(-1, 0)) Then Exit Sub
Dim adr
adr = ActiveCell.Offset(-1, 0).Address
Sheets("Gr.1").Range(adr).Value = Sheets("Gr.1").Range(adr).Value +
Range(adr).Value
Range(adr).Value = ""
End Sub

any sugestion ?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Norman Jones
 
Posts: n/a
Default Which cell was the activecell before a keypress

Hi Excelent,

Try replacing your code with:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim adr As String

If Not IsNumeric(Target) Then Exit Sub

On Error GoTo XIT
Application.EnableEvents = False

adr = Target.Address
Sheets("Sheet3").Range(adr).Value = _
Sheets("Sheet3").Range(adr).Value _
+ Range(adr).Value
Range(adr).Value = ""

XIT:
Application.EnableEvents = True
End Sub
'<<=============


---
Regards,
Norman


"excelent" wrote in message
...
Hi fellows

I got 2 sheets excatly the same except the name
when i put a number in a cell and press enter, the number is copyed to the
other sheet too
but if i put in a number, and press Tab or left/right/up arrow the sub
dosent work
haw do i get to no what key was pressed? or what cell was active begore
the
action?


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveCell.Offset(-1, 0)) Is Nothing Then Exit Sub
If Not IsNumeric(ActiveCell.Offset(-1, 0)) Then Exit Sub
Dim adr
adr = ActiveCell.Offset(-1, 0).Address
Sheets("Gr.1").Range(adr).Value = Sheets("Gr.1").Range(adr).Value +
Range(adr).Value
Range(adr).Value = ""
End Sub

any sugestion ?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
excelent
 
Posts: n/a
Default Which cell was the activecell before a keypress

Hi Norman
Perfect - just what i neded :-)

tanks alot

sry my french Denmark u no :-)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compiling macro based on cell values simonsmith Excel Discussion (Misc queries) 1 May 16th 06 08:31 PM
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
resetting last cell jagdish.eashwar Excel Discussion (Misc queries) 11 March 31st 06 02:06 AM
copying cell names Al Excel Discussion (Misc queries) 12 August 11th 05 03:01 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM


All times are GMT +1. The time now is 07:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"