Button on sheet: ? VB code to add date in active cell

[AA Arens]

I placed a button on the sheet and would like to know the VB code to
add the present date in the active cell. I did a group search, but not
not find anything.

Bart
Excel 2003

 

[Gord Dibben]

Sub NOWDATE()
ActiveCell.Value = Format(Date, "dd-mmm-yy")
End Sub


Gord Dibben MS Excel MVP

 

[AA Arens]

It works, however, I would like to add a range of cells where the
addition is allowed. I am even eble to add a date in a validation
restricted cell.

Bart

 

[Bob Phillips]

Gord's code puts it in the activecell, so you already have a range of cells.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Gord Dibben]

Bart

Not sure what you're asking for.

Do you want to add the date to multiple cells at a time?

Sub NOWDATE()
With Selection
.Value = Format(Date, "dd-mmm-yy")
End With
End Sub

Prevent adding the date to some cells?

That could be done through Tools>Protection>Protect Sheet


Gord

 

[AA Arens]

Hi Gord,

I do have a part which is locket and unlocket and I protect the whole
sheet so only a selection of cells are accessable, those who are
unlocked.


I do have the columns (1-4):

Number Date Model Serial#

1 15-05-06 ModelB 5467
2 etc.
3
4
5

All cells below the columns 2,3 and 4 are accessable by the user, but I
want to avoid that a date can be filled in when the active cell is NOT
under the Date column. How to do that?

Bart

 

[Gord Dibben]

Maybe this one?

Sub NOWDATE()
If ActiveCell.Column = 2 Then
ActiveCell.Value = Format(Date, "dd-mmm-yy")
Else: MsgBox "Dates must be entered in Column B"
Exit Sub
End If
End Sub


Gord

Hi Gord,

I do have a part which is locket and unlocket and I protect the whole
sheet so only a selection of cells are accessable, those who are
unlocked.


I do have the columns (1-4):

Number Date Model Serial#

1 15-05-06 ModelB 5467
2 etc.
3
4
5

All cells below the columns 2,3 and 4 are accessable by the user, but I
want to avoid that a date can be filled in when the active cell is NOT
under the Date column. How to do that?

Bart

Gord Dibben MS Excel MVP

 

[AA Arens]

It works, thank you.

One Q:

On another location the date positioning area is not a column, but a
range Z7 till AC7 instead of Column 2, how to perform?

I tried with the Help section with RANGE , ARRAY but w/o success.


Bart

 

[Gord Dibben]

Bart

Sub NOWDATE33()
If Not Intersect(ActiveCell, Range("Z7:AC7")) Is Nothing Then
ActiveCell.Value = Format(Date, "dd-mmm-yy")
Else: MsgBox "Dates must be entered in Range(Z7:AC7)"
Exit Sub
End If
End Sub


Gord