Can I write event procedures at shapes object level? How?
Dr.Il

--
Dr.Il
-----------------------------------------------------------------------
Dr.Ile's Profile: http://www.excelforum.com/member.php...fo&userid=1597
View this thread: http://www.excelforum.com/showthread.php?threadid=50668

Yes you can have mouse_click event associated with a shape object. Onc
you have a shape ( picture, drawing object, clipart etc ) on the sheet
right click on the shape, choose " Assign Macro " and then assign an
procedure to this shape. This procedure will execute when you click o
the shape.

A V Veerka

--
avveerka
-----------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...fo&userid=3033
View this thread: http://www.excelforum.com/showthread.php?threadid=50668

Thank you avveerkar,
Any suggestions if I have n lines (n20 and is changing)

--
Dr.Il
-----------------------------------------------------------------------
Dr.Ile's Profile: http://www.excelforum.com/member.php...fo&userid=1597
View this thread: http://www.excelforum.com/showthread.php?threadid=50668

Dr.Ile Wrote:
Thank you avveerkar,
Any suggestions if I have n lines (n20 and is changing)?

Sorry, I don't understand your question. 20 lines of what? Associatin
mouse_click event to a shape would not depend on any line count. Yo
will need to define your problem again.

A V Veerka

--
avveerka
-----------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...fo&userid=3033
View this thread: http://www.excelforum.com/showthread.php?threadid=50668

Sorry for nontrasparent question. I'll try in a different way:
At the beggining of my procedure I don't know how many lines there will
be. I get their number from running specific procedure. So, number of
lines is dependent of my input in this specific procedure. Because of
these dependenties I want write a procoderu who will first read the
number of lines and then assign them event procedures or macros (your
way).
Thank you
PS:Sorry for my language:)


--
Dr.Ile
------------------------------------------------------------------------
Dr.Ile's Profile: http://www.excelforum.com/member.php...o&userid=15975
View this thread: http://www.excelforum.com/showthread...hreadid=506688

Dr.Ile Wrote:
Sorry for nontrasparent question. I'll try in a different way:
At the beggining of my procedure I don't know how many lines there will
be. I get their number from running specific procedure. So, number of
lines is dependent of my input in this specific procedure. Because of
these dependenties I want write a procoderu who will first read the
number of lines and then assign them event procedures or macros (your
way).
Thank you
PS:Sorry for my language:)

I am again sorry, I am still not with you. By number of lines do you
mean number of used rows in a worksheet? Or you are referring to number
of lines of code of your procedure? If it is later case then I do not
know what exactly you are referring to because I cannot visualise a
situation where number of code lines can change. In the former case,
are you saying that the number of rows used is not fixed and you want
to write a procedure to first find out how many rows are used and once
you know the number then you want to evluate those rows? There is a
method called CurrentRegion which will return a used range and then
using Rows.Count you could find out number rows in the range. Is that
what you are looking for? Else you need to tell me what exactly is your
application. Sorry for being such thick-headed.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=506688

avveerkar Wrote:
I am again sorry, I am still not with you. By number of lines do yo
mean number of used rows in a worksheet? Or you are referring to numbe
of lines of code of your procedure? If it is later case then I do no
know what exactly you are referring to because I cannot visualise
situation where number of code lines can change. In the former case
are you saying that the number of rows used is not fixed and you wan
to write a procedure to first find out how many rows are used and onc
you know the number then you want to evluate those rows? There is
method called CurrentRegion which will return a used range and the
using Rows.Count you could find out number rows in the range. Is tha
what you are looking for? Else you need to tell me what exactly is you
application. Sorry for being such thick-headed.

Don't be sorry...I'm very happy that someone is interested in m
problem. I'll try again. I have a procedure which import DXF (AutoCad
into Excel. Lines in Autocad becomes Lines in Excel. I can draw an
Number of lines in Autocad so I don't know for their number till I ru
procedure which import DXF in Excel. Now I have their number and th
procedure already draw them all (in Excel). But here the main proble
appear: how can I assign a macro to all lines by VBA. I can do thi
manual (clik first line, assign macro, click second...like you sugges
in first reply). I want to VB do that for all lines.
Dr.Il

--
Dr.Il
-----------------------------------------------------------------------
Dr.Ile's Profile: http://www.excelforum.com/member.php...fo&userid=1597
View this thread: http://www.excelforum.com/showthread.php?threadid=50668

Don't be sorry...I'm very happy that someone is interested in my
problem. I'll try again. I have a procedure which import DXF (AutoCad)
into Excel. Lines in Autocad becomes Lines in Excel. I can draw any
Number of lines in Autocad so I don't know for their number till I run
procedure which import DXF in Excel. Now I have their number and the
procedure already draw them all (in Excel). But here the main problem
appear: how can I assign a macro to all lines by VBA. I can do this
manual (clik first line, assign macro, click second...like you suggest
in first reply). I want to VB do that for all lines.
Dr.Ile

