 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006Button on sheet: ? VB code to add date in active cell
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 Sub NOWDATE() ActiveCell.Value = Format(Date, "dd-mmm-yy") End Sub Gord Dibben MS Excel MVP >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 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 > Sub NOWDATE() > ActiveCell.Value = Format(Date, "dd-mmm-yy") [quoted text clipped - 8 lines] > >Bart > >Excel 2003 Gord's code puts it in the activecell, so you already have a range of cells.  Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > 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 [quoted text clipped - 14 lines] >> >Bart >> >Excel 2003 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 >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 [quoted text clipped - 14 lines] >> >Bart >> >Excel 2003 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 > Bart > [quoted text clipped - 32 lines] > >> >Bart > >> >Excel 2003 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, > [quoted text clipped - 54 lines] >> >> >Bart >> >> >Excel 2003 Gord Dibben MS Excel MVP 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 > Maybe this one? > [quoted text clipped - 66 lines] > >> >> >Bart > >> >> >Excel 2003Gord Dibben MS Excel MVP 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 >It works, thank you. > [quoted text clipped - 77 lines] >> >> >> >Bart >> >> >> >Excel 2003Gord Dibben MS Excel MVP
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.