VBA Help
 

Hi,

I have the following code so that when I select a cell in cells A7:A500 it
automatically enter the date, this takes place on 150 sheets in the workbook
for stock control, can I ammend this code or add to it so that when the cell
has been filled with the date it will automatically revert back to sheet 1
which is called Index of Stock.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then Target.Value = Date
End Sub

Many thanks

VBA Help
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then Target.Value = Date
Sheet1.Activate
End Sub

"terilad" wrote:

Hi,

I have the following code so that when I select a cell in cells A7:A500 it
automatically enter the date, this takes place on 150 sheets in the workbook
for stock control, can I ammend this code or add to it so that when the cell
has been filled with the date it will automatically revert back to sheet 1
which is called Index of Stock.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then Target.Value = Date
End Sub

Many thanks

VBA Help
 

Hi Mike thanks for your rapid response, it works well but what if I want to
change it slightly so that in cells D7:D500 after I input a set of initials
such as DR or whatever I have set up in data validation only after the
initials have been entered will it return to sheet1.

Any ideas?

Regards

"Mike" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then Target.Value = Date
Sheet1.Activate
End Sub

"terilad" wrote:

Hi,

I have the following code so that when I select a cell in cells A7:A500 it
automatically enter the date, this takes place on 150 sheets in the workbook
for stock control, can I ammend this code or add to it so that when the cell
has been filled with the date it will automatically revert back to sheet 1
which is called Index of Stock.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then Target.Value = Date
End Sub

Many thanks

VBA Help
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then
Target.Value = Date
ThisWorkbook.Sheets("Sheet1").Select
End If
End Sub


Based on your discription you must have a copy af the macro on every
sheet

You could have just 1 macro in the Workbook module

This code does exactly the same for everysheet except sheet1 and is the
only macro required - Place macro in ThisWorkbook module
If required it can be easily modified so that it does not run on
mulitple sheets


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Not LCase(Sh.Name) = "sheet1" Then
If Not Intersect(Target, Range("A7:A500")) Is Nothing Then
Target.Value = Date
ThisWorkbook.Sheets("Sheet1").Select
End If
End If
End Sub


--
mudraker

If my reply has assisted or failed to assist you I welcome your
Feedback.

www.thecodecage.com
------------------------------------------------------------------------
mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69321