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]

Try using the following worksheet_change event. This will run whenever you manually change the value in B1.

Private Sub Worksheet_Change(ByVal target As Range)
If target.Address = "$B$1" Then
    Range("A1").Value = Date
End If
End Sub

To use this code, right click on the sheet tab of the worksheet you want this to run on, and click view code. Then copy/paste the above code into the window that pops up.

 

[khalil]

thanks for the reply

but i wanted when print text in range from cell B1:B100 then Cell A1:A1000 takes today's date, and fix.

it did not work with normal today() formulas.

thanks alot

 

[MrKowz]

thanks for the reply

but i wanted when print text in range from cell B1:B100 then Cell A1:A1000 takes today's date, and fix.

it did not work with normal today() formulas.

thanks alot

Ahh, in your original post you only indicated B1 and A1. Try this adjusted code that will work for the entire column B and A.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B:B") Is Nothing Then
    target.offset(0,-1).value = Date
End If
End Sub

 

[khalil]

is "B:B" covers any cell in column B
i don't see A:A column as a target for the date

cause it did nit work

thanks

 

[MrKowz]

is "B:B" covers any cell in column B
i don't see A:A column as a target for the date

cause it did nit work

thanks

I mismatched a parenthases:

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B:B")[B][COLOR=Red])[/COLOR][/B] Is Nothing Then
    target.Offset(0, -1).Value = Date
End If
End Sub

The target.Offset(0,-1).Value = Date indicates that whatever row in column B you change, it will put the current date in column A of that row. So if you enter anything in B256, it will put the date in A256; if you change B592, it will put the date in A592, etc....

Also, yes, it will affect all of column B. If you want to limit it to a specific range, please let me know and I will adjust.

 

[khalil]

I appreciated your help

you are a good Man

Thanks

 

[MrKowz]

I appreciated your help

you are a good Man

Thanks

Glad it works. Thank you for the feedback!

 

[khalil]

How Can I learn these codes

thanks

 

[MrKowz]

If you have no previous VBA experience, I would recommend taking a beginner class at a local college. After taking a beginner course, you should have a fair understanding of how a macro works and how to utilize the macro recorder to learn new code.

If you learn better on your own, there are many VBA books out there that have tutorials and explanations (MrExcel has some you should be able to find on his website).

Aside from that, learn from these forums. Don't be afraid to ask questions, and learn from the questions you do ask.