| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA .activate
Hi,
I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA .activate
Put the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d = Range("D:D") If Intersect(t, d) Is Nothing Then Exit Sub If t.Value = "" Then Exit Sub Sheets(1).Activate End Sub -- Gary''s Student - gsnu200835 "terilad" wrote: Hi, I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA .activate
Many thanks Gary''s Student, works a treat, I am a bit confused as I notice
the Index of Stock sheet is actually named as Sheet4 in VBA but this code works with Sheets (1).activate How is this??? Also I have another 2 Private Sub Worksheet commands on these worksheets, do these have to be integrated together or are they ok separate as 3 different commands? Many thanks Terilad "Gary''s Student" wrote: Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d = Range("D:D") If Intersect(t, d) Is Nothing Then Exit Sub If t.Value = "" Then Exit Sub Sheets(1).Activate End Sub -- Gary''s Student - gsnu200835 "terilad" wrote: Hi, I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA .activate
Good question.
Sheets(1).Activate means to activate the first (left-most) sheet regardless of its name Sheets("Index of Stock").Activate means to activate the sheet with the given tabname regardless of its position -- Gary''s Student - gsnu200835 "terilad" wrote: Many thanks Gary''s Student, works a treat, I am a bit confused as I notice the Index of Stock sheet is actually named as Sheet4 in VBA but this code works with Sheets (1).activate How is this??? Also I have another 2 Private Sub Worksheet commands on these worksheets, do these have to be integrated together or are they ok separate as 3 different commands? Many thanks Terilad "Gary''s Student" wrote: Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d = Range("D:D") If Intersect(t, d) Is Nothing Then Exit Sub If t.Value = "" Then Exit Sub Sheets(1).Activate End Sub -- Gary''s Student - gsnu200835 "terilad" wrote: Hi, I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA .activate
Thanks for explaining, much appreciated, did you look at my other question
below about another 2 Private Sub Worksheet commands if they have to be migrated to one command or if they are ok to remain separate. Regards Terilad "Gary''s Student" wrote: Good question. Sheets(1).Activate means to activate the first (left-most) sheet regardless of its name Sheets("Index of Stock").Activate means to activate the sheet with the given tabname regardless of its position -- Gary''s Student - gsnu200835 "terilad" wrote: Many thanks Gary''s Student, works a treat, I am a bit confused as I notice the Index of Stock sheet is actually named as Sheet4 in VBA but this code works with Sheets (1).activate How is this??? Also I have another 2 Private Sub Worksheet commands on these worksheets, do these have to be integrated together or are they ok separate as 3 different commands? Many thanks Terilad "Gary''s Student" wrote: Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d = Range("D:D") If Intersect(t, d) Is Nothing Then Exit Sub If t.Value = "" Then Exit Sub Sheets(1).Activate End Sub -- Gary''s Student - gsnu200835 "terilad" wrote: Hi, I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA .activate
You can have several worksheet event macros, but only one of each type.
-- Gary''s Student - gsnu200835 "terilad" wrote: Thanks for explaining, much appreciated, did you look at my other question below about another 2 Private Sub Worksheet commands if they have to be migrated to one command or if they are ok to remain separate. Regards Terilad "Gary''s Student" wrote: Good question. Sheets(1).Activate means to activate the first (left-most) sheet regardless of its name Sheets("Index of Stock").Activate means to activate the sheet with the given tabname regardless of its position -- Gary''s Student - gsnu200835 "terilad" wrote: Many thanks Gary''s Student, works a treat, I am a bit confused as I notice the Index of Stock sheet is actually named as Sheet4 in VBA but this code works with Sheets (1).activate How is this??? Also I have another 2 Private Sub Worksheet commands on these worksheets, do these have to be integrated together or are they ok separate as 3 different commands? Many thanks Terilad "Gary''s Student" wrote: Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d = Range("D:D") If Intersect(t, d) Is Nothing Then Exit Sub If t.Value = "" Then Exit Sub Sheets(1).Activate End Sub -- Gary''s Student - gsnu200835 "terilad" wrote: Hi, I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA .activate
Many thanks for your help.
Terilad "Gary''s Student" wrote: You can have several worksheet event macros, but only one of each type. -- Gary''s Student - gsnu200835 "terilad" wrote: Thanks for explaining, much appreciated, did you look at my other question below about another 2 Private Sub Worksheet commands if they have to be migrated to one command or if they are ok to remain separate. Regards Terilad "Gary''s Student" wrote: Good question. Sheets(1).Activate means to activate the first (left-most) sheet regardless of its name Sheets("Index of Stock").Activate means to activate the sheet with the given tabname regardless of its position -- Gary''s Student - gsnu200835 "terilad" wrote: Many thanks Gary''s Student, works a treat, I am a bit confused as I notice the Index of Stock sheet is actually named as Sheet4 in VBA but this code works with Sheets (1).activate How is this??? Also I have another 2 Private Sub Worksheet commands on these worksheets, do these have to be integrated together or are they ok separate as 3 different commands? Many thanks Terilad "Gary''s Student" wrote: Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d = Range("D:D") If Intersect(t, d) Is Nothing Then Exit Sub If t.Value = "" Then Exit Sub Sheets(1).Activate End Sub -- Gary''s Student - gsnu200835 "terilad" wrote: Hi, I am needing some help with a code, I have 150 sheets in a workbook in relation to stock items, I have 4 columns of date I need to enter into these sheets. e.g. Date Stock +/- Stock Total Initials These are the 4 headings of the columns, what I am looking to do is that I fill in Date and Stock +/- after that I want to put in initials of person removing stock e.g KJ then after I have input the initials and hit enter I want to activate so that after initials are input it will automatically return to sheet1 "Index of Stock" Can anyone help me on this code? Many thanks Terilad |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reliably activate PowerPoint, do something, Activate Excel | Excel Programming | |||
| activate VBA | Excel Programming | |||
| activate VBA | Excel Programming | |||
| Windows().Activate vs Workbooks().Activate | Excel Programming | |||
| Workbook.Activate / Window.Activate problem | Excel Programming | |||
Linear Mode
