Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to display the number between two numbers closest to another value | Excel Worksheet Functions | |||
For Hector Miguel - Inserting page numbers in cell | Excel Discussion (Misc queries) | |||
Inserting page numbers in a cell | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
inserting page number in cell | Excel Programming |