Attend Excelapalooza
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Form control- button with updated date.

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Form control- button with updated date.

    Hello there,

    Need help with a VBA code or a macro for a clickable button.

    When the button is clicked I would like "the text in the button" to update the days date.

    Thank you!

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Posts
    143
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Hello, If I've understood this correctly, this should do.

    My code uses a button named "Button1":

    Code:
    Sub Button1_Click()
        ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Text = Date
    End Sub
    When you click the button, the text on the button will update to reflect the current date.
    Last edited by hotabae; May 14th, 2018 at 09:30 AM.

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by hotabae View Post
    Hello, If I've understood this correctly, this should do.

    My code uses a button named "Button1":

    Code:
    Sub Button1_Click()
        ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Text = Date
    End Sub
    When you click the button, the text on the button will update to reflect the current date.
    Hmm, doesn't work for me, says the code is wrong :/ And yes, I've renamed my bitton to "Button1"

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,088
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Hi,
    try

    Code:
    Sub Button1_Click()
        ActiveSheet.Buttons("Button 1").Caption = Format(Date, "dd/mm/yyyy")
    End Sub
    Dave

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by dmt32 View Post
    Hi,
    try

    Code:
    Sub Button1_Click()
        ActiveSheet.Buttons("Button 1").Caption = Format(Date, "dd/mm/yyyy")
    End Sub
    Dave
    Error 1004
    "Unable to get the Buttons property of the worksheec class"

    Guess I'm doing something wrong..

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,088
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by johannordgren View Post
    Error 1004
    "Unable to get the Buttons property of the worksheec class"

    Guess I'm doing something wrong..
    Is your sheet protected?

    if so, try this update

    Code:
    Sub Button1_Click()
        With ActiveSheet
        .Unprotect Password:=""
            .Buttons("Button 1").Caption = Format(Date, "dd/mm/yyyy")
        .Protect Password:=""
        End With
    End Sub
    add password as required

    Dave
    Last edited by dmt32; May 14th, 2018 at 10:06 AM.

  7. #7
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by dmt32 View Post
    Is your sheet protected?

    Dave
    Nope

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,088
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by johannordgren View Post
    Nope
    Is the button on the Activesheet when code is run?

    Dave

  9. #9
    Board Regular
    Join Date
    Apr 2018
    Posts
    143
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by johannordgren View Post
    Hmm, doesn't work for me, says the code is wrong :/ And yes, I've renamed my bitton to "Button1"
    If you're willing to take another shot with mine, you can right click the button and use "assign macro" then make sure the Button1_click is actually selected.

    For the record, I'm having success with my code and Dave's code (though my 'merican date format doesn't match Dave's) :P

  10. #10
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form control- button with updated date.

    Quote Originally Posted by dmt32 View Post
    Is the button on the Activesheet when code is run?

    Dave
    I suppose. I only have one "Sheet". And I open VB from "view code" by right-clicking on the button.
    If this is what you mean with "Activesheet".

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com