+ Reply to Thread
Results 1 to 8 of 8

To stay put in target cell when no value is entered & the user hits ENTER

  1. #1
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    To stay put in target cell when no value is entered & the user hits ENTER

    hi,


    can anyone point out where i'm wrong on my Worksheet_Change 1st paragragh? I just need the target cell AC4 to stay put in its cell when no value is entered but the user does hit ENTER key anyway. The first paragragh doesn'tseem to be achieving this. Only if there is data input jump to Y4.


    Please Login or Register  to view this content.

    (I need this mainly because once in protected sheet mode, whenever user hits enter key, no matter if AC4 has data or not, the screen always oddly pans itself to right side out of main worksheet view, i think it may be drawn there by another faraway unprotected cell that doesn't need daily inputs)

    cheers
    Stewart.
    Last edited by MannStewart; 07-01-2020 at 12:12 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    Stewart,

    1. your initial Intersect test should be Not (per the ElseIf)
    2. Target.Cells.Count will always be >= 1 -- if you want to test Empty use the Target(1).value or Target(1) = Empty

  3. #3
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    Quote Originally Posted by XLent View Post
    Stewart,

    1. your initial Intersect test should be Not (per the ElseIf)
    2. Target.Cells.Count will always be >= 1 -- if you want to test Empty use the Target(1).value or Target(1) = Empty
    hi Xlent,

    you mean likethis for 1st phrase?

    Please Login or Register  to view this content.
    but the above i tried and it also doesn't achieve the desired keep the cursor stay put in Target when ENTER is hit without any data.. what's the cause? The screen is still panning to the right after hitting ENTER key...

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    Do you want it like this?
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    Quote Originally Posted by jindon View Post
    Do you want it like this?
    Please Login or Register  to view this content.
    hi Jindon

    i tried inserting your code at the beginning of mine,it didn't help: with your code, what's still the same - when i start work from AC4, input data and ENTER the screen is still panning off ("jumping away" to the idle right side, off focus from view) from AC4 as soon as ENTER is punched like my current problem situation. What's worse than my situation, with your code this happens even in Unprotect mode, ie. if without typing any data into AC4 it doesnt connect to my rest of code instructions in Unprotect mode. My objective was to keep the cursor stay put in AC4 when AC4 is hit ENTER with no data typed into it.

    With my original imperfect code, EVEN in Unprotect mode, everything is fine, if input in AC4 the cursor will happily leap to Y4, while if no input and user hits ENTER cursor stays put nicely in AC4.
    But with your code, in Unprotect mode, hit ENTER from AC4 with no data in cell, the cursor falls to the cell AC5 below AC4, and this is what I want to avoid. i just need to keep cursor in AC4 if AC4 has no data and you hit ENTER from AC4. I hope you can see what i want to do..(?) I need to work in Protected mode.

    Is it possible in any way, to have an instruction before my main IF,ELSEIF code body,just to keep the cursor UNMOVED STAY PUT in AC4, if the Target is AC4, but NO INPUT in AC4, (or Y4, or AA4) & user HITS ENTER from AC4 anyway?
    Last edited by MannStewart; 07-01-2020 at 10:09 AM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    Not really sure though...
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    Quote Originally Posted by jindon View Post
    Not really sure though...
    Please Login or Register  to view this content.

    i tried this, but since i already have an existing Worksheet_Activate code, so i integrated your these lines
    Please Login or Register  to view this content.
    into my Worksheet_Activate,

    ..the ERROR msg Runtime Error 424: Object required appears when executed

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: To stay put in target cell when no value is entered & the user hits ENTER

    1)
    Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    2) No idea about that error. It is working here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 01-21-2020, 07:58 PM
  2. Stay in same cell until data is entered
    By melverz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-09-2019, 11:06 AM
  3. [SOLVED] Date separated by DOT to automatially convert into Slash as soon as user hits enter
    By abhinavbinkar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-19-2018, 11:19 AM
  4. Format a row when user hits enter
    By jcmckeon in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-29-2014, 01:42 PM
  5. Auto-sort but stay with cell just entered.
    By Hest20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2013, 02:44 PM
  6. Detect if user hits cancel from user input type box
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-20-2011, 11:42 AM
  7. [SOLVED] how do I hit return but stay in the same cell to enter a new line
    By Bijan001 in forum Excel General
    Replies: 2
    Last Post: 12-27-2005, 01:10 PM

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