Can inserting numbers into a cell display another number elsewhere
 

Im looking for a way to insert a 5 digit code into an Excel worksheet cell
and have another code automatically appear into another cell. Is it possible
to associate the two numbers, therefore, having one cause the other to appear
in a predetermined second location (cell)?

Can inserting numbers into a cell display another number elsewhere
 

You can use the worksheet_change event code within the particular sheet
on which you enter the 5 digit code. Here's a simple example with A1
being the cell you enter the code into and cell A5 being the other cell
it automatically adds a code to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value < "" Then
Range("A5").Value = Range("A1").Value + 1
End If
End Sub

Regards,
Anita

Can inserting numbers into a cell display another number elsew
 

be careful - when using the change event to change a cell, the event gets
fired again!
The code below will count the first change and the status bar will show the
value
enter anything into A2 to reset...
Private Sub Worksheet_Change(ByVal Target As Range)
Static count As Long
If Range("A1").Value < "" Then
count = count + 1
Application.StatusBar = count
Range("A5").Value = Range("A1").Value + 1
End If
If Target.Address = Range("A2").Address Then
count = 0
Application.StatusBar = False
End If
End Sub

There are several ways to remedy this
1) check if the cell is the one you're targetting:
Private Sub Worksheet_Change(ByVal Target As Range)
Static count As Long
Select Case Target.Address
Case Range("A1").Address
If Range("A1").Value < "" Then
count = count + 1
Application.StatusBar = count
Range("A5").Value = Range("A1").Value + 1
End If
Case Range("A2").Address
count = 0
Application.StatusBar = False
End Select
End Sub

2) another is to temporarily DISABLE the event from firing...
Private Sub Worksheet_Change(ByVal Target As Range)
Static count As Long
If Target.Address = Range("A1").Address Then
If Range("A1").Value < "" Then
count = count + 1
Application.StatusBar = count
Application.EnableEvents = False
Range("A5").Value = Range("A1").Value + 1
Application.EnableEvents = True
End If
Else
count = 0
Application.StatusBar = False
End If
End Sub

HTH
Patrick



"Anita" wrote:

You can use the worksheet_change event code within the particular sheet
on which you enter the 5 digit code. Here's a simple example with A1
being the cell you enter the code into and cell A5 being the other cell
it automatically adds a code to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value < "" Then
Range("A5").Value = Range("A1").Value + 1
End If
End Sub

Regards,
Anita