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:

Private Sub test
For Each Cell In Selection
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

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:

Sub test()

Dim cell As Range

For Each cell In Selection
    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

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.

Sub test()

Dim cell As Range

For Each cell In Selection
    If cell.Column = 7 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

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.