Shapes object events?

[Dr.Ile]

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

[avveerkar]

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

A V Veerkar

[Dr.Ile]

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

[avveerkar]

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

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

A V Veerkar

[Dr.Ile]

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

[avveerkar]

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

[Dr.Ile]

[QUOTE=avveerkar]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.

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

[avveerkar]

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[/QUOTE]

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 or imported on worksheet form a Shapes collection ). In that case a simple procedure to select all the shapes on the sheet and assign a procedure to them could be
Private Sub AssgnProcToLines()
ActiveSheet.Shapes.SelectAll
Selection.OnAction = "MyProcedure"
End Sub
I am assuming that there are only lines as shapes objects on the sheet. The proc I suggested will make all the shapes, not only the lines, on the sheet clickable. In case you want to assign different proc to indivisual lines ( 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 (other 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 members 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

[Dr.Ile]

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

[avveerkar]

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

[Peter T]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>