How do I assign a code to a button

I have had the following written for me

Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Target
If Cell.Column = 4 and Cell.row >= 17 and Cell.row <= 20 Then
If Cell <>"" Then
Cell.Offset(0, 3) = Date
Else
Cell.Offset(0, 3) = ""
End If
End If
Next Cell
End Sub

I wish to assign that code to a button on sheet 1.

I normally just right click the buitton and assign the macro, but on this occasion I can't. I think it's to do with it being a private sub.

All help much apprecitaed.

Thanx

Steve

That is a worksheet change event procedure which is automatically activated by the event of a change being made to the worksheet to which it is attached. You would need to put it in a standard code module and change to something like:

Please Login or Register  to view this content.

Then you could assign to a button as normal.

Hi Stephen

Thanx for your quick reply, I have made the suggested changes and the macro still is not showing, however when I take the private bit out it does show but doesnt work.


Steve

Sorry, yes remove the Private, and declare cell. Should work:

Please Login or Register  to view this content.

No mate doesnt work, as when I activate the macro it deletes the info in another cell


What I would like is a macro to show the date that data was entered into G17:G20 and display the date that data was imput into cells D17:D20, but if any of the cells in G17:G20 are blank then display nothing.

thanx


Steve

You're currently checking column D and putting the date in G so amend as below. See attachment.

Please Login or Register  to view this content.

Hi Stephen (btw that's the only way to spell it)

please find attached my workbook 'Links Golf'


what I would like to happen if possible is that if any data is entered into any of the cells on Sheet 1 G17:G20 for the date to be displayed in corresponding cells D17:D20 - however if any of the cells in G17:G20 are blank to leave the corresponding cell blank.

workbook attached for perusal

many thanx Steve

See modified attachment.

Perfect Steve many thanx


Steve

Sorry Steve

just a quickie - lets say i eneter data in G17 today and press the button, it will display todays date in D17, however if I enter data in G18 tomorrow and then press the button will D17's info change to tomorrows date as well, if it does is there a way to make that date static until I press the save button which clears those contents onto another page


thank you as always


Steve

Hi once again Steve


have just tried it out and changed the date on the Pc and cell D17 does change to whatever date I pressed the button.

Appreciate all your help but was wanting to keep D17:D20 locked with the date that the data was first entered. So I could end up with 4 dates in D17:D20


thank you

Steve

Steve,

See the re-modified attachment. Have gone back to the original approach using the Worksheet Change Event. When you enter something in G17:G20 you should get the date in D17:D20 and it shouldn't update whenever you enter new values.

I also did some housekeeping and removed a few empty modules!

Steve/Stephen

Steve - this should do it. For a worksheet event, right-click on the worksheet tab and select view code. The VB editor opens up and then you can just paste stuff in as normal.

Thanx for all your help during the day


Steve