Excel 2003 - Dynamic Buttons with Parameter to Sub
Posted on 2011-10-17
I am challenged with handling objects in Excel, but need to create a variable number of buttons each of which is then identified (if clicked) and a parameter passed to a Sub. Here is a rough sketch.
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) - 1) = UniqueFoRs
In other words the data dumped spans n rows each with 8 values from Columns B:I on the sheet.
In Column J I wish to place a button (Label = “Go”) in each row containing data from UniqueFoRs. I need to be able to adjust the dimensions of the buttons as a group i.e. set Width/Height parameters that I can adjust for aesthetic purposes.
When a user clicks a button I need it to call Sub (say “ButtonSub”) and pass the Sub the value in UniqueFoRs(1, n) where n = the row number of the Button that has been clicked i.e.
Call ButtonSub(FoR) where FoR = UniqueFoRs(1,n)