+ Reply to Thread
Results 1 to 10 of 10

Display contents of cell in another only when clicked on

  1. #1
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Display contents of cell in another only when clicked on

    I have the spreadsheet that I have been working on and have got some text that is too big for the cell.
    I dont want to shrink to fit as it would be too small and I dont want to resize the worksheet.

    Ideally I want the contents of the cell in the formula bar, but as its locked and hidden you cant press F9 to display the formula result.
    Is there anyway I could get it so that when I clicked on, for in this example D8, it displays the cell value in A1 or pops the value up or anything similar - comments box maybe??

    This is not for printing, just generally using the workbook on a Laptop/PC

    rota7.xlsx

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Display contents of cell in another only when clicked on

    Try pasting the following code into the sheet2 tab in the VBA editor (Alt F11)


    Please Login or Register  to view this content.
    Martin

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Display contents of cell in another only when clicked on

    Before you protect the worksheet, insert a Comment into each cell into which you want the "big" display. Copy the value of the cell into this Comment. Make sure the Comment box is big enough to display the full text. Then protect the sheet.

    Mouse-over D8 in my attachment
    Attached Files Attached Files
    Gary's Student

  4. #4
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Re: Display contents of cell in another only when clicked on

    Martin, that is brilliant - is there any way I can get this code to work on a range of worksheets or will i just need to have this code on every Worksheet for it to work!?

  5. #5
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Re: Display contents of cell in another only when clicked on

    And also for it not to pop up if the cell contains certain text eg D/O, HOL etc

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Display contents of cell in another only when clicked on

    It should work if you paste it into the ThisWorkbook tab and use the Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) event instead.

  7. #7
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Re: Display contents of cell in another only when clicked on

    Hi, thanks for you support - Its kind of working, but its failing on particular cells in the spreadsheet - the book I'm working with displays the number "150" but I've formatted it so its not visible - see attached file - When you click on this its coming up with an error.

    Also, i only want the code to run on certain cells, B6:N12, B15:N21 and only on certain sheets (which are defined as 18-37 in my book) and for it not to comment if the cell displays HOL or D/O as you can already see this. I know i'm asking a lot from with many variables but if its possible i'd appreciate the support.

    rota test8.xls

    Thanks John

  8. #8
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Display contents of cell in another only when clicked on

    Please try the amendment below

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Re: Display contents of cell in another only when clicked on

    Excellent stuff - I've been trying to redo:

    Please Login or Register  to view this content.
    to make it look at the Code name of the sheet (in the examples case Sheet2 and Sheet3) as opposed to the given name of the sheet as these tabs are going to be named by the name of the staff member so will be variable depending on each workbook.

  10. #10
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Re: Display contents of cell in another only when clicked on

    Hi, I sorted it - I used

    Please Login or Register  to view this content.

+ 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