Linking Object Size to Cell Value

[sengelen]

hi Leith

Could you program an example where the object size (arrow) is linked to a cell value?

Many thanks

[MickG]

Hi, Try this
Paste this code into a Command button or an MT macro.
Select the cell with the value, run code
You will need to alter the value 45 in the code and the factor 6 to suit.
See how you get on.

Please Login or Register  to view this content.

Regards
Mick

[sengelen]

Hi,

thanks for the quick reply
Could you send an example please?
Via the site or when not possible to se@lorstem.no

I seem not succeeding in doing it

It says it has problems with the Selection (compiling error)

Apologize but i am no expert in Excel programming

Thanks
Steve

[MickG]

Hi, I've copied this code back from the thread and it works fine.
Try this procedure to ensure your basic macro is working
Open a new Worksheet.
Click "Alt + F11. Viable Basic Editor Appears
On the Tool bar there is a small square blue button. This is the Reset button, Click it.
Close VB Dialog Box
Click "Tools", "Macro", " Record Macro" -- Macro Dialog box appears.
Enter the letter "a" in the box next to CTRL, Click "OK"-- Macro Recording Button appears on Sheet.
Type in Cell "A1" "Hello", Click the Cell below.
Click the Small Blue square in the Macro Recording Button.--- Button disappears.
Delete the word "Hello" from "A1"
Click "Ctrl+a" , the word "Hello" should appear in "A1".
If this works, Click "Alt + F8", Macro list box Appears, click you Macro Number,
Select "Step Into". -- VB Editor appears with you Macro code in it.
Delete the bit of code ref : "Hello"
Paste the New code (below) into the VB window, below all the green writing and above the Words "End Sub"..
Click the blue Reset button on the VB Toolbar.
Close VB Editor
Enter a number in a cell.
Select another cell.
Reselect the cell with the number in it.
Click "Ctrl+a", -- An arrow should appear along the top of the cell proportional in length to the cell value. (As previous post adjust code values 46 & 6 to suit)
Don't click Ctrl+a again, while in the same cell, else you will place another Arrow on top of the first.
If you get this working call back, I've got another bit of code that may compliment this Code.

Please Login or Register  to view this content.

I hope this is not too daunting
Regards Mick

[sengelen]

Hi,

Many thank it works...I got it almost except for the activation via ctrl a

Breg
Steve

[sengelen]

Hi,

This is for the size of the arrow but the main idea is actually for the tickness of the arrow...

Thanks
Steve

[MickG]

Hello again. Relace all the previous code with the Code below.
This new code will alter the Arrow thickness.
In the line that starts "Activesheet" you will see a number 20 this is the arrow length, change number to suit you.
In the line below that you will see a Number 0.1 this is a factor for the line thickness, change to suit
Let me know if it works

Please Login or Register  to view this content.

Regard Mick

[sengelen]

Mick

Many thanks
It works..

Only one comment regarding its operational use:

- I want to be able to update the cell value and so the arrow size, thickness directly in Excel.
This is not happening now.

The whole idea is to have a map with arrows showing relevant import trades by means of arrows. As this change quite often, it would be nice to just change the cell value, hence, chaning the arrow accordingly.

Many thanks
Steve

[MickG]

Hi,
What you are after is the extra code as mentioned in an earlier post.
Assign the code below to MT Macro or Commanbutton.
The code below will alter the thickness, on "Arrows" previously entered by the first code..
To do this change the values in the "Arrowhead Cells" to a larger two digit number and run the new code.
In this new code you will see, another 0.1 factor in the line "s.Line.Weight" .
This is a thickness factor in relation to the cell number, amend this number and the line "s.Width = 40" (Line Length) in due course to suit you.

Please Login or Register  to view this content.

Good Luck
Any problems call again
Mick

[sengelen]

Many thanks,

The only thing is this is not such a workable solution ;o

The task at hand is that I have a file with 20 values. The idea is to change the thickness of the arrow each time the value in the corresponding cell is chaned

Breg
Steve

[MickG]

Hi, If I understand you correctly you, the code is all right but you would like it to run automatically when you change a value. If this is so, you need to run the code in an "Event Handling " procedure.
To achieve this do the following:-
Open the "Arrow" Worksheet
Open VB Editor Alt +F11.
Find the code in Question, and copy it.
At the top of the VB Window, on the left hand side you will see a drop down box.
Select from this "Worksheet"
On the right hand side there is another Dropdown box.
Select "Selection Change".
A new code layout will appear Entitled.:-
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
Paste you code into the window
Click the (Blue) "Reset Button" on the VB Toolbar.
Close VB Editor
Alter an "arrowed" cell Number, Thickness should change Automatically
Hope this is what you want
Regards Mick