Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    5

    activate macro when cell is selected

    hello eveyone.
    im trying activate a macro when a cell is selected.
    for example:

    i have a range of cells a1 d10.
    if any cell within this range is selected i would like for a macro to be run automatically.

    when cell is selected a box would come up with options of selecting 1-6.
    value of selected cell would copy to cell assign to option.

    1=a20
    2=a21
    3=a22
    4=a23
    5=a24
    6=a25

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    This took me about 2 days to find it out

    here is the trick
    ALT+f11 get the VB thing
    the in Excel Objects
    double click on the said sheet

    change the drop down boxes to worksheet and Change

    you show see a

    Private Sub Worksheet_Change(ByVal Target As Range)


    End Sub

    what I did was create a select case on the Target

    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address

    CASE "$D$10"
    call that_other_sub
    Case "$E$16", "$E$17", "$E$18", "$E$19", "$E$20", "$E$21", "$E$23"
    call and_other_sub
    case else
    end select
    End Sub

    you make you user very happy when you get it working
    Last edited by myle; 11-27-12 at 21:09. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  3. #3
    Join Date
    Nov 2012
    Posts
    5
    thanks for replying.
    i tried this but wont even run anyone with any suggestion?
    thanks

  4. #4
    Join Date
    Nov 2012
    Posts
    5
    this is a start.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$A$1" Then <---i need this to be a range a1:g10
    MsgBox "Cell " & Target.Address & " has changed."

    End If
    End Sub

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    Quote Originally Posted by carlmart711 View Post
    thanks for replying.
    i tried this but wont even run anyone with any suggestion?
    thanks
    what happen

    I found this

    excel - VBA test if cell is in a range - Stack Overflow

    and tested it it work
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    Here the file I created base on the that link

    it a nice trick MMMM think i can use this on my own staff
    Attached Files Attached Files
    Last edited by myle; 11-28-12 at 00:43.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

Posting Permissions

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