Button to place date

Microsoft Excel 2003 (full product)
November 21, 2009 at 06:54:22
Specs: Windows XP
I want to create a button in a cell that when I select a cell it will put the current date in that cell. Can anyone help?

See More: Button to place date

Report •

November 21, 2009 at 07:07:43
allso Im looking to have a balance subtract from cells bellow it when I enter the amounts with out haveing to add cells to the formula

Report •

November 21, 2009 at 08:07:09

Here is some code to put the date into the Active Cell.

Sub Enter_Date()
'Sub to enter today's date into the active cell and format the date in your 'Short Date' format
'(see Regional and Language Options - Customize - Date Tab - Short Date)
ActiveCell.Value = Format(Date, "Short Date")
End Sub

You can attach this code to a button on a worksheet or place it in a standard module to make it accessible from the Cell - right click menu, as described further on.

To create a button use the Controls Toolbar and select the button. In Design mode, right click button and select view code.
When entering code attached to a button, the opening Sub and closing End Sub are already in place.
Enter the code, but excluding the line starting 'Sub" and the 'End Sub' line.

To access the code from the Cell - right click menu, place the code in a standard module:
Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find VBAProject(Your.xls)
Right click on it and select Insert then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.

To create the extra entry in the Cell right click menu:
In the Your.xls workbook goto the VB window and select the ThisWorkbook object, under Your.xls. Double click it.

In the main VB code window enter the following:

Private Sub Workbook_Open()
' Add controls to cell right click context menu   
Dim objNewCtrlIT As CommandBarButton
On Error Resume Next
' Clear any existing copies of new menu item
Application.CommandBars("Cell").Controls("Insert Date").Delete
On Error goto ErrHnd
' Create new control
Set objNewCtrlIT = Application.CommandBars("Cell").Controls.Add
  With objNewCtrlIT
    .Caption = "Insert Date"
    .OnAction = "Module1.Enter_Date"
    .BeginGroup = False
  End With
Set objNewCtrlIT = Nothing
Exit Sub
' error handler
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Delete items from shortcut menu on close
On Error Resume Next
Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

The .OnAction line can be modified to point to the Module and subroutine name you have used for entering the date subroutine if different to the example used here.

You could use a standard module in Personal.xls and then the Insert Date would be available to all workbooks opened.


Report •

November 21, 2009 at 08:14:37

Regarding also Im looking to have a balance subtract from cells bellow it when I enter the amounts with out having to add cells to the formula
You will need to provide a lot more information. We have no idea what data you have, what cells the data is in or what data you will be entering.

An example of what you are trying to achieve with before and after data would be helpful.

If you are copying data from a spreadsheet, place it between the <pre> and </pre> tags (use the 'pre' icon above the message box to get these tags). This helps keep columns of data aligned and makes it easier to see what you are trying to achieve.


Report •

Related Solutions

November 22, 2009 at 06:53:20
First off thank you for your help.

Second I made the button for the date and I put the code you mentioned into it and when I highlight a cell to put the date in it and click the button I get a compile error: Expected End Sub and I cant figure out why. When it goes to the VB window it highlights Private Sub CommandButton1_Click() in yellow.

Report •

November 22, 2009 at 13:32:04

As you have your own button, the code should be:

ActiveCell.Value = Format(Date, "Short Date")
end sub

Is this what you have?
If not modify it as above and try again.


Report •

Ask Question