rexwrx


Hi all,

Ok, i'm not an expert at all with programming, so please bear with me:

This is a dashboard design.

In my spreadsheet I have (example) 4 drawings (shapes) -> they are all arrows.

I have a column called STATUS which basically has list of GREEN, AMBER, RED, which I can select. Whenever I select the GREEN/AMBER/RED, the drawing change from UP, DOWN or HORIZONTAL.

Each arrows will have its own status.

Example:
CEll - STATUS - ARROW
Cell $C$5 - RED - DOWN
Cell $C$16 - AMBER - HORIZONTAL


so I have this as the 'main' in my Sheet1:

Private Sub Worksheet_Change(ByVal Target1 As Excel.Range)

On Error GoTo WkSheet_Change_Err
Dim trend As String

'overall
If (Target1.Address = "$C$5") Then

Call changeTrend(Target1)

End If

'assets & liabilities
If (Target1.Address = "$C$16") Then

Call changeTrendAL(Target1)

End If

......and so on

Each of the CALL will have this:

Sub changeTrend(ByVal Target1 As Excel.Range)

trend = Target1.Value

Select Case trend

Case "G"
ActiveSheet.shapes("AutoShape 10").Select
Selection.ShapeRange.AutoShapeType = msoShapeUpArrow
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11 'green
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid

Case "R"
ActiveSheet.shapes("AutoShape 10").Select
Selection.ShapeRange.AutoShapeType = msoShapeDownArrow
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 'red
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid

Case "A"
'ActiveSheet.shapes(myshape).Select
ActiveSheet.shapes("AutoShape 10").Select
Selection.ShapeRange.AutoShapeType = msoShapeLeftRightArrow
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52 'orange
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
End Select

End Sub
.....

and so on.

this is quite cumbersome and what I need is to PASS the name of the SHAPES as parameter into the MODULE - just like passing the 'TARGET'

Call changeTrend(Target1) ->.

QS:

HOW do I pass the name of the shapes as parameter

Thanks



Re: Pass Shapes property names into module - Plz help

JoshZ


I'm not sure I totally understand, but if you're asking how you could pass the name as an argument from Worksheet_Change to changeTrend, then you can do it by simply adding the string variable to the call:

Code Snippet
Sub changeTrend(ByVal Target1 As Range, ByVal sShapeName as String)


then you would call it with changeTrend Target, "nameofshape"

you could also add some more arguments for the other object parameters like this:

Code Snippet
Sub changeTrend(ByVal trend As String, ByVal sShapeName as String, lAutoShapeType as long, lSchemeColor as long)
'// note : I changed Target1 to string, since you don't seem to need the actual range for anything, in fact you could drop the string "trend" totally since you're giving all the parameters to the sub :)

then you wouldn't need to have that select...case there, you'd just call the sub:

Code Snippet
changeTrend Target.value, "nameofshape", msoShapeLeftRightArrow, 52

and this is how the sub would look like

Code Snippet

with ActiveSheet.shapes(sShapeName)

.AutoShapeType = lAutoShapeType

.Fill.ForeColor.SchemeColor = lSchemeColor

.Fill.Visible = msoTrue

.Fill.Solid

end with

'// note: you don't need to select the shape in order to edit it's properties, that's too much memory-consuming :)

hope this helps you forward with your studies in programming!






Re: Pass Shapes property names into module - Plz help

rexwrx

Thank you sir!

yes defin. help much!

i'll try and see if this works. Wink






Re: Pass Shapes property names into module - Plz help

rexwrx

actually, will it work by not having the case statement

that's because there will be few sets of arrows, each set containing UP ARROW, DOWN ARROW, and HORIZONTAL ARROW.

and these each set of the arrow is DRIVEN by the value of the CELLS.

So say e.g. Cell "$C$5" will drive ONE SET of ARROWS (up, down and horiz.)

cell $C$16" will drive another set. and so on...

if there's no case statement to pick out the cell range, then how can I get the CELL to DRIVE the arrows

many thanks!

JoshZ wrote:

then you would call it with changeTrend Target, "nameofshape"

you could also add some more arguments for the other object parameters like this:

Code Snippet
Sub changeTrend(ByVal trend As String, ByVal sShapeName as String, lAutoShapeType as long, lSchemeColor as long)
'// note : I changed Target1 to string, since you don't seem to need the actual range for anything, in fact you could drop the string "trend" totally since you're giving all the parameters to the sub :)

then you wouldn't need to have that select...case there, you'd just call the sub:

Code Snippet
changeTrend Target.value, "nameofshape", msoShapeLeftRightArrow, 52

and this is how the sub would look like

Code Snippet

with ActiveSheet.shapes(sShapeName)

.AutoShapeType = lAutoShapeType

.Fill.ForeColor.SchemeColor = lSchemeColor

.Fill.Visible = msoTrue

.Fill.Solid

end with

'// note: you don't need to select the shape in order to edit it's properties, that's too much memory-consuming :)

hope this helps you forward with your studies in programming!





Re: Pass Shapes property names into module - Plz help

JoshZ

Oops, sorry I wasn't being very clear, was I Smile You can still have the select in the Worksheet_Change -event if you need it, so you don't need to have it in the changeTrend sub. That means this:

in Worksheet_Change:

Code Snippet

dim trend as string

dim lShape as long

dim lColor as long

dim sShapeName as string

if Target.Address = "$C$5" then

sShapeName = "NameOfShape1"

elseif Target.Address = "$C$16" then

sShapeName = "NameOfShape2"

'etc.

end if

trend = target.value

select case trend

case "case1"

'// set shape properties for case 1

lShape = msoShapeUpArrow '// the shape of the shape :)

lColor = 11 '// the color

case "case2"

'// set shape properties for case 1

lShape = msoShapeDownArrow'// the shape of the shape :)

lColor = 10 '// the color

'// and so on...

end select

'// then you call the sub...

changeTrend trend, sShapeName, lshape, lcolor

Hope this clears things up a bit Smile

edit. I may have understood you wrong in the beginning, but now it seems you first need to know which shape to change and then to what kind of arrow... changed the code to do that with the if-statement. You could also try naming the shape by the cell that is changing it ie. cell "$C$5" would have a shape named C5 and then you could set sShapeName to Target.Address(False,False)...