Trying to Replicate a Mysterious Format

[swanjun]

Each month, my supervisor has to update an Excel spreadsheet pertaining to different strategic initiatives. Our work unit has two that we're reporting out on. She didn't make this spreadsheet and neither did I.

Because we've been reporting out on these initiatives for a while, there's a lot of text in the cells she adds her comments to. We don't want to change the layout because we didn't make the spreadsheet.

The issue is that for the *second* of the two initiatives, if you double-click the cell, it will sort of... spool (unfurl?) out and show you all the contents of that cell.

If you double-click the comparable cell for the first initiative, it has the effect of hitting F2. In that, it puts your cursor at the end of the text in the field as though you were wanting to add some more.

We want both cells to do the spool thing.

I have tried just copying the formatting from one to the other. It doesn't work. What's weird is that it *does* work when you flatout copy the second one on top of the first one. Spools beautifully. However, when you go to put the correct text back in it... poof. No more spooling.

I've checked for conditional formatting. I've checked for macros. I don't see either.

Why is this spooling happening in the first place? How do I replicate it on the other cell while retaining the correct text in that cell?

Thanks in advance!

 

[Anthony47]

I think this is based on a "Worksheet_BeforeDoubleClick" event.
Rightclick on the tab with the name of the sheet and chose View code; this should open the vba environment and show the code that create that effect.

Bye

 

[swanjun]

Thanks for the suggestion!

Looking at the code all I see is this, which I think might be the typical default:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

 

[Anthony47]

So (betting you right-clicked on the correct sheet name tab) you don't have a "Worksheet_BeforeDoubleClick" macro

While in the vba editor, search for "ThisWorkbook" module in the VBAProject tree, and doubleclick on it to open it; check if you have a "Workbook_SheetBeforeDoubleClick", that trigger at any doubleclick in any worksheet.

 

[swanjun]

I appreciate your clear instructions!

I found the "ThisWorkbook" module and this is what I see:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub

 

[Georgiboy]

Reading this it seems like the cell that does the spooling has WrapText set to true and the one that does not set to false. It is he only way i can create something similar.

Try enabling WrapText for both cells and see if that has the desired effect.

 

[swanjun]

They both already do have Wrap Text enabled. When comparing the formatting, I can't see anything different between them.

I worry I'm not describing it correctly. It kind of.. unfurls like a scroll or something. Unfortunately, I don't think I am permitted to actually share the document itself.

 

[Georgiboy]

If there isn't any code in any of the tabs then I am not sure how it could be doing it. Is there any code at all in the Workbook Module/ Worksheet module/ Standard Modules?

 

[Anthony47]

Thus no macro bot in the Sheet and ThisWorkbook modules
Can you check if there is any "Class module" in your VBAProject?
Also, does the magic cell has an hyperlink?

Finally, I am not sure about what happens when you doubleclick on the (magic) cell; could you explain with more words and maybe get a screenshot of it?

 

[swanjun]

There is no Class module amongst the Microsoft Excel Objects for this VBAProject and it doesn't contain a hyperlink.

Unfortunately, I am not permitted to share a screenshot of it but I will try to describe it a little better.

The cell in question has a height of 408.75 and a width of 80.14. The row is so tall it takes up half my Excel window when it is the top row displaying.

When I double click on the magic cell, it unfurls like a scroll and now I can read everything that's in it. It now covers the whole right side of my Excel window.