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

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

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

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

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

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

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