+ Reply to Thread
Results 1 to 18 of 18

automatically changing the worksheet name based on a cell entry in that sheet

  1. #1
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    automatically changing the worksheet name based on a cell entry in that sheet

    I hope someone can help. I have put together a simple spreadsheet to keep tabs on spending on jobs, what I want to do is enter the job number in a cell (F5 - note that this is part of a merge cell [f5:u5]) and then the tab of that sheet is automatically changed to the job number.


    As my nick suggests I am a novice at this. I have tried entering various VBA codes but I cannot get anything to work.

    Help would be appreciated thank you all.

  2. #2
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hello Novice999,

    Merged cells generally create havoc with VBA coding, however, try the following code and if it doesn't work, you'll need to unmerge the cells or even move the job number to a nearby cell.


    Please Login or Register  to view this content.
    The code is a Workbook_SheetChange event and needs to be placed into the workbook module so, to implement the code:-

    - Press Alt + F11 which will take you to the VB Editor.
    - Over to the left, double click on ThisWorkbook.
    - In the big white field, paste the above code.

    The code should now work in every sheet once you enter the job number and click away (or press enter).

    I haven't tested the code but it includes unmerging and merging the range F5:U5.

    I hope that this helps (and that it works!).

    Cheerio,
    vcoolio.

  3. #3
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    vcoolio

    Thanks works a treat. I unmerged the cells and changed the code to just F5 to be safe.

    Thanks again.

  4. #4
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hello Novice999,

    You're welcome. Glad that I was able to help.

    Good move, BTW, in unmerging the cells!

    Cheerio,
    vcoolio.

  5. #5
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hi there the excel sheet seems to have developed a mind of its own and comes up with the following error message now.

    Run-time error '1004'
    Method 'Intersect' of object '_Global' failed

    I end up clicking on 'end' button probably the amount of times that there are sheets in the workbook.

    when I go to debug the line of code that is highlighted is

    If Intersect(Target, Range("F5")) Is Nothing Then


    The code tat is placed in the 'Thisworkbook' is

    Please Login or Register  to view this content.


    I have now a total of 29 sheets in the workbook.

    any thoughts would be appreciated.

    Thanks.
    Last edited by AliGW; 07-24-2017 at 06:02 AM.

  6. #6
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    I also tried deleting the line of code 'If Intersect(Target, Range("F5")) Is Nothing Then Exit Sub' but then that threw up further error messages.
    Last edited by AliGW; 07-24-2017 at 04:24 AM. Reason: Unnecessary quotation removed.

  7. #7
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hello Novice999,

    I've just tried the code in a mock-up of what I believe your workbook looks like and I can't re-create the error. It works just fine.

    In your workbook, remove the following two lines of code from the one in post #2:-

    Please Login or Register  to view this content.
    and see if that works but make sure that cell F5 is on its own, not merged.

    Also make sure that the code is in the workbook module as explained in post #2.

    Test the code in a copy of your workbook first.

    Cheerio,
    vcoolio.

  8. #8
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    BTW Novice,

    To merge or unmerge cells, all the merged cells need to be taken into consideration. If you look at my post #2, in the code you will see that all the merged cells are taken into account (F5:U5). If you look at how you have adjusted the code in your posts above, you'll notice that you have only considered F5 .

    This may be the reason behind the error.

    Cheerio,
    vcoolio.

  9. #9
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Vcoolio

    Thanks. Just tried it and am not getting any error messages so far.

    Novice

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hi, Novice!

    Welcome to the forum, neighbour.

    Two things to note and act upon, please:


    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (I have added the code tags for you THIS TIME - in future you MUST do this yourself.


    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Vcoolio

    Thank you for your help previously. I wonder if you have any idea why I keep getting the following error message when I group tag a number of sheets in the work book to enhance the spreadsheet for example adding an invoicing column?

    Microsoft Visual Basic
    Run-time error '1004'
    Method 'Intersect' of object '_Global' failed

    Regards

    Novice

  12. #12
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hello Novice,

    Without seeing what you're up to, its hard to tell. Hence please upload a sample of your work book (please use dummy data) and we'll try to sort it out from there.

    Cheerio,
    vcoolio.

  13. #13
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Vcoolie

    Thanks. I get an error message when i want to change something on multiple sheets at the same time and when i highlight cells f5 to f7 and press delete

    Novice
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    Hello Novice,

    If you are high-lighting F5:F7 and clearing the contents (don't delete!) then you will receive the error as you are making a change to the target cell (F5) but also trying to change multiple cells at once. So try changing the code to:-

    Please Login or Register  to view this content.
    to counter the fact that you are trying to alter multiple cells at once.

    I hope that this helps.

    Cheerio,
    vcoolio.

  15. #15
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    ................and to enhance the code a little further:-


    Please Login or Register  to view this content.
    Cheerio,
    vcoolio.

  16. #16
    Registered User
    Join Date
    09-14-2015
    Location
    ipswich, england
    MS-Off Ver
    2010
    Posts
    31

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    thanks for the help

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  18. #18
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: automatically changing the worksheet name based on a cell entry in that sheet

    You're welcome, Novice. Glad that I was able to help again.

    Cheerio,
    vcoolio.

+ 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] Changing color of Tabs based on cell entry
    By $mart Group in forum Excel General
    Replies: 2
    Last Post: 03-11-2017, 04:51 PM
  2. Copy and Paste into new worksheet based on entry on Sheet 1
    By alextwre in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2013, 06:10 PM
  3. [SOLVED] automatically change all cell values based on updated entry
    By ruliansyah in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 02-02-2013, 06:22 AM
  4. Automatically create Gmail with text, based on date entry in Excel worksheet
    By F1Fan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-17-2012, 12:17 PM
  5. Replies: 0
    Last Post: 12-08-2011, 10:12 AM
  6. Replies: 3
    Last Post: 12-05-2010, 05:47 AM
  7. Changing a cell color based on date entry ...
    By T. Denford in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-26-2005, 02:51 PM

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