OLEObject Question

[bont]

Question 1: Does anyone know of any good references for using OLEOBJECTS (Buttons, checkbox) that is free on the net?

Question 2: I have chosen to use OLEOBJECTS because I have the dilema that I want to make a sub such as this:

MySub(Param A, Param B...)

How do I connect a function / sub to this OLEOBJECT button?

Why I need this:

The standard Excel buttons cannot use complicated macros as this (unless anyone knows how I can work around this). I am hoping that I may be able to do this using OLEOBJECTS.

My purpose for this is to create a row management system. Each row will have 2 buttons [+] and [X]. [+] will add a row preceding the current row, while [X] will delete the current row.

I have the functions built to do so, but I can't attach them to standard buttons. The functions work on the premis that the button calls a function "MySub("A1&quot" which will give a relative location of the current row "1", and handle it properly.

My other option is to be able to detect the cell that the button resides in, when acted upon, but I do not know how to do this, an example of this would also be wonderful.

Any guidance would be greatly appreciated.

 

[RobBroekhuis]

Go with the approach you suggested in your last paragraph. Assign the same sub (say, AddRow) to all of your buttons. then use something like:

Sub AddRow()
ActiveSheet.Shapes(Application.Caller).TopLeftCell.EntireRow.Insert
End Sub

This is if you use a button from the forms toolbar. With a button from the control toolbox, you need to reference the oleobjects collection instead of the shapes collection, but the concept is the same.
Rob

 

[bont]

I'm sorry, I think my point wasn't brought across correctly. I don't want the user to have to click on the row.

I have a row, which has two buttons on it. I want a user to be able to hit the [+] button to add a row. This procedure is a lot more complicated then just inserting a row, due to formulas that exist.

What I need to know is how to attach a sub to an OLEOBJECT form.commandbutton, and if it is possible at all all. The sub call is similar to MySub(Param 1, Param2, Param 3...)

My problem has been that my previous button (standard Excel form button), would not allow me to do this, for it considered the MySub(....) too complicated, as it thought of it as a macro, and would state "macro too complex". Bascially, how do I attach a function or sub to a button in excel, and pass parameters at the same time?

 

[RobBroekhuis]

You can't pass parameters. But my example accomplishes, in its most basic form, what you need. The single row insert was just an example, I understand that you'd need to do more manipulation. For example:

Sub AddRow()
dim cell as range
set cell=ActiveSheet.Shapes(Application.Caller).TopLeftCell
cell.offset(1,0).EntireRow.Insert
cell.range("A1:Z1&quot.copy cell.offset(1,0)
End Sub

This inserts a row below the one with the button that was clicked, and copies the first 26 columns from the existing row into the new one (including the button!)

Rob