+ Reply to Thread
Results 1 to 4 of 4

Change same cell on all sheets but the first with a command button

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Costa Mesa, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Change same cell on all sheets but the first with a command button

    Hi! I have a vacation tracker workbook with a worksheet for each employee (we only have 10 employees so it's not too bad) and the first worksheet is a summary sheet (used to do the month end vacation accrual). On the Summary sheet, there is a cell named SmryDate where I put the month ending date for the accrual and there is a table below it that lists each employee and the number of vacation hours (done by referencing named ranges on each employee's ws). On each of the employee's worksheets, cell G4 (named AsOfDate for all worksheets) is the date the worksheet uses to calculate the hours as of that date. What I want to have is a command button next to the SmryDate cell that changes the AsOfDate on all sheets to the SmryDate. I tried the simple Range("AsOfDate").value = Range("SmryDate").value but that didn't do anything...I'm assuming because the name is the same on all tabs and it doesn't understand (I searched 3D range and saw a post where someone said VBA can't handle those). The sheet names are the employee names so those will not always be the same.

    Also, I need to be able to change the AsOfDate on individual tabs so simply putting a formula in those cells to say =SmryDate will not work. I'm guessing the answer is something along the lines of selecting all sheets but the first and setting cell G4 (that will always be the same) to SmryDate, but I'm not that versed in VBA code so I don't know how to write that code. Any help would be greatly appreciated!!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Change same cell on all sheets but the first with a command button

    i know you said you didnt want this, but if ou have 3 cells on each emp sheet, you could have 1 cell for manually entered date, a cell for an X to tell that sheet to USE the manually entered date or not, and then based on the X (or lack there-of) you just reference the AsOfDate to a cell on your summary, so that when you change that date, it changes thru all your sheets?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    Costa Mesa, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Change same cell on all sheets but the first with a command button

    I reference AsOfDate in a bunch of different formulas, some of which are quite lengthy and a headache to work with, so it would be a lot of work to go in and change everything. Thanks for the reply, though!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Change same cell on all sheets but the first with a command button

    ok, so could you change my suggestion around and make your empl cell somthing that references AoD insttead?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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