A1 contains a number that changes randomly from 1 to 100
a2 reads =concatenate("Macro",A1)

Therefore A2 equals Macro1 when A1 is 1

I have written 100 macros for each of these values as I could not figure out
another way to do this. (still a little new to VB)

How do write a macro so that when it runs it refers to the cel A2 to see
which macro it is supposed to run

Thanks in advance

C

Dim myMacCell as range
Set myMacCell = worksheets("sheet9999").range("a2")

'some testing to see if it's ok should go here
if it_is_ok then
application.run mymaccell.value
else
beep
end if



Cathy wrote:

A1 contains a number that changes randomly from 1 to 100
a2 reads =concatenate("Macro",A1)

Therefore A2 equals Macro1 when A1 is 1

I have written 100 macros for each of these values as I could not figure out
another way to do this. (still a little new to VB)

How do write a macro so that when it runs it refers to the cel A2 to see
which macro it is supposed to run

Thanks in advance

C

--

Dave Peterson

Sub whchMacro()
Dim Mac2run
Mac2run = "macro" & Range("a1")
Application.Run [Mac2run]
End Sub

I was wondering about the 100 macros, how are they all differing?

Scott

On Fri, 22 Feb 2008 21:21:24 -0000, "Cathy" wrote:

A1 contains a number that changes randomly from 1 to 100
a2 reads =concatenate("Macro",A1)

Therefore A2 equals Macro1 when A1 is 1

I have written 100 macros for each of these values as I could not figure out
another way to do this. (still a little new to VB)

How do write a macro so that when it runs it refers to the cel A2 to see
which macro it is supposed to run

Thanks in advance

C

Thank you for all the help so far.

I was wondering about the 100 macros, how are they all differing?

I have probably done this wrong. Each macro is an angle, in fact I have 360
of them

They are used to change the ange of a line with an arror in it. I could not
figure out another way to do this.

Perhaps you might know of a better way.

**** Example (currently working) ****
Sub SetTruPointer()
' SetTruPointer Macro
Dim myMacCell As Range
Set myMacCell = Worksheets("sheet1").Range("D1")
If 1 = 1 Then
Application.Run myMacCell.Value
Else
Beep
End If
End Sub
Sub Angle1()
' Angle1 Macro
ActiveSheet.Shapes("Line 41").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = 1#
End Sub
Sub Angle2()
' Angle2 Macro
ActiveSheet.Shapes("Line 41").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = 2#
End Sub
***********

TIA
C

Hi Cathy,

Assuming cell A1 contains the value that you want to 'tilt' the shape
something like this should work:

Sub TiltShape()
' SetTruPointer Macro
Dim TiltValue As Integer
TiltValue = Worksheets("sheet1").Range("A1")
ActiveSheet.Shapes("Line 41").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = TiltValue & "#"
End Sub

You will probably want to enter some sore of error checking (ie if
statment)

Regards

Michael

Hi Cathy,

Slight amendment to the code. It does work & therefore if l have
interpreted you requirements correctly avoids the need for 100
macro's.

Sub TiltShape()
' SetTruPointer Macro
Dim TiltValue As Integer
TiltValue = ActiveSheet.Range("A1").Value
ActiveSheet.Shapes("Line 41").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = TiltValue
End Sub

The line will tilt to whatever value is entered into cell A1

If you want this to happen automatically whenever the value in A1 is
changed paste this code into the Sheet1 code module: (copy code, right
click Sheet1 tab, view code, paste)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TiltValue As Integer
If Target = Range("$A$1") Then
TiltValue = Worksheets("sheet1").Range("A1").Value
ActiveSheet.Shapes("Line 2").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = TiltValue
End If

End Sub

Regards

Michael

Wow, Thanks a lot for that.

The line I am tilting is a lin I have drawn using the "Drawing" toolbar.

I have to angle the line to point in different directions. A problem I am
having is that tilting the line, pivots the line on its centrepoint.

What I actually need to do is pivot this line on one end.

Any idea how this can be achieved?

TIA
C

Hi Cathy,

