View original (French)

Active cell in top left corner of Excel

Solved
le Glauque Posted messages 60 Registration date   Status Member Last intervention   -  
le Glauque Posted messages 60 Registration date   Status Member Last intervention   -
Hello everyone,

I'm looking to create a series of macros (which I will then link to a button) that will take me to a different cell each time (depending on the button).
So far, no problem; I've created macros (with the recorder, as I don't know anything about Visual Basic), and they correctly select the relevant cell.
But the best would be for it to bring that cell to the top left of the screen...

I've obviously done some research online before bothering you... but I can't find any answers...
Let's just say the only one I found on the Microsoft site is as follows:

Symptoms
Is there a command that allows you to select any cell in a worksheet and position it in the top left corner of the active window?

Resolution
You just need to use the SELECT.GOAL function with its second argument set to TRUE.
EXAMPLE:
=SELECT.GOAL("L20C20";TRUE)
=RETURN()
This macro command selects the cell L20C20 and scrolls the active window to display cell L20C20 in the top left corner of the active worksheet.
If this second argument is FALSE or omitted, Microsoft Excel will scroll normally.

However, I can't understand how to implement it. A copy/paste into the VB editor (and replacing it with the cells I’m interested in) doesn’t seem to work (it has trouble with the semicolon before TRUE...).

I'm a bit lost...

Help!

Thank you in advance...

l.G.
Configuration: Windows XP Firefox 2.0.0.14

1 answer

  1.
    LePierre Posted messages 249 Status Member 338
     
    Hello

    while searching on the forums I found this that should suit you: 
    Sub test() 'to place cell S4 in the top left Row 4, column 19 Sheet = ActiveSheet.Name MakeTopLeft ThisWorkbook.Sheets(Sheet).Cells(4, 19) End Sub Sub MakeTopLeft(R As Range) R.Parent.Activate ActiveWindow.ScrollRow = R.Row ActiveWindow.ScrollColumn = R.Column End Sub

    see you later
    2
    1. le Glauque Posted messages 60 Registration date   Status Member Last intervention   1
       
      Thank you very much....

      So I copied all this into the pane (Sheet1).

      Therefore, the concerned cell is correctly positioned at the top left (and that's great!)...
      but it is not the "active cell", the latter remains the previously selected cell...

      Additionally, could you explain to me a bit what this involves
      (well, if it's not too complicated and if it's not too much trouble for you).

      I like to try to understand a little...

      Thank you in advance,

      l.G.
      0
    2. LePierre Posted messages 249 Status Member 338 > le Glauque Posted messages 60 Registration date   Status Member Last intervention  
       
      Hello

      here is the adapted code to place the active cell at the top left: 
      Sub test() LigIni = Selection.Row Colini = Selection.Column Onglet = ActiveSheet.Name 'to place cell S4 at the top left Line 4, column 19 'MakeTopLeft ThisWorkbook.Sheets(Onglet).Cells(4, 19) MakeTopLeft ThisWorkbook.Sheets(Onglet).Cells(LigIni, Colini) End Sub

      the ScrollRow property returns or sets the number of the row that appears at the top of the pane or window.

      you can also simply use these two lines of code (simpler than calling the "MakeTopLeft" procedure): 
      Sub test2() ActiveWindow.ScrollRow = Selection.Row ActiveWindow.ScrollColumn = Selection.Column End Sub


      it all depends on your usage.

      see you later
      0
    3. le Glauque Posted messages 60 Registration date   Status Member Last intervention   1 > LePierre Posted messages 249 Status Member
       
      Thank you very much,

      I copied your two little lines of code after the Macro I had previously recorded...
      and it works!
      I copied this into all the relevant macros... it's great! Thank you very much!

      Here's what it looks like
      (the goal is to retrieve the title of a Skills category, to have them directly visible):

      Sub Per()
      Sheets("Skills").Select
      Range("A81").Select
      ActiveWindow.ScrollRow = Selection.Row
      ActiveWindow.ScrollColumn = Selection.Column
      End Sub

      So, for this one, it retrieves the cell of the Perception skills title and positions it at the top left of the screen.
      I then have all the Perception skills below...

      It's perfect!

      However, it seemed very good, all the MakeTopLeft, etc.
      Was it a way to do the same thing through different means?

      Thank you again!

      l.G.
      0
    4. LePierre Posted messages 249 Status Member 338 > le Glauque Posted messages 60 Registration date   Status Member Last intervention  
       
      Hello

      The benefit of using a procedure like "MakeTopLeft" is that it keeps your main code lighter, making it easier to troubleshoot in case of future problems or modifications.
      But the most important thing is that it works correctly.
      Have a good day and see you later.
      0
    5. le Glauque Posted messages 60 Registration date   Status Member Last intervention   1 > LePierre Posted messages 249 Status Member
       


      So, if I follow you, I need to copy this formula:

      Sub MakeTopLeft(R As Range)
      R.Parent.Activate
      ActiveWindow.ScrollRow = R.Row
      ActiveWindow.ScrollColumn = R.Column
      End Sub

      into the Procedures of the doc?

      And then this one:

      Sub test()
      LigIni = Selection.Row
      Colini = Selection.Column
      Onglet = ActiveSheet.Name
      End Sub

      into the Functions?

      And so, afterwards, in the modules, if for example, I want a Macro to place cell A75 in the top left and make that cell active

      Sub test()
      MakeTopLeft ThisWorkbook.Sheets(Onglet).Cells(75, 1i)
      End Sub

      Am I on the right track?

      ThisWorkbook refers to the current document, right?
      Sheets, that's the Sheet. Do I need to specify the name of the sheet?

      If this sheet is named (for example "Compétences"), do I need to write:

      Sub test()
      MakeTopLeft ThisWorkbook.Sheets(Compétences).Cells(75, 1i)
      End Sub

      Sorry,
      these questions might be a bit basic...

      Thanks in advance...
      0