Auto expansion for cell when selecting

EE Pros,

I have a set of cells in a column that are the identical width and height.  The first cell is typically a short statement that is the actual case.  The second cell, just below, is a description.  

Here's what I'm trying to accomplish.  When the description cell is selected (perhaps double clicked on), I need it to expand to show the entire contents.  Then when clicking again, close back to the original size.

That's it.  Let me know if you need a sample.

Thank you in advance.

B.
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You can use the code in my Magical Floating ActiveX Control article to float a textbox on any cell you want. You could then set the font size to what you want and Autosize the text.

Let me know if you need help in doing it.
0
Bright01Author Commented:
Martin,

Greetings and thanks for picking this up.

I tried all three samples and even tried to code one myself.  Nojoy.  I must be doing something simple that is wrong....but I used the samples provided.

B.
D--Data-Data-Temp-Copy-of-Floating-Textb
D--Data-Data-Temp-Copy-of-Floating-Combo
D--Data-Data-Temp-Copy-of-Floating-Combo
0
Martin LissOlder than dirtCommented:
Actually you can just add an ActiveX label to the sheet, name it lblFloat and add this code to the sheet. This example assumes that there is a long description in cell A2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Set the range(s) where you want the label to appear
If Intersect(ActiveCell, Range("A2")) Is Nothing Then
    lblFloat.Visible = False
    Exit Sub
End If

Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If

With lblFloat
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .AutoSize = False
    .Caption = ActiveCell.Text
    .AutoSize = True
    .Activate
End With

errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Bright01Author Commented:
How do I add an ActiveX Label?  Is this under "Design Mode"?

B.
0
Martin LissOlder than dirtCommented:
...
0
Bright01Author Commented:
I am very sorry..... I can't get this.  I added a ActiveX Text Box, named it lblfloat, added the code you provided in the sheet, and tried putting both text and numbers into C2:F2 and nothing happens..........

B.
0
Martin LissOlder than dirtCommented:
Did you add a label or textbox? I should have been a label. I've attached a working workbook where A2 has the description. If you want to see the label you can run the ShowLabel sub that you'll find in Sheet1's code.

If you are still having trouble please attach your workbook.
Q-28636206.xlsm
0
Martin LissOlder than dirtCommented:
I just reread your last post and maybe I needed to be more specific about something. In the code you'll find a line that says

If Intersect(ActiveCell, Range("A2")) Is Nothing Then

A2 should be changed to whatever range(s) where you want the label to appear.
0
Bright01Author Commented:
Martin,  I attached all three of the referenced WBs in the article after trying them.  I'm doing something stupid I just don't know what.  Can you send me a sample of how it works right?

B.
0
Martin LissOlder than dirtCommented:
I attached all three of the referenced WBs in the article after trying them.
You don't need them and none of them regardless of what I initially said are on point.
Can you send me a sample of how it works right?
I did. Did you see Post ID: 40665456?
0
Bright01Author Commented:
I did.  When I go to A2 and change the text or double click...nothing happens.  I looked up for the macro and none appears.  The macros get triggered when A2 has a change....but when I change it nothing happens.

Let's pause for a second.  

What I'm trying to do is display and edit a full cell with wrapped text when you click or double click on that cell.  A full cell is a cell with several lines of text that when it is in its original configuration it has the same dimensions as all other cells.  Only when you click on the cell does it expand to show all the text.

Does that make sense?

B.
0
Bright01Author Commented:
I just tried to type several sentences into A2.  When I do it will not show.  When I double click on it, I can see the entire text.  Is this right?  Is that what the Macro is doing to display all text in cell A2?

B.
0
Martin LissOlder than dirtCommented:
I understood that what you wanted was to be able to click on a cell that contains a lot of text, and to have all the text show up. In the workbook I posted, A2 has the phrase "This is a long description of something" already in it. If you select that cell do you see it all?
0
Bright01Author Commented:
Yes.  But I see that same capability in every cell.  Is that right?

B.
0
Martin LissOlder than dirtCommented:
If you are talking about what you see in the formula line then yes. But for example type something long into A3 and then anything into B3. If you then select A3 you'll see it all in the formula line but you won't see it in the cell. If you select A2 on the other hand you'll see the full text in the cell. If that's not what you want then please explain what you need.
0
Martin LissOlder than dirtCommented:
Haven't heard from you so I hope my previous solution is not too far off of what you want, but I was thinking about the solution and I realized that it displays the cell's text in one long line and that may not be desirable if the text is lengthy. The attached workbook will produce multi-line output.

There are several changes in the Worksheet_SelectionChange code including:

1.

The sub now includes a constant called DESIRED_WIDTH with a value of 400. That's the width of the label and you can change it to whatever you want.

2.

The Intersect code in the sub determines the cells where the label will pop up. If you have a contiguous range like A2:A4 you would do If Intersect(ActiveCell, Range("A2:A4") Is Nothing Then, but if you have non-contiguous cells you can't do that as easily. In this workbook I wanted the label to pop up in cells A2, A5 and E9 so I added a range called rngActive which is the result, in this case, of Union-ing (merging) those 3 cells into one range and I refer to that range in  the Intersect code.Q-28636206a.xlsm
0
Bright01Author Commented:
Martin,

Sorry for the delay.  You have anticipated correctly!  I need the text in an expanded cell, like being able to see it as if you were looking at the formula bar (but the formula bar is hidden.  Also, this needs to happen in a column not just a single cell (so the reference to A2 cannot work.  Perhaps A:A would.  The autofit is a very nice touch.  I also see that I can edit it quickly in the box.  That's great.  So I think to take what you have done in A2 and adjust it to either expand in the cell itself or simply have it show up in a box next to the column would be ideal.

Is that enough to go on?

Thanks again.

B.
0
Martin LissOlder than dirtCommented:
OK, to change it so that selecting any cell in column A activates the code is easy. Let me verify that for example you selected A7, that you want to see the text in B7. Is that the case?
0
Martin LissOlder than dirtCommented:
Here's a workbook that includes changes for the assumptions that I made in post ID: 40666737.
Q-28636206b.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Martin, as always....thank you for your efforts.  This will do just fine!

B.
0
Martin LissOlder than dirtCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.