+ Reply to Thread
Results 1 to 10 of 10

Unable to fire Private Sub Worksheet_SelectionChange on user Input

  1. #1
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Question Unable to fire Private Sub Worksheet_SelectionChange on user Input

    Good Day All (Please see Most recent post below)

    VBA Skill: Pretty Basic
    Excel type: Excel 2010

    Problem:
    Range("A1:A10") is where my users will enter a text structure; 1.0, 1.1, 1.2, 2.0, 2.1 etc. If the user enters a text string that includes "0" then we would like the code to activate and place a formula Offset(0, 4) from the ActiveCell, the following code is cobbled together from various posts and works to some extent:

    Please Login or Register  to view this content.
    This code activates if we have entered 1.0, 2.0, 3.0 etc however it only works after we return to the Cell (with Keyboard Navigation Keys), what do we need to do to make the code activate on "Enter" or when we leave the ActiveCell; i.e. User inputs 1.0 hits Enter, Tabs or Navigates using the arrow Keys Then Code Activates.

    I wondered if the following would be more effcient than ActiveCell but i can't get it to work with the above code

    Please Login or Register  to view this content.
    Any help will be gratefully received

    As advised below; Apologies for delay, my work Internet is rubbish!
    Test Macro Entry via VBA.xlsm
    Last edited by VirEgregius; 01-31-2013 at 08:13 AM. Reason: uploading dumb test Worksheet, forward SuperUser(s) to most recent post

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    Change SelectionChange to Change.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    Hi Norie thanks for the prompt response, just tried it in various guises, even closed Worksheet down and re-opened, but it won't fire at all using

    Please Login or Register  to view this content.
    I'll edit my original post and upload a dumb test Worksheet

  4. #4
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    I went back to the drawing board and support.microsoft.com and have come up with the following:

    Please Login or Register  to view this content.
    Am currently trying to change the line
    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    this is how i would expect it to look as a function =IF(RIGHT(A1,LEN(A1)-FIND(".",A1,1))="0","Do Nothing", "Add Formula as per ActiveCell.Offset")

    Any help as always gratefully received
    Last edited by VirEgregius; 01-31-2013 at 08:11 AM. Reason: added support.microsoft URL

  5. #5
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    Got it
    Please Login or Register  to view this content.
    should actually read
    Please Login or Register  to view this content.
    less the: " "

    Working Code So Far:

    Please Login or Register  to view this content.
    Last edited by VirEgregius; 01-31-2013 at 09:11 AM. Reason: Modified View of Working code So Far

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    You can replace Range(Target.Address) with Target.

  7. #7
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    Hi Norie

    I changed Range(Target.Address) to Range(Target) and I got the follwing Run-time error:

    Run-time error '1004':

    Method 'Range' of Object '_Worksheet' failed

    Will keep battling

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    not Range(Target) - just Target
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  9. #9
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    Ahhh Got it in the end thanks for the Help JosephP

    next version of code

    Please Login or Register  to view this content.

    Now I'm goign to change
    Please Login or Register  to view this content.
    to delete the SUM if a user needs to backtrack

  10. #10
    Registered User
    Join Date
    01-30-2013
    Location
    Crawley, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Unable to fire Private Sub Worksheet_SelectionChange on user Input

    I've only bloody gone and done it!!

    Thanks to Norie and JosephP for helping tidy up my working code here's the finished article:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1