circular references

  • Thread starter Thread starter securityman
  • Start date Start date
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
 
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
 
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 ***
 
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
 
Back
Top