Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Triggering macros (XL97/WinNT)

    How would one trigger a macro to run when a certain cell is clicked, pls?

    eg. If someone clicks in a cell without filling another in first, I would want to say "if range("a3") = "" then msgbox "You have not entered your name": range("a3").setfocus (or the equivalent in XL - I know Word better!).

    I guess it would be sub ... _click (), but I can't find out what the ... should be!

    Any help would be gratefully appreciated!
    Beryl M


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

    Re: Triggering macros (XL97/WinNT)

    Activate the Visual Basic Editor (Alt+F11)
    Make sure that the Project Explorer is visible (Ctrl+R)
    Expand the file you're working on.
    Expand Microsoft Excel Objects.
    Double click the sheet you want this code for.
    You should get the skeleton for the Worksheet SelectionChange event. This event fires every time you select another cell (or range) in this worksheet.

    Enter or paste the code below, so that it looks as follows:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Application.EnableEvents = False
    Select Case Target.Address
    Case "$A$4"
    If Range("A3") = "" Then
    MsgBox "A3 is empty"
    Range("A3").Select
    End If
    End Select
    Application.EnableEvents = True
    End Sub

    Replace $A$4 by the address of the cell you want. You can also enter a series of cells:
    ...
    Case "$A$4", "$B$4", "$C$4"
    ...

    HTH,
    Hans

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Triggering macros (XL97/WinNT)

    This should go in your sheet's code, instead of a module or ThisWorkbook
    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Range("A1").Text = "" Then
    MsgBox "Cell A1 is empty!"
    End If
    End Sub</pre>


  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Triggering macros (XL97/WinNT)

    Right-click on the sheet-tab at the bottom of the worksheet that contains your cell. Select view code. In the object drop-down, select "Worksheet." In the procedure drop-down, select "Selection Change." Add a line of code so that you have:
    <pre>Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells(1, 1) = Target.Address
    End Sub</pre>


    Try clicking various cells on your worksheet. You can check the address property to make it cell specific. But, if I am only interested in a cell or a range of cells, I make this a named range and check if the Intersect of the Target and the Named Range is Nothing. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Triggering macros (XL97/WinNT)

    Many thanks, Hans, that was exactly what I needed! Thanks also, Sammy and K Smerdonk.

    I don't suppose anyone knows how to tell XL to move the active cell one to the left, similar to the MoveLeft command in Word, do they? ie If B4 is active, move to A4; if G5 is active, move to F5 - when I tried recording it, it just said range("A4") (or F5) - what I want is a command to move one cell left from wherever it is!

    Many thanks!
    Beryl M


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

    Re: Triggering macros (XL97/WinNT)

    The cell that is clicked on is the Target argument. Use Offset for relative positions.

    Try:

    Target.Offset(0,-1).Select

    This will select the cell in the same row (RowOffset=0) and one column to the left (ColumnOffset=-1) of the "target" cell.

    Warning: this will bomb if the target is in column A, so you must be careful to test the target!

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Triggering macros (XL97/WinNT)

    Thanks Hans, that's great - it won't be in column A because this is going to happen if the user has clicked into a cell in column E without filling in the equivalent in column D - a msgbox tells them to behave then this is to move them to column D!

    Many thanks <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Triggering macros (XL97/WinNT)

    Beryl,

    When you are recording a Macro you can tell Excel to use relative referencing.

    To do this click on the second Icon on the Stop Recording Macro toolbar. Click on it again to return to absolute referencing.

    Andrew C

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Triggering macros (XL97/WinNT)

    Ah - now that's interesting; I'll have to try that! Many thanks, Andrew!
    Beryl M


Posting Permissions

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