• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Excel 2003 - Dynamic Buttons with Parameter to Sub

Hi,
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)
0
TigerMan
Asked:
TigerMan
  • 16
  • 9
  • 2
1 Solution
 
Rory ArchibaldCommented:
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.
0
 
Rory ArchibaldCommented:
Ps obviously no space in the macro name (stupid phone)
0
 
gowflowCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TigerManAuthor Commented:
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?
0
 
gowflowCommented:
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
0
 
TigerManAuthor Commented:
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.

 0202 1103
0
 
Rory ArchibaldCommented:
Something wrong with my suggestions?
0
 
TigerManAuthor Commented:
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 ...
0
 
Rory ArchibaldCommented:
It would be something like:
Dim n as long
Dim btn as button
For n = lbound(UniqueFoRs, 1) to ubound(UniqueFoRs, 1)
   With cells(20 + n, "J")
      Set btn = Activesheet.buttons.add(.left, .top, .width, .height)
      btn.onaction = "'mysub " & UniqueFoRs(n, 1) & "'"
   End With
Next n

Open in new window

for example. This would hardcode the values passed to the sub at the time of button creation.

It may be better to simply set the OnAction to the sub name and have the sub use ActiveSheet.Buttons(Application.Caller).Topleftcell.Row to work out which button has been pressed. Can't really say which is best without your workbook.
0
 
TigerManAuthor Commented:
OK, i will play with this tomorrow ... thanks!
0
 
TigerManAuthor Commented:
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
0
 
Rory ArchibaldCommented:
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.
0
 
TigerManAuthor Commented:
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 ??
0
 
Rory ArchibaldCommented:
Correct.
0
 
TigerManAuthor Commented:
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

Open in new window

0
 
TigerManAuthor Commented:
forget that ... dumb-as-paint sometimes ... when was the last time you checked the Witdh of your swimming pool?
0
 
TigerManAuthor Commented:
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.
0
 
TigerManAuthor Commented:
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

Open in new window

0
 
TigerManAuthor Commented:
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? button-align
0
 
Rory ArchibaldCommented:
Do you have SP2 applied?
0
 
TigerManAuthor Commented:
sp3 is installed :)
0
 
Rory ArchibaldCommented:
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?
0
 
TigerManAuthor Commented:
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?
0
 
TigerManAuthor Commented:
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

Open in new window

0
 
TigerManAuthor Commented:
rorya, this works

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

all good and thanks for pointing me in right direction
0
 
Rory ArchibaldCommented:
Glad to help. And I enjoyed your discussion with yourself while I was otherwise engaged. ;)
0
 
TigerManAuthor Commented:
all good then ... thanks very much for help on this one :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 16
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now