+ Reply to Thread
Results 1 to 32 of 32

Looking for a solution to automatically generate two crossing lines from the cell I'm at

  1. #1
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Looking for a solution to automatically generate two crossing lines from the cell I'm at

    Hi,


    I don't know if this is considered to similar to another thread I have.
    But I am looking for a solution that will underline and right line with an intersection from the cell I am standing.

    Underline + right line from where I am like a cross that's following the marked cell.
    Since the sheet is frozen both ways (orignally), that's why it's important that the lines go all the way from the beginning of the sheet and from the top, not just around the cell.
    This is for me to better navigate when I move out from a part of the sheet that is frozen to work with numbers far on the right side.
    Attached Files Attached Files

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    This is the kind of thing that will do it however' the borders will need to be removed when the selection has changed - in the example below i am removing the borders from the entire worksheet but you may need to restrict that to a range. It will be the same for the lines it is drawing, where i have locked them to start at row 1 and column 1 you may need to amend it to suit your range.

    Please Login or Register  to view this content.
    If things don't change they stay the same

  3. #3
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Thank you!

    I will test it out and let you know.
    Have a nice day!

  4. #4
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    This is the kind of thing that will do it however' the borders will need to be removed when the selection has changed - in the example below i am removing the borders from the entire worksheet but you may need to restrict that to a range. It will be the same for the lines it is drawing, where i have locked them to start at row 1 and column 1 you may need to amend it to suit your range.

    Please Login or Register  to view this content.
    This is working, but with one big problem.
    The script deletes all the lines I have in the spreadsheet.

    I am implementing this code to a sheet with tables and lines and all of them disappeared when I saved the workbook after pasting the code.

    Is there any other way around this?

  5. #5
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    I explained some of that in my last post after the word 'however'

    You should always be testing code in a dummy workbook, never your final until fully tested.

    So instead of:
    Please Login or Register  to view this content.
    You may need:
    Please Login or Register  to view this content.
    With this bit:
    Please Login or Register  to view this content.
    The 1 is the row where your upright line will start

    And with this bit:
    Please Login or Register  to view this content.
    The 1 is the column where you horizontal line will start

    If you have lines all over the place where you want these new lines to be then you may have to look for another option.

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

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    try
    1) To ThisWorkbook code module
    Please Login or Register  to view this content.
    2) To Ark1 sheet code module
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by jindon View Post
    try
    1) To ThisWorkbook code module
    Please Login or Register  to view this content.
    2) To Ark1 sheet code module
    Please Login or Register  to view this content.
    Hi,

    Thank you for this.

    After downloading and opening the workbook, nothing happens ;/

    I have tried it on another PC and another Excel and it works.

    Is there something wrong with my computer, Excel or any additional program why some of the VBA's doesn't work?

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

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Why don't you upload a workbook that is not working?

  9. #9
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by jindon View Post
    Why don't you upload a workbook that is not working?
    The workbook you attached yesterday doesn't work for me.
    The code doesn't run. It won't show any lines.

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

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Then it should be something wrong with your PC...

  11. #11
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by jindon View Post
    Then it should be something wrong with your PC...
    Yeah, probably.

    But why does the code from CheeseSandwich run, except the first one erased my borders.
    Is there a different way to set up the codes?

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

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    No, it should work as it is.

    Just tested 365 & 2010, and both working as expected, so it is something to do with your PC/Excel or other reason that I can't even guess.

  13. #13
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    I explained some of that in my last post after the word 'however'

    You should always be testing code in a dummy workbook, never your final until fully tested.

    So instead of:
    Please Login or Register  to view this content.
    You may need:
    Please Login or Register  to view this content.
    With this bit:
    Please Login or Register  to view this content.
    The 1 is the row where your upright line will start

    And with this bit:
    Please Login or Register  to view this content.
    The 1 is the column where you horizontal line will start

    If you have lines all over the place where you want these new lines to be then you may have to look for another option.

    Hi,

    Thank you.
    I have tested to change the things you said but I get some kind of error.
    Attached Images Attached Images
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by jindon View Post
    No, it should work as it is.

    Just tested 365 & 2010, and both working as expected, so it is something to do with your PC/Excel or other reason that I can't even guess.
    I have opened another thread where a moderator says my Excel version is beta.
    But after installing Office 365, this is the only version that comes.

    I could try to reinstall office and don't update anything that has something to do with office from Windows update.

    It's very strange that some codes work and some doesn't.

  15. #15
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by Tirrazo View Post
    Hi,

    Thank you.
    I have tested to change the things you said but I get some kind of error.
    I have modified the below so all you should need to do is change the range at the top to be the range where you want the borders to be cleared and then remade

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    I have modified the below so all you should need to do is change the range at the top to be the range where you want the borders to be cleared and then remade

    Please Login or Register  to view this content.
    Thank you.
    This is working but it will not work for me.

    My original book have many lines and tables and they will be destroyed :o

  17. #17
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    To be honest it is more pain than it is worth IMO

  18. #18
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    To be honest it is more pain than it is worth IMO
    Do you have any other idea that might work?

    I am just looking for a better way to see where I am in the spreadsheet referring to a frozen part on the left.

  19. #19
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    You could try using named shapes and move them around depending on selection, you can also toggle the visiblity on/ off depending on the current selection.
    The below requires you to have on the sheet:
    Down arrow named: 'Down Arrow 1'
    Right arrow named: 'Right Arrow 1'
    The size of them does not matter as the code will adjust them, you can colour them as you wish.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by CheeseSandwich; 05-11-2022 at 05:49 AM.

  20. #20
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    You could try using named shapes and move them around depending on selection, you can also toggle the visiblity on/ off depending on the current selection.
    The below requires you to have on the sheet:
    Down arrow named: 'Down Arrow 1'
    Right arrow named: 'Right Arrow 1'
    The size of them does not matter as the code will adjust them, you can colour them as you wish.

    Please Login or Register  to view this content.
    Thank you for this.

    I immediately got a debug, but after I got out from the debug box it worked until I exited the sheet.
    Attached Images Attached Images

  21. #21
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    How do you mean 'Until i exited the sheet'

    Might be worth wrapping it with on error resume next and see how it works then - the error could have been where it is setting something to hidden that is already hidden.

    Try:
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    How do you mean 'Until i exited the sheet'

    Might be worth wrapping it with on error resume next and see how it works then - the error could have been where it is setting something to hidden that is already hidden.

    Try:
    Please Login or Register  to view this content.
    Thank you!

    Will test and come back to you

  23. #23
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    How do you mean 'Until i exited the sheet'

    Might be worth wrapping it with on error resume next and see how it works then - the error could have been where it is setting something to hidden that is already hidden.

    Try:
    Please Login or Register  to view this content.
    Hi again,

    I am struggling to copy this VBA into my existing sheets.
    After downloading the workbook from you, that is working. I can also drag the sheet from you workbook over to mine and it will work.
    But I need this code itself to be pasted to work for a sheet that I have created, but that doesn't work.
    It doesn't work if I have a completely new sheet either.

    You can see I am trying to copy this function to a brand new sheet ( Ark1).

    What am I missing?
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Tirrazo; 05-13-2022 at 05:09 AM.

  24. #24
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    You must first add two arrow shapes to the worksheet.

    Add shape: Arrow Right (Name this shape: Right Arrow 1)
    Add shape: Arrow Down (Name this shape: Down Arrow 1)

  25. #25
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    You must first add two arrow shapes to the worksheet.

    Add shape: Arrow Right (Name this shape: Right Arrow 1)
    Add shape: Arrow Down (Name this shape: Down Arrow 1)

    Is this correct?
    How do you toggle the visibility?
    Attached Images Attached Images
    Last edited by Tirrazo; 05-13-2022 at 09:01 AM.

  26. #26
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    They are the correct shapes, the visibility is handled in the code.

    When I say name them I mean name them using the 'Name Box' that is in the ribbon near cell(A1).
    When you select one of the shapes there will be a name in this name box, this is where you need to name the shape.
    The name of the shape needs to be as the text in arrows in your screenshot above.

  27. #27
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    They are the correct shapes, the visibility is handled in the code.

    When I say name them I mean name them using the 'Name Box' that is in the ribbon near cell(A1).
    When you select one of the shapes there will be a name in this name box, this is where you need to name the shape.
    The name of the shape needs to be as the text in arrows in your screenshot above.
    I will try it out in my original workbook. I think I got it to work now

  28. #28
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    They are the correct shapes, the visibility is handled in the code.

    When I say name them I mean name them using the 'Name Box' that is in the ribbon near cell(A1).
    When you select one of the shapes there will be a name in this name box, this is where you need to name the shape.
    The name of the shape needs to be as the text in arrows in your screenshot above.
    So I made it work now and I feel it is working better than highlighting the rows. Thats probably more resource demanding.
    In my original sheet it's still lagging a bit if I move to fast.

    How can I change the VBA to just create the horizontal line and remove the vertical?
    If this would help it to respond faster.

    Is it possible to move the intersection horizontally 1 cell to the right?
    I am not able to grab the right lower cross on the cell to drag it down - it's just grabbing hold of the blue line.

    It could also help if the horizontal blue line was above the selected cell.
    Attached Images Attached Images
    Last edited by Tirrazo; 05-14-2022 at 07:10 AM.

  29. #29
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Anyone has some ideas here?:D

  30. #30
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    This will point to tp left of activecell:
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Quote Originally Posted by CheeseSandwich View Post
    This will point to tp left of activecell:
    Please Login or Register  to view this content.
    Thank you! This is working very good!

    In smaller sheets there is no problem with the lag and I can have both lines.
    Would it be possible for the lines to intersect bottom left of the marked cell?

  32. #32
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    179

    Re: Looking for a solution to automatically generate two crossing lines from the cell I'm

    Is it possible to change these lines and intersections myself to this?
    Attached Images Attached Images
    Last edited by Tirrazo; 05-19-2022 at 11:32 AM.

+ 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. [SOLVED] All crossing should be filtered in different sheet crossing type
    By rajatds31 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2020, 08:37 AM
  2. [SOLVED] Coloring above and below crossing lines.
    By sungen99 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-12-2019, 03:58 PM
  3. Formulas for calculating for two sma lines crossing point value
    By DavidRoger in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2015, 05:13 AM
  4. I want to generate automatically the number of lines i indicate in the fill in form
    By christiano.Braem in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-21-2014, 10:56 AM
  5. one cell to generate multiple lines on a second sheet
    By realmseeker in forum Excel General
    Replies: 1
    Last Post: 04-03-2013, 06:39 PM
  6. Automatically Generate Lines
    By rikapple in forum Excel General
    Replies: 1
    Last Post: 11-19-2007, 03:35 PM
  7. Trying to generate a solution
    By Russ in forum Excel General
    Replies: 2
    Last Post: 05-18-2006, 06:40 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