+ Reply to Thread
Results 1 to 8 of 8

VBA to Check if Cell Formula References Another Sheet

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    VBA to Check if Cell Formula References Another Sheet

    Hello,

    I am trying to use VBA to determine if a formula within a given cell references another sheet. Suppose I am on A1 on Sheet1 and the formula in that cell is "=sum(A2+Sheet2!A2). I want to be able to return something (doesn't matter what) that lets me know that the formula references a different sheet.

    Thank you for any help that can be provided.

    learning_vba
    Last edited by learning_vba; 05-07-2013 at 01:22 PM. Reason: marking solved

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: VBA to Check if Cell Formula References Another Sheet

    This shades any formula cells referencing other sheets to yellow.

    Please Login or Register  to view this content.
    Elegant Simplicity............. Not Always

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: VBA to Check if Cell Formula References Another Sheet

    Hi, Andy,

    too much Selection in the code in order to work with ActiveCell (who needs to do this except the macro recorder) - you should be able to do better than that (thereīs no Select necessary, and action may be taken from another sheet). To my opinion not the best of code examples ever.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: VBA to Check if Cell Formula References Another Sheet

    Thank you for the code. One question- what if I don't know the names of all my sheets (ie: they aren't Sheet1, Sheet2 etc)?

    Thanks

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: VBA to Check if Cell Formula References Another Sheet

    Hi, learning_vba,

    what Andy used is the codename for the sheet, not the one on the worksheet tab.

    If you donīt know the name you could refer to ActiveSheet for the sheet the code is run on or loop through the worksheets in the workbook and rely on them without having to know the names:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Cioa,
    Holger

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: VBA to Check if Cell Formula References Another Sheet

    Holger it was based on a sub to convert functions to values.. so i don't really care

    VBA - do you want the code to iterate through all the worksheets ?

  7. #7
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: VBA to Check if Cell Formula References Another Sheet

    Holger and Andy-

    Thank you both. I will not need code to loop through sheets- I can challenge myself to figure that one out.

    Thanks again. Marking SOLVED

    learning_vba

  8. #8
    Registered User
    Join Date
    03-28-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: VBA to Check if Cell Formula References Another Sheet

    BTW- I thought of an easy way to work around not knowing the name of all the different sheets; change this line:

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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