Cursor positioning in an Excel sheet
Solved
sebyyy
-
Anonymous user -
Anonymous user -
Hello,
I have been working on a project for a while and I am trying to figure out how to position the cursor on a specific cell.
Let me explain:
I need to insert numbers into a range, in the meantime, I need to run a macro, and I would like the cursor to automatically return to the cell I was on before using my macro once I'm done with it.
Could someone help me?
The only problem is that it is not always
Configuration: Windows 7 / Chrome 52.0.2743.116
I have been working on a project for a while and I am trying to figure out how to position the cursor on a specific cell.
Let me explain:
I need to insert numbers into a range, in the meantime, I need to run a macro, and I would like the cursor to automatically return to the cell I was on before using my macro once I'm done with it.
Could someone help me?
The only problem is that it is not always
Configuration: Windows 7 / Chrome 52.0.2743.116
7 réponses
Good evening sebyyy,
Look at this VBA code:
You can also use this method:
If your problem is solved, please go to the top of the page
to click on "Mark as resolved".
Best regards. 😊
Look at this VBA code:
Option Explicit Sub Essai() Dim cellX As Range ' 1) before doing the work of the macro: Set CellX = ActiveCell ' 2) work of the macro Essai = what you want to do: ' insertion of numbers in a range (or other) ' 3) go to the starting cell (before the work) CellX.Select End Sub
You can also use this method:
Option Explicit Sub Essai() Dim lig As Long, col As Integer ' 1) before doing the work of the macro: lig = ActiveCell.Row: col = ActiveCell.Column ' 2) work of the macro Essai = what you want to do: ' insertion of numbers in a range (or other) ' 3) go to the starting cell (before the work) Cells(lig, col).Select End Sub
If your problem is solved, please go to the top of the page
to click on "Mark as resolved".
Best regards. 😊
Thank you Alkan for your super quick response, but it shows me an error message and the VBA window opens every time...
Should I make a modification in the VBA?
;-)
Should I make a modification in the VBA?
;-)
My VBA code must be placed in a module, for example in Module1.
And not both at the same time: either the first VBA code or the second!
What is your error message? When you click on the "Debug" button,
which line of your VBA code is highlighted in yellow (if there is one)?
Can you post in your next message the Sub of your VBA code that is causing
the problem (by simply copying / pasting)? Looking forward to hearing from you. 😊
Hi Sebyyy, maybe you'll see better with my Excel 2007 file? ????
When you open the Excel file, you can press < Ctrl >< a > to run the macro Essai1() or < Ctrl >< b > to run the macro Essai2(). They will do
exactly the same thing, but the VBA code is different: it's up to you to
read everything in Module1 (there are a lot of comments).
If it's okay, don't forget to mark the subject as resolved; otherwise, send me
your Excel file via cjoint.com or mon-partage.fr ????
Good evening sebyyy,
You wrote: "I can't do it even after multiple attempts... :-( ";
but with which Excel file? Yours, or the one for which I posted the link
in my message #6?
If it's in mine, which I tested and which works correctly,
then it just comes down to an operation that you should perform.
If it's in yours, the best thing is for you to send me your file:
go to the website mon-partage.fr; you need to open the file, then
click on the "Upload" button; wait a bit: you will then see
a link to copy/paste into your next
post on the forum.
If you can't do it, try again with the site
cjoint.com; you will need to enter your email address, which will remain
confidential. There will also be a link to copy/paste.
Looking forward to hearing from you. 😊
Hello,
To attach a file, use the site cjoint.
You will find a complete tutorial that will help you do this.
https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
Thank you.
To attach a file, use the site cjoint.
You will find a complete tutorial that will help you do this.
https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
Thank you.
albkan,
Here is the link:
https://mon-partage.fr/f/z6pmtPCy/
Thank you again for your valuable help ^^
Here is the link:
https://mon-partage.fr/f/z6pmtPCy/
Thank you again for your valuable help ^^
Hello sebyyy,
If you downloaded and tried the Excel 2007 file from my message #6, you
might have seen that it is a very good demo of what you initially asked for.
Let me know if this demo helped you better understand how
to save/retrieve the position of the active cell?
----------------------------------------------------------
Everything that follows is for the file you sent me (thank you for that).
=================================
1) You didn't forget, did you, that you have 3 hidden sheets? 😉
----------------------------------------------------------
2) It's normal that you couldn't get my VBA code to work: you put it
in ThisWorkbook instead of putting it in a module! So select it
entirely and cut/paste from ThisWorkbook (which will then be empty)
into Module3 (which is currently empty). But by reading the comments,
you will understand that in order to see that it works, you need to add a task of
your choice at step 2) of the VBA code that goes to a cell other than the
active cell. If you do all this correctly, you will have the same type of
functionality as that of my demo (the one I mentioned at the beginning of
this message, so the Excel 2007 file I sent you).
----------------------------------------------------------
3) Following the previous cut/paste, Module3 which was empty is no longer so.
Module5 is empty; Module4 contains only one line: Option Explicit
(therefore useless since there are no sub or function) => as if it were
empty too, so you can delete both modules 4 and 5.
----------------------------------------------------------
4) What you asked next in your text bubble is not very clear!
The column where you enter numbers, is it really from B22 to B111?
Then, should it continue from G22 to G111?
Still regarding what you asked, can you confirm for me that:
a) Only the 1st sheet "LOTOVérif" is concerned, or should I look at other
sheets (possibly hidden)?
b) Only the macro QUINE1 from Module6 is concerned or should I check another
part of your VBA code (if yes, which one?)
----------------------------------------------------------
5) So for now, I understand this: for your new request, I should only look at
the 1st sheet "LOTOVérif" and the macro QUINE1() from Module6; then:
Each time you use your macro QUINE1() from Module6, your active cell
which was initially B22 must move down one cell until B111; and then,
either it stops at B111, or it should continue in G22 to move down one
cell until G111.
It's up to you to confirm this to me or to clarify better what you want! Because keep in mind
that a well-stated exercise statement is already 50% of the solution. 😉
=================================
Looking forward to hearing from you. Best regards. 😊
Hi Albkan,
Thanks again for the info. So, I would like the active cell to always be between cells B22 and B111. Cells G22 to G111 are reserved for something else... (to display the 90 numbers).
Thanks ;-)
Thanks again for the info. So, I would like the active cell to always be between cells B22 and B111. Cells G22 to G111 are reserved for something else... (to display the 90 numbers).
Thanks ;-)
Albkan,
I am attaching the file again with additional macros. You will have a little bubble with what I would like more specifically. I hope you can help me with the finalization of my program.
And, we won't worry about the hidden tabs... it's data for my project to work.
Thanks again ;-)
https://mon-partage.fr/f/DnnqgnpD/
I am attaching the file again with additional macros. You will have a little bubble with what I would like more specifically. I hope you can help me with the finalization of my program.
And, we won't worry about the hidden tabs... it's data for my project to work.
Thanks again ;-)
https://mon-partage.fr/f/DnnqgnpD/
Hello sebyyy,
Your new Excel 2007 file is ready!
-------------------------------------------------------
1) Usage
When you enter a number in one of the cells B22 to B111, press < Enter >
to stay in the same cell; or < ↓ > to move down to the next cell.
-------------------------------------------------------
2) Regarding VBA
You will see that I was able to make a lot of simplifications; for the modules,
there are only 2 left, and everything is in Module1 and Module2.
-------------------------------------------------------
Let me know what you think, and if it suits you.
If your issue is resolved, please go to the top of the page
to click on "Mark as resolved".
Best regards. 😊
Hi albkan,
First of all, a big thank you for your help which is advancing my project wonderfully! ^^
However, I still have a few little things... it's by doing a test that we notice some things that don't work perfectly... :-/
1) I would also like that when I use the macro "findes90," the cursor moves to the end of the numbers I just inserted. So, if I inserted, for example, 42 numbers in the cells from B22 to B111, my macro will clear the numbers in the cells from G22 to G111, and I would like the cursor to be positioned right after the numbers inserted in the cells from B22 to B111.
2) There is a yellow box next to the red "LOTOVérif" square. If I do a manual check of a card (that wouldn't be valid), the cursor does not automatically move to the cells from B22 to B111. Is that possible too? For example, during the game, when someone announces, I manually check the card by typing the number in this yellow box, followed by the "ENTER" key. But the active cell remains the one just below.
3) This is a luxury, but if you can do this, I would like the lines from 21 to 100... (all the way to the bottom of the page) to be hidden. I tried, but when I insert my numbers, the active cell B22 never changes. (Only if it's possible).
I know I'm being a pain, but I promise after this, I'll leave you in peace. If it works, my project will be finished, and I will be able to use it soon.
Once again, thank you for your help, which is very precious to me.
Best regards
First of all, a big thank you for your help which is advancing my project wonderfully! ^^
However, I still have a few little things... it's by doing a test that we notice some things that don't work perfectly... :-/
1) I would also like that when I use the macro "findes90," the cursor moves to the end of the numbers I just inserted. So, if I inserted, for example, 42 numbers in the cells from B22 to B111, my macro will clear the numbers in the cells from G22 to G111, and I would like the cursor to be positioned right after the numbers inserted in the cells from B22 to B111.
2) There is a yellow box next to the red "LOTOVérif" square. If I do a manual check of a card (that wouldn't be valid), the cursor does not automatically move to the cells from B22 to B111. Is that possible too? For example, during the game, when someone announces, I manually check the card by typing the number in this yellow box, followed by the "ENTER" key. But the active cell remains the one just below.
3) This is a luxury, but if you can do this, I would like the lines from 21 to 100... (all the way to the bottom of the page) to be hidden. I tried, but when I insert my numbers, the active cell B22 never changes. (Only if it's possible).
I know I'm being a pain, but I promise after this, I'll leave you in peace. If it works, my project will be finished, and I will be able to use it soon.
Once again, thank you for your help, which is very precious to me.
Best regards
Good evening sebyyy,
Based on what you wrote in your point 2), I recommend doing this:
Office button (= File), “Excel Options”; on the left side: “Advanced Options”;
on the right, 1st section “Editing Options”, uncheck the following box:
☐ Move selection after entering
It's much more practical: it means that after validating a cell with
the < Enter > key, the selection remains on that same cell, which allows
you to do other things on it: formatting like bold, choosing another
cell format, etc. And if you need to go to the cell below,
just use < ↓ > instead of < Enter >
--------------------------------------------------
For your point 2), I modified the VBA code of Sheet1 (LOTOVérif).
For the rest, I then modified the VBA code of Module1.
The VBA code of Module2 is the same as the previous one.
--------------------------------------------------
I didn’t quite understand your point 3), but maybe it will already be addressed
by the new changes I made. If so, great: it's resolved!
Otherwise:
For rows 21 to 100, are you sure you want to hide them?
If so: Rows("21:100").Hidden = True
But based on what do you want to do that? Just thinking about it, telepathically?
By the operation of the Excel Holy Spirit? No, no, I’m just joking, of course! 😉
But you understand that you need to provide more details.
Moreover, when these rows are hidden, you will probably want to unhide them.
If so: Rows("21:100").Hidden = False
But again, you’ll need to specify based on what! 😉
I am entirely willing to address your point 3) as soon as you provide me with all
the useful details (if not already done by my recent modifications, because I am not
sure that you really want to hide rows 21 to 100, but just change the position of the active cell).
--------------------------------------------------
Here is your new Excel 2007 file.
--------------------------------------------------
Best regards, albkan 😊