I was away for two days hence could not respond to your mail. Again sorry
for that. Now I understand your problem. I have never worked with DXF. But
as I understand when when you a group of lines from CAD to EXCEL each line
is represented as indivsual drawing object in EXCEL. Suppose there are 10
lines. There are two possibilities. You want to assign only one procedure
to all the 10 lines. That means if you click on any line ( each line is
now clickable ) one procedure needs to be executed. I assume that these
lines are imported as Shapes objec collection in EXCEL. ( I am assuming
this because all drawing objects such as images, cliparts, lines and
figures drawn on worksheet form a Shapes collection ). In that case a
simple procedure to select all the shapes on the sheet and assign a
procedure could be
Private Sub AssgnProcToLines()
ActiveSheet.Shapes.SelectAll
Selection.OnAction = "MyProcedure"
End Sub
This will make all the shapes on the sheet clickable and I am assuming
that there are only lines as objects. In case you want to assign
different proc to indivisual ( doesn't seem likely )then you need have
those different procedures named as, say, myproc1, myproc2,myproc3 etc
and want to assign first one to line 1, second to line 2 etc Then your
proc could be

For i = 1 to 10
ActiveSheet.Shapes(i).select
Selection.OnAction ="myproc" & i
Next
If you have many other shapes on the worksheet othere than the lines and
want to make only the lines clickable then you need to find out how these
lines are identified by EXCEL as member of Shapes colletion. They could be
pictures, lines etc One qucikest method is to record a macro select these
lines one by one and then read the macro code to find out how EXCEL
identifies the lines.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=506688

Thank you AVeerkar again, you show me the right way (I want to assign
different proc to individual lines - myproc1, myproc2,myproc3 etc). I
also ruminated abbout OnAction method and now I'm sure that this is it!
I have only one question:
As I already wrote, the number of lines is unknown (till the DXF is
imported) and consecutive the number of macro myproc1, myproc2,myproc3
etc. Do you suggest to write approx. 500 macros, soo last macro will be
myproc500 (I work with approx. 400 lines). With 500 macros I’ll capture
all possible lines. Is there onother way. I read that’s possible to
write I macro with VBA, but...:).

Dr.ile


--
Dr.Ile
------------------------------------------------------------------------
Dr.Ile's Profile: http://www.excelforum.com/member.php...o&userid=15975
View this thread: http://www.excelforum.com/showthread...hreadid=506688

Dr.Ile Wrote:
Thank you AVeerkar again, you show me the right way (I want to assign
different proc to individual lines - myproc1, myproc2,myproc3 etc). I
also ruminated abbout OnAction method and now I'm sure that this is it!
I have only one question:
As I already wrote, the number of lines is unknown (till the DXF is
imported) and consecutive the number of macro myproc1, myproc2,myproc3
etc. Do you suggest to write approx. 500 macros, soo last macro will be
myproc500 (I work with approx. 400 lines). With 500 macros I’ll capture
all possible lines. Is there onother way. I read that’s possible to
write I macro with VBA, but...:).

Dr.ile

Yes choose a large number ( say 500 ) to cover the actual number of
lines you may have. After the procedure assigns macro to the last line,
when it tries to assign a macro to a new line which does not exist, it
will give an error. Trap that error with On Error Exit Sub. This could
be your first line of the sub before you enter For..Next loop to assign
macros to different lines. Thus your sub would be
Sub AssgnMacro()
On Error Exit Sub
For i = 1 to 500
ActiveSheet.Shapes(i).select
Selection.OnAction ="myproc" & i
Next
End Sub

There would be error on two counts -either when it tries to assign proc
to a non-existant line or tries to assign a proccedure which does not
exist. It means that you must have enough number of procedures (
Myproc1 to Myproc500 ) to cover maximum number of lines you may
actually have. Since we are now only exiting on error, if number of
procedures is less than you actually require, the sub will just exit
without assigning any macro to last lines and there wont be any
indication.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=506688

I haven't read all this discussion but propbably easier to have just the one
macro

Sub MyProc()
Dim shp As Shape
Dim s As String, n As Long
s = Application.Caller

' assuming names like "Line x"
n = Val(Mid$(s, 5, 5))

Set shp = ActiveSheet.Shapes(Application.Caller)

Stop
'press Alt-v, s
'Look "shp" in Locals for properties that can be returned

'maybe
'Select case n ' etc

End Sub

Sub setup()
Dim oLine As Line
For Each oLine In ActiveSheet.Lines

oLine.OnAction = "myProc"
Next
End Sub


Regards,
Peter T

"avveerkar" wrote
in message ...

Dr.Ile Wrote:
Thank you AVeerkar again, you show me the right way (I want to assign
different proc to individual lines - myproc1, myproc2,myproc3 etc). I
also ruminated abbout OnAction method and now I'm sure that this is it!
I have only one question:
As I already wrote, the number of lines is unknown (till the DXF is
imported) and consecutive the number of macro myproc1, myproc2,myproc3
etc. Do you suggest to write approx. 500 macros, soo last macro will be
myproc500 (I work with approx. 400 lines). With 500 macros I’ll capture
all possible lines. Is there onother way. I read that’s possible to
write I macro with VBA, but...:).

Dr.ile

Yes choose a large number ( say 500 ) to cover the actual number of
lines you may have. After the procedure assigns macro to the last line,
when it tries to assign a macro to a new line which does not exist, it
will give an error. Trap that error with On Error Exit Sub. This could
be your first line of the sub before you enter For..Next loop to assign
macros to different lines. Thus your sub would be
Sub AssgnMacro()
On Error Exit Sub
For i = 1 to 500
ActiveSheet.Shapes(i).select
Selection.OnAction ="myproc" & i
Next
End Sub

There would be error on two counts -either when it tries to assign proc
to a non-existant line or tries to assign a proccedure which does not
exist. It means that you must have enough number of procedures (
Myproc1 to Myproc500 ) to cover maximum number of lines you may
actually have. Since we are now only exiting on error, if number of
procedures is less than you actually require, the sub will just exit
without assigning any macro to last lines and there wont be any
indication.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=506688