Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|