Macro triggered by an event

[Mekinnik]

I have a worksheet that I am storing a list of manufacturers and there
addresses and phone info in, I have it set up this way.

A B C D E
F G
1 sequence ManName Address City State Zip
Phone
2 1 john doe 111 doe rd. nowhere AA 00000
(000)0000-0000
3 2 xxxxx xxxxxx xxxx xx
xxxxxx xxxxxxxxxxxxxx

Now I would like the macro to fire when I type the ManName into the cell and
create(autofill) cell A2 and on with the next sequencial number. In addition
to that I would like the same macro to fire sorting the ManName column
keeping all the info with the sequence number when the worksheet is closed.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mekinnik]

Mr. Phillips,
I cut and pasted your code as you said only when the macro fires it does
nothing and I cannot figure out why. I currently have 162 entries in my
worksheet and the macro does not create a sequence number for the in column A
at all. Now a couple of questions
1) the second line
Const WS_RANGE As String = "B:B" '<== change to suit
what does this line do and how do I call it?

2) In the following line is the word header supposed to be replaced with my
column header name?
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes

I understand most of the vb language however I am still learning and
teaching myself to use it. Maybe explain the code for me what it is suppose
to do line by line?

Sorry to be such a pain.

Thanks
Mekinnik

 

[Tom Ogilvy]

Put this in a general module and run it

Sub ABC()
Application.EnableEvents = True
End sub

Or close and reopen Excel.

Perhaps in playing around, you have disabled event.s

the
const WS_RANGE as String = "B:B'

declares WS_RANGE as a constant that holds the string value B:B. When Bob
does

Range(WS_RANGE) that is the same as Range("B:B") but if you have to change
it, you only have to change it at the top. So you don't need to call it.

header:=xlYes means that the data table has a header. Same as in the sort
dialog when you click the check box that it has a header or it doesn't. If
you say yes, then the top row is not included in the sort since it contains
column labels.

Key1:=Range("B1") says to sort the range using column B as the sort key.


since the code is going to sort the rows, it is unlikely that the last row
will have the hightest sequence number, so you probably should change

Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1

to

Me.Cells(.row,"A").Value = application.Max(me.columns(1)) + 1

If your data isn't in columns A to G, with headers in row 1, then perhaps
you can say where it is located