+ Reply to Thread
Results 1 to 19 of 19

Link button to cell

  1. #1
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Link button to cell

    I have a simple macro which works well - created using macro recorder as I am not a VBA code generator!!! However, it uses relative references because I want to add rows and copy formulae into a block of cells that will itself appear multiple times as a result of another copy and paste macro. So the button would appear in each block.

    The problem is that everyting is based on the assumption that the correct cell is selected before the macro runs - and if it isn't chaos ensues! If I could locate a button in a cell rather than over a cell, so that clicking the button automatically selected the approriate cell before the macro runs all would be well. Is this possible?

    I have attached a file to show what I am trying to do - I am afraid that I am very much self taught and this may be far from the optimimum approach but if only I could protect against the macro being run from the wrong cells I would be happy!

    Thanks,

    RDS
    Attached Files Attached Files
    Last edited by RDS; 02-19-2012 at 10:46 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Link button to cell

    You can use the TOPLEFTCELL property of the button shape to determine it's position and then select a cell before the reset of your code runs.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Brilliant! Works like a charm!

    Very many thanks!

    R

  4. #4
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Hmmm, evidently I am a slow learner - the solution above is great and I thought I would be able to solve my next challenge unaided.... but I can't make head nor tail of the code

    What I am trying to do now is insert a button at the top of one column which will find the last numbered block of information in the sheet and insert a new, blank block ready for the next set of data under it. Manual copy and paste works fine, including the insert line button I already have. Not being into VBA I have been trying to do it with Macro Recorder and just tying myself in knots.Development Sheet2.xlsm

    File attached,

    Many thanks,

    RDS

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Link button to cell

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    That's great - I really will have to work out this VBA stuff - just one issue - I have lost the formulae in columns O,P and Q but, oddly not in C or F for instance...

    Many thanks,

    RDS

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Link button to cell

    That's because this line clears that area. I though it was static values.
    Simply remove or adjust so copied cells that need clearing are.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Ah, they've gone in R too!

    Another slight snag (sorry to be picky, it seems very cheeky!) is that this copies the last block including any contents - which is why I wanted to hide the last block - so no-one adds data to it and it is always "clean" and ,most importantly, without extra inserted lines, changing its size before copying!

  9. #9
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    we've crossed! I'll try that! It might take me a while!


  10. #10
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    This excellent, I am learning! I have modified the line so that the required cells are not cleared and that fixes that issue nicely. How do I part clear other coumns? These columns contain a mixture of data that needs clearing and headings that don't do I use the colum reference and row number relative to the top left cell of the block?

    Also, I tried hiding the bottom block and everything still worked but everthing was unhidden at the end, so starting with a clear but hidden block at the end, hitting the button leaves me with 2 claer blocks exposed and no hidden block - so people will potentially add lines and give me the "wrong" sized block after inserting a new one. Obviously, if I could resolve this issue then the clearing cells problem goes away anyway!

    Many thanks,

    RDS

  11. #11
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Oops, forgot file
    Attached Files Attached Files

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Link button to cell

    Why not store the cells to be copied on a separate sheet, like a mini template.
    The you only need to locate the end of the table and then copy over to append.

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Link button to cell

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Yes, I see - put the template on the "Scratch" tab in this workbook. I did try that in one of my earlier attempts but using "Record Macro" I couldn't make it work. It's a very good solution to the problem however. What I can't see from the macro is how the range to be copied is identified - I am sorry to be dim but I could see easily what you meant in terms of clearing columns G:AB etc but the earlier lines in the macro I can't decipher in terms of idnetifying blocks of cell....

    Thanks,

    RDS

  15. #15
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    OH, you even sent the sheet! Damn! You are good to me!

  16. #16
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Fantastic! Absolutely does the job.

    Very many thanks. The trouble with this is that it just makes you want to do more! I now have a hyperlink ambition but I will start a separate thread for that. This one is compehensively solved.

    As the raiser I assume I am supposed to mark the thread solved - but can't see how.

    Rgds,

    RDS

  17. #17
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Well, I thought it was solved... It seemed to work but now the second macro is perfect - a new block appears and life is good - but the first one - Insert a row has stopped working and inserts a bunch of very deep rows in the wrong place.... and it all looked so good. Initially I think it was all working but save and close and it's broke... At least I think it was all working, you begin to doubt your own sanity at times!

    RDS
    Attached Files Attached Files

  18. #18
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Link button to cell

    First it would appear some of the controls are assigned to a macro in another workbook.
    The Create Task button is directly over the Insert Task button, not sure what the difference is.

    The button overhangs on the left edge so the TopLeftCell is column D rather than E

  19. #19
    Registered User
    Join Date
    02-18-2012
    Location
    Brighton, UK
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Link button to cell

    Sorted! Very many thanks! Both for the help and your patience!

    RDS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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