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