VBA .activate

[terilad]

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

 

[Gary''s Student]

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")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
End Sub

 

[terilad]

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]

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

 

[terilad]

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]

You can have several worksheet event macros, but only one of each type.

 

[terilad]

Many thanks for your help.

Terilad

You can have several worksheet event macros, but only one of each type.