Macro to insert username and date with a press of a button RRS feed

  • Question

  • Hi all,

    I have build a excel macro that inserts your windows username and the date in a cell specified in the macro code with the press of a button. This is a check off list and has not one, but many lines with a button which does the above. The problem with my code is that if you insert a new check off point (not at the end but between two existing ones), the code of the points below the new one have to be altered as the cell numbers obviously change. This is not really efficient, especially if there are a lot of points beneath the one inserted. Is there a way to make this code more flexible, in a way that new lines can be inserted without having to change the code for the other lines? I was thinking about a button with an assigned macro that puts the username and date one cell to the left of the button.

    So here is my current situation: I have an empty cell with a button one cell to the right of it. Button has the following macro assigned to it:

    Sub Handler_ClickC1()
    Range("G7") = Environ("Username") & " " & Format(Date, "dd-mm-yyyy")
    End Sub

    This code puts windows username and date in the cell to the left of the button.

    Hope someone can help. Thanks.

    Sunday, July 22, 2012 9:01 AM

All replies

  • I'd use a single button that enters the text in the active cell:

    Sub Handler_ClickC1()
         ActiveCell = Environ("Username") & " " & Format(Date, "dd-mm-yyyy")
    End Sub

    That way, you don't have to add new buttons if you insert rows, and you won't have to change the code.

    Regards, Hans Vogelaar

    Sunday, July 22, 2012 9:24 AM
  • Thanks for your response Hans. The above would be a good solution if I were the only one using the sheet. In my case the sheet is used by a number of people and i'm afraid that username &date will get all over the place if they don't realize they have to select the right cell first and then press the button. Is there a code that, based on the position of the button, can put the username&date in the cell to the left of it? So that when you press the button it detects the cell the button is in, and then inserts the above in one cell to the left.
    Sunday, July 22, 2012 8:05 PM
  • Assuming that your command button is a Forms control:

    Sub Handler_ClickC1()
        ActiveSheet.Shapes(Application.Caller).TopLeftCell.Offset(0, -1) = _
            Environ("Username") & " " & Format(Date, "dd-mm-yyyy")
    End Sub

    Regards, Hans Vogelaar

    • Proposed as answer by JP2112 Monday, July 23, 2012 2:45 AM
    Sunday, July 22, 2012 8:15 PM