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.

Dumb Macro Question (Excel)

Discussion in 'Business Applications' started by nickcherry, Nov 9, 2005.

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

    nickcherry Thread Starter

    Joined:
    Nov 9, 2005
    Messages:
    1
    I've recorded a few macros successfully, but I am having trouble applying them to cells. How can I make Cell A1, for example, perform macro M1 when I click on the cell?

    Thanks,
    Nick Cherry
     
  2. wolfworx

    wolfworx

    Joined:
    Oct 2, 2001
    Messages:
    1,215
    I can't be sure of your problem without seeing your macro, but I suspect you are getting an Absolute execution where you need a Relative execution.

    Absolute recording of a macro to type in three lines starting in Cell A1 will look like this:
    ActiveCell.FormulaR1C1 = "Entry one"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Entry two"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Entry three"
    Range("A4").Select

    The problem with this coding is, when the macro is executed, "Entry one" will be typed int the current cell, but the next two entrys will ALWAYS be typed into cell A2 and A3!


    Here are examples of Relative coding:

    ActiveCell.Offset(1, -5).Range("A1").Select

    the above selects a cell one row down and five rows to the left of the current cell.

    ActiveCell.FormulaR1C1 = "=R[-1]C]+RC[-1]"

    The above creates a formula by taking the value one row above and adding it to the value one column to the left.

    If this is not your problem, please provide more information.
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    It's difficult to apply these things to cells. You can trap events of Excel, but those are particular and an action must be performed, a selection change, cell change (from edit mode), etc. If you are wanting to run a macro routine from clicking on a cell, you would probably be better off making a clear rectangle over the cell and assigning the macro you want to that shape. It should be a shape from the drawing toolbar.

    Right click the shape and Format it to have no Border and no Line (if desired) as so you can see the cell. You can still get to the cell underneath, think of it as layers which your mouse cannot penatrate, but your keyboard can. You can get there with your arrow keys, F5 or tabbing/entering there. By clicking the cell with your mouse, you will click the shape on top of it by default (assuming your shape covers the entire cell).

    Right click the shape and click Assign Macro, then choose your routine of choice and click ok. Now every time you click that shape it will run the specified routine.

    Is this what you were talking about? HTH
     
  4. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Zack,

    I have a friend who needs a similar requirement, and I was wondering how to put a listener on a cell. The trouble is, he needs a common routine for every cell in a range, but the "active cell" (i.e., the cell beneath the object receiving the click) makes a difference. It'd be a maintenance nightmare to have to place an object over every cell in the range, but if that's what it takes, that's what we'll do. However, is it possible to include an argument with the function call described here:

    ? I seem to recall having trouble when trying to include arguments in macro calls. The parameter would be a string representing the range.

    Thanks, and to anybody else who can answer as well.

    chris.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You can grab what cell is underneath an object like that if you need it. Is that what you mean? I think it's the TopLeftCell property and there should be a BottomRight as well. This should give you the range which the shape exists over.

    Another option is to give the shape a name and use the Application.Caller method for obtaining it's name which could correspond to a range or action.
     
  6. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Thanks for the feedback. Yeah, I hit a wall trying to include the range as an argument. The second option sounds promising, but I'd hate to have to maintain it. There are some 200 cells in the range, and each needs to handle a click event. Can a worksheet object listen for events? That might be easier, so that if the active cell falls within the range the event handler runs. Is this possible?

    chris.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yes. If you are using actual cells and the user is selecting them, yeah, there is the SelectionChange event along with the Change event on it's own. Or maybe I'm not understanding everything here.. If the user wants to just click on a cell in a range and the desired range of interest is known ahead of time, this can be done easily. Is that what you mean?
     
  8. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Yeah, that'd be the setup. The application is a seating reservations database for a community theater, and the requirement is for a (developed) UserForm to open when a particular cell is clicked. The workbook is already designed, more or less, but currently the users have to click a cell, then manually open the form from the menu bar. I don't know much about automating Excel but I figured the designer could use a named range to handle the click events combined with the worksheet's ActiveCell property. That's what I suggested but I don't know if it would work. Just doing some follow-up/research on my own, I guess.

    Do you have any code models/links for this kind of thing?

    Thanks again man.

    chris.
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yeah, here is the basics ...

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
        'Exit if more than one cell selected
        If Target.Cells.Count > 1 Then Exit Sub
        
        'Ensure selected cell is in desired range
        If Intersect(Target, Me.Range("A1:F10")) Is Nothing Then Exit Sub
        
        'Perform work here
    '    MyUserForm.Show
        
    End Sub
    Change the range to anything you'd like and the name of the userform. When you right click the sheet tab and select View Code, that is the module it goes into. You can add as many checks into it as you would like.

    Does this help a little more Chris?
     
  10. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    That's exactly what I need, thanks a ton (y)

    chris.
     
  11. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    [bump]

    lol, too bad I'm not OP; otherwise, I'd mark this solved.

    Ah well.

    chris.
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    LOL!

    No problem Chris. ;)
     
  13. Sponsor

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/415211

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice