command buttons in worksheet

Posted by Jay on June 08, 2001 10:08 PM

Hi,

I need to add several command buttons (about 35) to a spreadsheet. I am using following VB code to add the buttons, which works fine except it's very slow.

Does anyone has any idea how i could do this in a more efficient way?

For i = 1 To 30
Set objBtn = lWorkSheet.OLEObjects.Add"Forms.CommandButton.1", _
Left:=1, _
Top:=lWorkSheet.Cells(lStartingRow, 1).Top + 1, _
Height:=lWorkSheet.Cells(lStartingRow, 1).Height - 1, _
Width:=lbtnWidth - 1)
next i

Posted by Damon Ostrander on June 09, 2001 3:42 PM

Hi Jay,

Why not just add Forms buttons? They are very efficient. Here's the code.

Also, I noticed that you never assigned the buttons to anything. Here I assign all the buttons to run "macro1", and also give each button a caption (Run Proc 1, Run Proc 2, etc.) Hopefully you can see how to customize this for your case.

Good luck.

Damon

For i = 1 To 30
Set btn = ActiveSheet.Buttons.Add(100, 200 + 40 * i, 100, 25)
btn.OnAction = "Macro1"
btn.Characters.Text = "Run Proc " & i
Next i

Posted by Jay on June 09, 2001 7:16 PM

Hi Damon,

Thanks for your reply. I have my code in VB6 project (COM) and i can't see any Buttons property on ActiveSheet object. Is this code for VBA only?

Posted by Damon Ostrander on June 11, 2001 12:10 PM

Jay,

Buttons is actually a collection of Button objects, not a property. The Button object is not documented in Excel 97 and beyond, being one of many objects that were replaced by the ActiveX controls that one now finds on the Controls toolbar. They are still supported in newer versions of Excel for backward compatibility purposes, and Microsoft now considers them archaic features, even though they have some functionality that does not exist in the controls that have replaced them, such as the ability to easily define in VB code the macro that you want to run.

The result of this is that Buttons are not a VBA capability at all, but rather part of the Excel object model (albeit an undocumented part). I generated this example in VBA, but if you cannot run this code in VB6 then you probably just do not have the complete set of Excel object libraries loaded. In the VBE for Office there is a References item in the Tools menu that allows you to select the object libraries to load. I am not up on VB6 (and in fact don't even have it), but there should be an equivalent capability there to specify the appropriate object libraries. Just make sure you have all the object libraries loaded in VB6 that you see in the list of Office VBE References for Excel.

Happy computing.

Damon

Posted by Jay on June 12, 2001 12:58 AM

Hi Damon,

Thanks for your reply. I am able to get the code running and noticed that it's extremely fast to add button to the worksheet in this way. I have now faced the challenge with making the buttons to work. How could i define a macro in vb, that should be invoked when the button is clicked?

I appreciate your help