Enlarge and view data which is not normally seen 1

[nimo]

Hi All..

Suppose I have a Excel Sheet where I have some Rectangles. Inside those rectangles I have some texts. But normally those texts are not visible because the rectangles are small in shape. To view the texts, I have to click, then enlarge the rectangles.

My question is, how can I write a Macro so that whenever I put the mouse over any Rectangle, the rectangle enlarges automatically so that all the text is visible?

Thanx.

Nimo

 

[aMember]

I'm almost sure this isn't possible. You'd need something to trigger an event...even if it was your own macro.

You would have to at least click on the rectangle.

Just a question. If the rectangles are never seen until you move the mouse over them, could you insert an Excel comment where the rectangles are. That seems to be essentially what you want.

 

[nimo]

Dear aMember:

Yes, u r right. I have to trigger a event when I click the rectangles or drag mouse over it. So, can you give an idea how I can trigger an event and then see all the texts inside the rectangle automatically when I click a rectangle or drag mouse over it?

Answering your 2nd question, the rectangles are always visible and also some texts of the rectangles are visible. But problem is there are more texts in them and I cannot see all of the texts. To see all the texts, I have to click a rectangle and then enlarge it.

Also, I can write inside the rectangles or any other cell in the Excel sheet.

Nimo

 

[VulcanJedi]

Well I got this far, if you rt click on the rectangle you can assign a particular macro to it, I haven't figured out how to do this in the brief time I played with it but then:

Sub Rectangle1_Click()
Dim rectangle1 As Object


MsgBox (ActiveSheet.Shapes(1).AlternativeText)
End Sub Tranpkp
************************************
- Let me know if this helped/worked!

 

[aMember]

right click each rectangle and assign a macro a copy.version of the following macro to it.
Sub Rectangle1_click()
Dim theHeight As Integer
Dim myRectangle As Shape
Set myRectangle = ActiveSheet.Shapes("Rectangle 2&quot
theHeight = myRectangle.Height
Select Case theHeight
Case 20
myRectangle.Height = 40
myRectangle.Width = 40
Case 40
myRectangle.Height = 80
myRectangle.Width = 80
Case 80
myRectangle.Height = 160
myRectangle.Width = 160
Case 160
myRectangle.Height = 20
myRectangle.Width = 20
End Select


End Sub

 

[nimo]

Hi Tranpkp

Thanx a lot.
This works great from the VB IDE, I mean if I run the Macro from VB, I can get the Text of a rectangles[depending on the value of i on Shapes(i)]. Also it works fine if I assign this macro to a rectangle by right clicking.But if I have 30 Rectangles like this, then it is gonna be a hard work
SO, how can I make a Macro so that whenever I click on any of these rectangles, an event generates and gives me only the info text of that rectangle?

Nimo

 

[nimo]

Dear aMemeber:

I tried your code........but it did not work in my PC...did it work on yr PC?

Nimo

 

[aMember]

Yes, it did work for me and that was a straight cut and paste from the module.

Did you verify you had the correct rectangle name?
What version of Excel are you using? I'm using '97.

 

[nimo]

Hi aMember

I am using '2000.....so may b there is a problem for that......anyway, if I have 30-40 rectangles, then it is not possible to assign a macro to all the shapes....how I can I do it dynamically so that once the Macro is running, all the shapes are assigned the macro and when I click any shape, it shows it'S text?

And I am also confused with the code u have given here...what does it do in your PC and how it is working?

Nimo


Nimo

 

[VulcanJedi]

Nimo I somewhat understand Amembers code. At least what it trys/should be doing. IT's the approach I think you are asking, but its an interesting way of identifying the rectangles. His code is identifing / distinquishing all the rectangles by height. You'd probably just use an index. Unfortunately if you read his post all he's saying I think is that you would have to based on this or my code assign a macro to each one. I haven't figured out how to return the current shape when clicked upon. Is it that important the file has these rectangles is it from Visio or something, I have been working on it, but not too familiar w/ shapes. Tranpkp
************************************
- Let me know if this helped/worked!

 

[VulcanJedi]

Nimo I've tried all day.... I can not determine a way to identify the current rectangle and access that shape. Thus the only way creatively around this would be to add code based on the layout of your sheet assuming it remains relatively static and perhaps using that. For instance if you have a linear example from left to right:

Private sub Worksheet_beforedoubleclick()

msgbox(activesheet.shapes(activecell.column).alternativetext)
ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
end sub

This will display the text from the box immediately above the current cell? and also since based on double click cell move the active cell to the next adjacent cell since double click enters edit mode. Tranpkp
************************************
- Let me know if this helped/worked!

 

[VulcanJedi]

Nimo,

I think I GOT IT!!

It's a little quirkly but I really hopes it help this has been nagging at me all day! (you should give me a star for effort )
make a button or put this code elsewhere. The only way I could isolate and identify a rectangle was the usign the feature ZORDER. This is basically the order of the shapes colleciton in your book. Basically rt. click on any rectangle and click 'order -> Bring to Front'
Then click the command button. Done.
Fall down, go boom. Hope this is satisfactory.


Private Sub CommandButton1_Click()

For x = 1 To ActiveSheet.shapes.Count
If ActiveSheet.shapes(x).ZOrderPosition = ActiveSheet.shapes.Count Then
MsgBox (ActiveSheet.shapes(x).AlternativeText)
ActiveSheet.shapes(x).ZOrder (SendToBack)
End If

Next x
End Sub Tranpkp
************************************
- Let me know if this helped/worked!

 

[aMember]

Just to clarify what the code was doing...I was jsut playing. Each time you click on it, it changes sizes until it gets to the bigger size. Sorry. Should have commented or left it out and just answered the question. Playing and became a victim of cut and paste.

This definitely works on 2000. Assign each rectangle to this macro.

Sub Rectangle1_click()
Dim theHeight As Integer
Dim myRectangle As Shape
Set myRectangle = ActiveSheet.Shapes(Application.Caller)

myRectangle.Height = someNewHeight

End Sub

 

[nimo]

Dear Tranpkp and aMemeber:

Thanks a lot for all the time and effort you have given on
the problem. I am really grateful to you both.
Currently I am in a different place and cannot test the solutions you have provided. I will try them tomorrow, hopefully.
Another question, can I use these codes with VB Script also?

Keep on the good work and keep smiling.

Nimo