1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Run Excel Macro with "IF" Statement

Discussion in 'Business Applications' started by dmsadler, Feb 6, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. dmsadler

    dmsadler Thread Starter

    Joined:
    Oct 8, 2004
    Messages:
    19
    I'm using Microsoft Excel 2003. I have an excel macro that hides a tab/sheet in a workbook. I want this macro to run when an "X" is placed in a specific cell. Is this possible???
    For example, if cell A56=X then run Macro1

    Please keep in mind, I don't know Macros very well, and know nothing at all about programming with Visual Basic or Microsoft Script Editor.

    Thanks for any help :)
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Then how do you know what the macro does? ;)

    Okay, so you need to get into the Visual Basic Editor (as opposed to the Microsoft Script Editor, which you do NOT need to be in). So, to get there hit Alt + F11. Ctrl + R will bring up the Project Explorer, Ctrl + G will bring up the Immediate window, and F4 will bring up the Properties window. Keep these windows open at all times. I'd also recommend opening the Locals window (find from the View menu in the VBE, especially as opposed to the Watch window, even though it is very good as well).

    So, once in the VBE, double click the file in which the macro/routine is located, double click the module folder and then the module as well. You can copy/paste your routine here in its entirety. Your routine could then be edited something like this for your if/then statement...

    Code:
    Sub YourRoutineNameHere()
    
        If Thisworkbook.sheets("Sheet1").Range("A56").Value = "X" Then
        
            '... rest of your [original] code here
        
        End If
    
    End Sub
    Notice two things right off. The workbook referenced is ThisWorkbook, which is an object in the Excel object library which references the workbook from which the code is called. So if the code is going in a workbook other than that which it is embedded in, that will need to be changed. Also note the worksheet. I 'hardcoded' a worksheet name of "Sheet1". If this will be dynamic, you'll need to specify how and what the conditions will be for it, or change the name accordingly.

    Also, as a final note, please, whatever you do, when you use any code, please make sure and read through it all the way, each line. Most of it (or part of it) may not make sense, but you should read it all. This is because there are some things which will make sense, plus the more you read it, the more familiar you will become with it and how it works. Remember, anything inside quotation marks will not update or be dynamic in any way, so you'll need to pay strict attention to those items (i.e. sheet name and cell address above).

    HTH
     
  3. jmosmith

    jmosmith

    Joined:
    Aug 10, 2004
    Messages:
    6,091
    Awesome tip!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/680284