circular references

S

securityman

Hello, All,
I have a spreadsheet with invoices being taken from a standar
amout of money. I have gotten it to do everything correct except on
thing.
In the column where I will be placing my invoice numbers.
All our invoice numbers start with 300. I want to enter only the las
3 or 4 digits of the invoice number in the cell and then when I ta
over to the next cell to put in the amount, the cell with the invoic
number will change to 300xxx.

Example: I enter into cell B2 --- 543
when I tab over to the next cell to put in the
amount, cell B2 changes to 300543.

I figure I will have to use VBA to accomplish this, so there wil
not be any circular references?

Can anyone help me with this?


Thank
 
M

mudraker

secrityman

You will need somehing like this code

Paste into the worksheetsheet module



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Column = 2 Then
If IsNumeric(Target.Value) Then
If Len(Target.Value) <> 6 Then
Target.Value = 300 & Format(Target.Value, "000")
End If
End If
End If
Application.EnableEvents = True
End Su
 
D

Dave Hawley

Hi

Right click on the sheet name tab, select "View Code" and paste in the
code below


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = 300 & Target
Target.NumberFormat = "General"
Application.EnableEvents = True
End If
End If
End Sub

It has been set to only work on the range A1:A100. Change this to suit.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
S

securityman

Thanks mudraker, your code did the trick.......

Dave, I keep getting an error at the "If Not Intersect" line of th
code. I looked up Intersect in HELP and I think it needs anothe
range.

Thanks, guys, It is working the way I need
 
Top