Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run a macro from a specific cell (Excel 2000)

    Hi,

    I want to be able to run a macro from a specific set of cells, so example as follows

    Column A has a list of name
    A1 Mike
    A2 John
    A3 Pete
    etc....

    I want to select John and rn the macro but need to pass the name and cell position to the macro

    Each name has to have the macro enabled and when new names are added the macro is enabled for them.

    Any help appreciated.

    Mike

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    You can look at ActiveCell in a macro. ActiveCell.Address will give you the position of the cell (or you can look at ActiveCell.Row and ActiveCell.Column). ActiveCell.Value will give you the value.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    Thanks Hans,

    That I understand, but I want it so that the user can click the cell with the name in it and it then runs the macro rather than have a button they click by each name, is that possible.

    Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    It is possible to use the Worksheet_SelectionChange event to run code when the user selects a cell, but it would be run *each* time the user selects a cell, whether by clicking on it or by using the Enter, Tab or arrow keys to get there. Would you really want that?

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    Ohh no..

    The sheet is used to hold certain data which the user can maintain, but there is a level of data required which is complex and multi dimensional which I want to control via a macro and is only need occasionally, hence I want them to select the name cell to activate it, so I guess the only option would be a small button next to the cell to activate the macro.

    Mike

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    You don't need a button next to every cell. A single button and a single macro will do - as mentioned, you can inspect ActiveCell in the macro to see which cell was selected before the user clicked the button.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    Thanks Hans,

    I could have a single button but I am not sure the users are that bright and I was trying to make it simple for them.

    Thanks for our help

    Mike

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro from a specific cell (Excel 2000)

    You could also use the Worksheet_Before Doubleclick or the Worksheet_Before_Rightclick to run a macro if the user double clicks or right clicks on the worksheet. The macro could then use the Target parameter to see if the user Double or Right clicked in one of the cells you are interested in. Try double clicking in a cell in the range A1:A100 on Sheet1 in the attached workbook.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •