Consulting

Results 1 to 7 of 7

Thread: Selection Change where a merged cell is involved

  1. #1
    VBAX Regular
    Joined
    Sep 2019
    Posts
    52
    Location

    Selection Change where a merged cell is involved

    Hi there all
    I have a merged cell = P5 to U5

    I have written the following vba to call a macro if this cell changes but to no success


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Target.Address = "p5" Then
    Call ADVDAY1


    End If


    End Sub


    Pls assist to enable me to learn what I have done wrong

  2. #2
    VBAX Regular
    Joined
    Sep 2019
    Posts
    52
    Location
    Got it solved with:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:BC17")) Is Nothing Then
    Call ADVDAY1
    End If
    End Sub

    Better suggestions would be appreciated

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    I never used Merged Cells that way. Try
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.MergeArea.Cells(1).Address = "$P$5" Then ADVDAY1 '$ signs required, Caps required
        If Target.MergeArea.Cells(1).Address = "$W$5" Then ADVDAY2
    End Sub
    Sub Test()
    MsgBox Range("a1").Address & ": test1"
    MsgBox Cells(1, 1).Address & ": test2"
    MsgBox Cells(1).Address & ": test3"
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,872
    Location
    Quote Originally Posted by Marsau View Post
    Hi there all I have a merged cell = P5 to U5 I have written the following vba to call a macro if this cell changes but to no success Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "p5" Then Call ADVDAY1 End If End Sub Pls assist to enable me to learn what I have done wrong

    1. Do you mean Worksheet_SelectionChange or Worksheet_Change?


    The first fires when you just click any cell, the second when you change any cell




    2. I think a little investigation on your part would find that the .Address is returned as "$P$5" so the test will always be False
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Sep 2019
    Posts
    52
    Location
    Thank you for all Info
    SamT your vba works thanks
    Paul_Hossler in hind site I should have maybe used Worksheet_SelectionChange but not sure how to

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,872
    Location
    Quote Originally Posted by Marsau View Post
    in hind site I should have maybe used Worksheet_SelectionChange but not sure how to

    You did use Worksheet_SelectionChange

    I was thinking

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Address = "$P$5" Then
            Application.EnableEvents = False
            Call ADVDAY1
            Application.EnableEvents = True
        End If
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Sep 2019
    Posts
    52
    Location
    Thank you will try last post

Posting Permissions

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