Excel 2003 - Dynamic Buttons with Parameter to Sub

Assuming forms buttons, you can use:
Activesheet.buttons(application.caller).topleftcell.row
In your called macro to get the row number under the top left corner of the button. You could also add the parameter to the onaction property when you create the button:

btn.onaction = "'my sub " & rownum & "'"

For example.

Ps obviously no space in the macro name (stupid phone)

I am not sure I understood the whole thing but in summary (correct me if I am wrong) you need a button to the shape of the cell n in col j to be located in dimention exactly same as the cel Jn when you click it it will pass the value of An to In to the sub ?

but then why a button ? can you do this just by clicking on the cell Jn

If yes what do you want to do with the values? pls correct me maybe I misunderstood
gowflow

hi gf ... it's a messy problem to describe so I will go at it again and try to answer your questions in what i write.

UniqueFoRs (8 columns, n rows) is an array where n is always between 0 and 157.

The contents of UniqueFoRs are dumped to a sheet as per
Range("B20:I" & 20 + UBound(UniqueFoRs, 1) = UniqueFoRs

From that, if n = 10, then you can see that Sheet1!B20:I30 will contain the values from UniqueFoRs.

Beside each of those rows of data (in Column J) I wish the macro to place a button.  The macro needs to have parameters for the width and height of all buttons so that I can change them to what I wish them to be.

Each button placed in Column J has Label = Go

When any of the buttons are clicked it will run another Sub (call this one ButtonSub), but your macro needs to call that Sub and pass it a value for a parameter.  In other words the declaration of the ButtonSub would look like this:

Sub ButtonSub(Value)

Where Value is the contents of Column B of the same row on which the button was placed (in J).

Your macro will look like this:

Call ButtonSub(Range(“B”&n).Value)

Since UniqueFoRs is an array with the same contents as B20:I30, then the value that is passed in the Call can also use the Array to pass the value i.e.

Call ButtonSub(Application.Lookup(Row), UniqueFoRs(Row, 1)

Does that help?

Just so I can understand if you have always as you mentioned n is always between 0 and 157.
then you need to have placed in the worksheet 157 buttons ? like at design we have to put 157 buttons or when n  is selected you want the macro to create a button at the location of n ... ???

and also what determins the value of n ? you want the macro to prompt the user for a value of n ? or when he select something it will pass the value of the row selected ? still all this is vague for me sorry am a bit slow.. can you post a sample workbook as they always say a picture is worth a 1000 words !!! :)
gowflow

1: i need as many buttons as there are rows of data in B20:I_n - one beside each row in column J.  You can get the value of n from either the number of rows on Sheet1!B20:I_n or UBound(UniqueFoRs,1)
Each button would probably call the same macro but it must pass the parameter UniqueFoRs(n, 1) so that I can do further processing

The sample workbook is a good idea but it will take 2 files (1 is quite large) and it is proprietary data that is important.

I have attached a couple of images - they are produced by macros, and the two different shots show that n can be different.  I have also marked where the buttons need to be placed.

 

Something wrong with my suggestions?

lol ... rorya, sorry, i glossed over you notes as i was confused
if you can work with the problem as now specified fairly clearly i am happy with that ...

OK, i will play with this tomorrow ... thanks!

just a question before i go to bed

i assume that this btn.onaction = "'mysub " & UniqueFoRs(n, 1) & "'" is the call to mySub
does it pass UniqueFoRs(n, 1) to mySub as a parameter?  one that changes dependent on the vaule of UniqueFoRs(n, 1) in relation to the row on which it is placed?

like i said right at the start i know nothing about messing with drawing objects etc in excel

Yes - it adds whatever the value of UniqueFoRs(n, 1) is to the macro call. So for example if the value is 4 for that iteration of n, the button is assigned the macro:
'MySub 4'
and passes 4 to the MySub code. This will not work for objects, only literal values (though you can work around that) - if you are passing text, you need to include that in quotes.

so i would code as
Sub mySub(Value)
For counter = 1 to Value
Do Stuff
Next counter
End Sub

and this will know that Value is whatever is passed from onAction ??

Correct.

hi rorya
the below code fails at the line

Set aBtn = ActiveSheet.Buttons.Add(.Left, .Top, .Witdh, .Height)

where it returns 'Object doesn't support this property or method' runtime error.

Sub Placebuttons()
Dim Counter, aBtn As Button

For Counter = 1 To UBound(UniqueFoRs, 1)
    With Cells(20 + Counter, "J")
        Set aBtn = ActiveSheet.Buttons.Add(.Left, .Top, .Witdh, .Height)
        aBtn.OnAction = "'ExpandFoRs " & UniqueFoRs(Counter, 1) & "'"
    End With
Next Counter
End Sub

Select allOpen in new window

forget that ... dumb-as-paint sometimes ... when was the last time you checked the Witdh of your swimming pool?

OK, a couple of serious questions ...
1: how do I make the Label = "Go" for each of these buttons?  The above code with Width spelt correctly is what I now have working.
2: what is the code to destroy those buttons i.e. when any one of them is clicked, I need to remove them from Sheet!1 and from memory altogether.

ok, it is done. below code works fine and i have a bit of a heads up on how to work with buttons as well
all good ... i will wait a day or two so that I can ask other 'minor' questions about this, and then give points for answer ... cool?

Sub ExpandAFoR(FourFoR)
Dim Response, Counter

'Response = MsgBox("Button for " & Format(FourFoR, "0000"))
For Counter = 1 To NumUniqueFoRs
    ActiveSheet.Shapes(Format(UniqueFoRs(Counter, 1), "0000")).Delete
Next Counter
' do rest of processing here
End Sub
Sub Placebuttons()
Dim Counter, aBtn As Button

For Counter = 0 To UBound(UniqueFoRs, 1) - 1
    With Cells(20 + Counter, "J") ' start placing btns at row 20 col J
        Set aBtn = ActiveSheet.Buttons.Add(.Left, .Top, 60, .Height)
        With aBtn
            .Caption = "Look at " & Format(UniqueFoRs(Counter + 1, 1), "0000")
            .Font.Size = 8
            .Name = Format(UniqueFoRs(Counter + 1, 1), "0000")
            .OnAction = "'ExpandAFoR " & UniqueFoRs(Counter + 1, 1) & "'"
        End With
    End With
Next Counter
End Sub

Select allOpen in new window

a question then
when the btns are created in column J, the first is placed a few pixels above the border of J20
each button has height slightly less than the height of a cell (default cell/row height)
each subsequent btn placed after the first is abutted up to the previous
therefore by the n_th button the bottom border of the button has crept up significantly and no longer aligns with the cell
when n is much larger the buttons will be a full row out of sync
is there a way to fix this?

Do you have SP2 applied?

sp3 is installed :)

Oh, sorry - had it in my head that you were on 2007.
Does it still happen if you set the page zoom to 100% first?

no, it is nicely sized and placed at 100%

so do you know how to collect the zoom size, change to 100%, run the buttons routine, then change back to original zoom?

and this doesn't work ... when i look at the value of Zoom after this

Zoom = Worksheets("Startup").PageSetup.Zoom

it returns 100 even though i have set the sheet intentionally to 80

any clues?

Sub Placebuttons()
Dim Counter, aBtn As Button, Zoom

Zoom = Worksheets("Startup").PageSetup.Zoom
Worksheets("Startup").PageSetup.Zoom = 100

For Counter = 0 To UBound(UniqueFoRs, 1) - 1
do stuff
Next Counter
Worksheets("Startup").PageSetup.Zoom = Zoom
End Sub

Select allOpen in new window

rorya, this works

myZoom = ActiveWindow.Zoom
ActiveWindow.Zoom = 100
do stuff
ActiveWindow.Zoom = myZoom

all good and thanks for pointing me in right direction

Glad to help. And I enjoyed your discussion with yourself while I was otherwise engaged. ;)

all good then ... thanks very much for help on this one :)