Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by an event
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by an event
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by an event
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 "Bob Phillips" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by an event
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 -- Regards, Tom Ogilvy "Mekinnik" wrote: 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 "Bob Phillips" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox Keydown event not triggered. | Excel Programming | |||
Event triggered by paste | Excel Programming | |||
Change event triggered by a named range | Excel Programming | |||
Macro triggered by an event | Excel Discussion (Misc queries) | |||
BeforeSave sub won't save another workbook when triggered by another event sub | Excel Programming |