Date to be fixed

[khalil]

Hi all

please help

excel 2007

i want when fill cell B1 with any text , then cell A1= today , i want to fix this so when tomorrow come the date will not change

thanks in advance

 

[MrKowz]

Ok, remove all of the Worksheet_Change macros you used, and place the following in the "ThisWorkbook" module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "A" Or Sh.Name = "B" Or Sh.Name = "C" Then
    If Not Intersect(Target, Sh.Range("B:B")) Is Nothing Then
        If Target.Value = "" Then
            Target.Offset(0, -1).ClearContents
        Else
            Target.Offset(0, -1).Value = Date
        End If
    End If
End If
End Sub

 

[khalil]

you mean by workbook module i s another complete workbook nothing to do with project workbook,
please can you ex-plane little bit more

thanks my friend

 

[MrKowz]

Ok, lets say you have a workbook that has the following sheets (names) in it:

• Summary
• A
• B
• C
• Calculations
• Data
• Log

In the VBA Editor (when you press alt+F11), in the Project - VBAProject window, you should see a list of the worksheets and workbook under the "Microsoft Excel Objects" group that would look something like this:

• Sheet1 (Summary)
• Sheet2 (A)
• Sheet3 (B)
• Sheet4 (C)
• Sheet5 (Calculations)
• Sheet6 (Data)
• Sheet7 (Log)
• ThisWorkbook

Up until this point, you were storing the code at what is known as the "worksheet level" or in a "worksheet module". This means that the code has been going into those objects that are worksheets (Sheet1, Sheet2, Sheet3, etc). However, the code I have recently given you, the Workbook_SheetChange code, is stored in the "ThisWorkbook" module. By storing the updated code in the "ThisWorkbook" module, you can remove all of the other Worksheet_Change codes you previously put in.

If you are copying/pasting things from one workbook to another, and you want to maintain this functionality in the new workbook, you must copy/paste over the VBA code as well. The benefit to using the Workbook_SheetChange code instead of using a bunch of Worksheet_Change codes is that, for your needs, you want the same exact code to run, just on specific worksheets.

There is absolutely no way around this (as far as I am aware) in order to maintain your original requirements.

 

[khalil]

it was a very good tip
thanks a lot my friend

talk to you later

Khalil

 

[khalil]

Ok, lets say you have a workbook that has the following sheets (names) in it:

• Summary
• A
• B
• C
• Calculations
• Data
• Log

In the VBA Editor (when you press alt+F11), in the Project - VBAProject window, you should see a list of the worksheets and workbook under the "Microsoft Excel Objects" group that would look something like this:

• Sheet1 (Summary)
• Sheet2 (A)
• Sheet3 (B)
• Sheet4 (C)
• Sheet5 (Calculations)
• Sheet6 (Data)
• Sheet7 (Log)
• ThisWorkbook

Up until this point, you were storing the code at what is known as the "worksheet level" or in a "worksheet module". This means that the code has been going into those objects that are worksheets (Sheet1, Sheet2, Sheet3, etc). However, the code I have recently given you, the Workbook_SheetChange code, is stored in the "ThisWorkbook" module. By storing the updated code in the "ThisWorkbook" module, you can remove all of the other Worksheet_Change codes you previously put in.

If you are copying/pasting things from one workbook to another, and you want to maintain this functionality in the new workbook, you must copy/paste over the VBA code as well. The benefit to using the Workbook_SheetChange code instead of using a bunch of Worksheet_Change codes is that, for your needs, you want the same exact code to run, just on specific worksheets.

There is absolutely no way around this (as far as I am aware) in order to maintain your original requirements.

it worked
thanks

 

[khalil]

Hi MrKowz

how can i add these tow codes, to the one you sent me yesterday to paste in "this workbook"


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "E1" Then
If Target.Value = "" Then
Application.EnableEvents = False
Target.Value = "?"
Application.EnableEvents = True
End If
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldvalue As Long
Dim rng As Range
Dim lr As Long

oldvalue = Range("c1")
Set rng = Target.Parent.Range("c1")

If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub

With Sheets("Storevaluebydate")
lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & lr) = oldvalue
.Range("B" & lr) = Date
End With

End Sub






thanks