Thats a lot more difficult but heres some code that l have amended
from Chip Pearsons site and most of the credit should go to him.

Everything in this is automatic, even the creation of the 'arrow
line'. Copy & paste the code into the sheet module (as described in my
earlier post) and assuming your 'tilt' value is in cell A1 everyr time
that value changes the existing arrow will be deleted and a new one
inserted at the correct angle. Assumes that 0 is vertical.

Rememebr to delete any existing lines / arrows you have. You will see
at the top of the code that there are some 'Const' variables that
determine the position & length of the arrow. I suggest you try as is
then see what happens when you change the values. Note the X & y
values are determined from the top left of the active sheet.

Hope this helps, let me know how you get on.

NOTE: (beware of word wrapping & commenting of posted code)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("$A$1") Then
'____________________________________________
'Change these values for position of arrow
'rotation point. Relates to postion from top
'left of document.
Const cCenterX As Single = 250
Const cCenterY As Single = 250
'____________________________________________
'Change this value for arrow length
Const cLenMinute As Single = 125
'____________________________________________
Const PI As Single = 3.14159265358979
Const TwoPI As Single = 2 * PI
Dim TiltValue As Integer
Dim LM As Shape
Dim LLM As LineFormat
Dim WS As Worksheet
Dim Theta As Single ' clockwise angle from vertical
Dim NewX As Single
Dim NewY As Single

On Error Resume Next

Set WS = ActiveSheet

WS.Shapes("LineMinute").Delete

'Create minute hand
Set LM = WS.Shapes.AddLine(beginx:=cCenterX, beginy:=cCenterY,
_
endx:=cCenterX, endy:=cCenterY -
cLenMinute)
Set LLM = LM.Line
LM.Name = "LineMinute"
LLM.EndArrowheadStyle = msoArrowheadTriangle
LLM.ForeColor.RGB = RGB(0, 0, 255)
LLM.Weight = 1.5
TiltValue = (Range("A1").Value) / 6
Theta = (TiltValue / 60) * TwoPI
NewX = cCenterX + (cLenMinute * Sin(Theta))
NewY = cCenterY - (cLenMinute * Cos(Theta))
LM.Nodes.SetPosition 2, NewX, NewY
End If
End Sub

Regards

Michael

Thank you very much for this Michael. Really appreciate the trouble you have
gone through to get this to me. I have managed to get your code working and
had a bit of a fiddle with the line wraps so again you were spot on.

I never new that one could enter code on the sheet directly like this and
this will save me a lot of trouble as I can think of loads of solutions I
have used in the past using macro's and having to create buttons etc to get
them working

Thank you very much

Regards
C

Just realised that the angle does not appear to be changing on every change
in value, Only every 4 or 5 degrees there seems to be a change. As the code
itself refers to Minute arm, I asume this was set up to show a clockface.

Perhaps if you send the original code is it was before you tweaked this, I
could try and figure out how to make it show each angle.


On another note. I tried adding the Spinner (From Form Toolbar) to change
the value for me in A1 for testing purposes, but when this is used the Line
is not changed. Seems I have to go to the cell and change the value
manually.

Thanks again for all your help

Regards
C

Hi Cathy,

To find the original code just search 'clock hands' on this site,
there are only a few entry's.

Also l can confirm that the cell usually needs to be edited directly
to trigger the macro.

An alternative is to use a spin button from the 'control toolbox' and
trigger the macro using the 'spinbutton_change' event. (This is
similar to using the sheet code module but in this case right click
the spin button and click view code.)

Reagrds

Michael

Hi Cathy,


To get a movement for each degree amend the code to read as follows:

TiltValue = (Range("A1").Value)
Theta = (TiltValue * PI / 180)

Regards

Michael.

Thanks again for that Michael.

That works perfectly and saved me having to look up and remind myself how
trigonometry works.

Regards
C

What would be the easiest method of getting NewX and NewY values entered
into cells C1 & C2?

"michael.beckinsale" <michael.beckinsale@... wrote in message
news:23ed159c-1eb1-4bd3-aa25-
LM.Nodes.SetPosition 2, NewX, NewY

TIA
Cathy