+ Reply to Thread
Results 1 to 11 of 11

Button or formula to automatically move to a selected column

  1. #1
    Registered User
    Join Date
    12-17-2014
    Location
    Somerset
    MS-Off Ver
    365
    Posts
    17

    Talking Button or formula to automatically move to a selected column

    Good morning everyone.

    I have a spreadsheet that records clocking in and out times and combined hours. Columns A-D are frozen. The next three columns are headed up by a date and contain Start time, end time and total hours.

    Picture2.jpg

    This format of three columns per day continues through to the end of the year, making it a really wide spreadsheet.

    Is it possible to insert a button somewhere that when clicked moves the unfrozen columns to today's date so times recorded are entered into the correct day. So if I pressed it now, column E headed with today's date would sit next to D, press it tomorrow and column H would be next to D. Does this make sense?



    Does this make sense?

    Thank you!
    Alan

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Button or formula to automatically move to a selected column

    Try this
    Assumes date are in row 1
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Button or formula to automatically move to a selected column

    Quote Originally Posted by novicealan View Post
    Good morning everyone.

    Is it possible to insert a button somewhere that when clicked moves the unfrozen columns to today's date so times recorded are entered into the correct day. So if I pressed it now, column E headed with today's date would sit next to D, press it tomorrow and column H would be next to D. Does this make sense?

    Does this make sense?

    Thank you!
    Alan
    I assumed the dates are in row 1.
    Try this:
    Assign this code to a button.

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Button or formula to automatically move to a selected column

    Quote Originally Posted by Akuini View Post
    ........

    Please Login or Register  to view this content.
    Akuini, did this code work for you?

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Button or formula to automatically move to a selected column

    Quote Originally Posted by davesexcel View Post
    Akuini, did this code work for you?
    Yes, I've tried it.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Button or formula to automatically move to a selected column

    Quote Originally Posted by Akuini View Post
    Yes, I've tried it.
    Okay, when I try your code, it does not find r and goes straight to the msgbox.

  7. #7
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Button or formula to automatically move to a selected column

    Quote Originally Posted by davesexcel View Post
    Okay, when I try your code, it does not find r and goes straight to the msgbox.
    Hm, I don't understand why.
    I can see in your code (btw it works for me too & it's much simpler) you use variable d to get Date.
    This modified code below works for me too. Could you try it?

    Please Login or Register  to view this content.
    And if I try
    Please Login or Register  to view this content.
    the result is 15/11/2018

    I'm using Excel 2007

  8. #8
    Registered User
    Join Date
    12-17-2014
    Location
    Somerset
    MS-Off Ver
    365
    Posts
    17

    Re: Button or formula to automatically move to a selected column

    Quote Originally Posted by Akuini View Post
    Yes, I've tried it.
    Quote Originally Posted by Akuini View Post
    I assumed the dates are in row 1.
    Try this:
    Assign this code to a button.

    Please Login or Register  to view this content.
    Hi,
    Thank you for your help/input
    I have tried entering this code and when I run it, I get an error saying cannot find todays date.

    Just to ensure I am doing it correctly:

    Developer tab>Insert>Form Controls Button>click new>Enter code>click run

    As here:
    Sub Button8_Click()
    Dim r As Range
    Set r = Rows(2).Find(Date, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If Not r Is Nothing Then
    With ActiveWindow
    Application.Goto Reference:=Cells(.SplitRow + 1, .SplitColumn + r.Column - 4), Scroll:=True
    End With
    Else
    MsgBox "Can't find today's date"
    End If

    End Sub

    >Once I click run, it shows an error. I have changed the row number to 2 as well. Cells are formatted as dates. I cannot understand what is missing?

    Picture3.png

  9. #9
    Registered User
    Join Date
    12-17-2014
    Location
    Somerset
    MS-Off Ver
    365
    Posts
    17

    Re: Button or formula to automatically move to a selected column

    Hi davesexcel,
    I have tried your code and the one from Akuini but it doesnt seem to work? I replied to Akuini in the thread but couldnt CC you in. If you are able, your input would be appreciated.
    Thankyou
    Alan

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Button or formula to automatically move to a selected column

    When I ran Akuini's code, then ran my code, my code would not work anymore.

    I had to close excel and when I reopened excel and ran my code, it would work.

    There seems to be something wrong with his find() line. He does say it works for him though.

  11. #11
    Registered User
    Join Date
    12-17-2014
    Location
    Somerset
    MS-Off Ver
    365
    Posts
    17

    Re: Button or formula to automatically move to a selected column

    Davesexcel, Perfect! That solved it. I tried your code agin, re saved as another workbook name (macro enabled), closed it and opened again and its worked!
    Thank you so much! Very grateful!
    Alan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Move cursor to the right automatically after data is entered/selected from the drop downs
    By sloshpuppy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2017, 11:14 AM
  2. button click to move selected multi-column list box items to new list box
    By kranic3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2017, 08:34 AM
  3. [SOLVED] Move selected row up or down with a command button
    By allwrighty in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2016, 08:13 AM
  4. Macro button to move formula cell reference to the next column - HELP!
    By Alice21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2013, 12:04 PM
  5. VBA to automatically move command button to another cell after being clicked on
    By vaznlyfe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2012, 11:29 AM
  6. Replies: 16
    Last Post: 06-25-2011, 05:14 PM
  7. Selected cell move one column to the right
    By marksuza in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2009, 04:26 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1