Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Go Back  Xtreme Visual Basic Talk > > > > Programmatically Adding Buttons to a Sheet


Reply
 
Thread Tools Display Modes
  #1  
Old 09-08-2017, 01:30 PM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Programmatically Adding Buttons to a Sheet


I am attempting to create a Control Array of command buttons on a worksheet.

It almost works, but my class instances get terminated, so the event will not stick.

I've seen this done before (and have a working version) but only for pre-existing controls. The example I have uses check boxes, but the control type shouldn't matter. I think I'm missing something. I just don't know what.

Or is it not possible at all to do what I'm trying to do?
Attached Files
File Type: zip Command Button Control Array on Sheet.zip (24.4 KB, 9 views)
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #2  
Old 09-08-2017, 10:08 PM
Kluz's Avatar
KluzProgrammatically Adding Buttons to a Sheet Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

Have you looked at Gruff's answer?
__________________
No the other right mouse click
Reply With Quote
  #3  
Old 09-11-2017, 02:05 AM
Goggy's Avatar
Goggy Goggy is offline
Contributor
 
Join Date: Sep 2005
Location: Enschede,The Netherlands
Posts: 670
Default Maybe this could help, a little?

Basicly what i try to do is, add a btn to sheet and then through the vbe add the code that handles the click event of that buttun.

Code:
Const DQUOTE = """" ' one " character

Public Sub AddBtn(Sh As Excel.Worksheet, BtnName As String)
    Dim myOleBtn As OLEObject
    Dim sGap As Single
    Dim sShapeTop As Single
    Dim sShapeHeight As Single
On Error GoTo ErrHandler

    sGap = 30
    sShapeTop = 15
    sShapeHeight = 30
    'Create, place, name, and caption the button
    Set myOleBtn = Sh.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=48, Top:=sShapeTop, Width:=96, Height:=sShapeHeight)
    sShapeTop = sShapeTop + sShapeHeight + sGap
    myOleBtn.Name = BtnName
    myOleBtn.Object.Caption = BtnName
    
    Call AddBtnProcedure(Sh, BtnName)
    Exit Sub
ErrHandler:
    MsgBox (Err.Description)
End Sub

Private Sub AddBtnProcedure(Sh As Excel.Worksheet, BtnName As String)
 
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
 On Error GoTo ErrHandler

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(Sh.Name)
    Set CodeMod = VBComp.CodeModule
    
    With CodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, "Public Sub " & BtnName & "_Click()"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    MsgBox " & DQUOTE & BtnName & DQUOTE
        LineNum = LineNum + 1
        .InsertLines LineNum, "End Sub"
    End With
     Exit Sub
ErrHandler:
    MsgBox (Err.Description)
End Sub
You will need a reference to "microsoft visual basic for applications extensibility 5.3"

For more helpfull information about programming the VBE ,
Programming In The VBA Editor
__________________
As finishing touch, god created the Dutch!

utterly useless, but always trying to help
Reply With Quote
  #4  
Old 09-11-2017, 09:01 AM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default

Thanks for the link, Kluz. Unfortunately, Gruff is using a form rather than a worksheet, so I'm not that everything is apples to apples. Though I admit I'm confused why he would use 3 classes just to capture a click event.

Goggy, I'm aware that I could edit the VB code through VB code, but that would require asking any user to change Trust Center settings. Also, the number of buttons must be variable, so I'd like to be able to kill them and recreate them by clicking a button. That could get messy. I'm not a big fan of modifying code with code, unless I'm applying a patch to several files at once.

I'm attaching a working version that uses checkboxes. If you look at it, it's quite simple. I just don't know why that one works and the command button one does not.
Attached Files
File Type: zip VBA Control Array on Sheet.zip (22.5 KB, 5 views)
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #5  
Old 09-11-2017, 08:26 PM
Kluz's Avatar
KluzProgrammatically Adding Buttons to a Sheet Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

I noticed a difference in how the two workbooks are behaving: Open the Locals window...
Checkbox: when I run the Class_Initialize procedure all goes well and then I step into it (just one step is all you need(F8 key)). Now in the locals window you can open the Module section and see that the colCheckBox collection is populated: all is is as expected.

Buttons:
Run the PrepareButtons sub and when it ends step into it one step and look at the colButtons collection: empty!

Stepping through the PrepareButtons sub again I get a problem at the "Set myOleBtn = ..." line I get an error: "Can't enter break mode at this time" I think that's what's buggering the thing up.

Maybe just create the button, which now has focus, then set the focus back to the sheet, then update the button's properties.
__________________
No the other right mouse click
Reply With Quote
  #6  
Old 09-12-2017, 03:10 AM
Goggy's Avatar
Goggy Goggy is offline
Contributor
 
Join Date: Sep 2005
Location: Enschede,The Netherlands
Posts: 670
Default This Seems to work, in the way i think you want it too....

Atleast i'm hopefull
Attached Files
File Type: zip VBA Control Array on Sheet.zip (39.2 KB, 3 views)
__________________
As finishing touch, god created the Dutch!

utterly useless, but always trying to help
Reply With Quote
  #7  
Old 09-12-2017, 08:57 AM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default

Thanks again, guys.

Kluz - I had assumed that the class calls were causing me to not be able to enter break mode. As it turns out, any creation or removal of an OleObjects object seems to prevent you from entering break mode. I couldn't even enter break mode before entering the loop, due to my CleanUp procedure call. That is one big difference between the two files, though.

Goggy - Success! Thanks! I get what you're doing, though I am confused by this bit:
Code:
Public Property Get NewEnum() As IUnknown
    'this property allows you to enumerate
    'this collection with the For...Each syntax
    Set NewEnum = mCol.[_NewEnum]
End Property
What are IUnknown and _NewEnum?

I'm going to play around and try to take bits from your file and my file.

Thanks again - both of you.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #8  
Old 09-12-2017, 09:21 AM
Goggy's Avatar
Goggy Goggy is offline
Contributor
 
Join Date: Sep 2005
Location: Enschede,The Netherlands
Posts: 670
Default

For the best explenation. (this because i can't explane things for ****...)

Quote:
The important thing you're delegating to the Collection object is its enumerator. An enumerator is a small object that knows how to iterate through the items in a collection. You can't write an enumerator object with Visual Basic, but because the Employees class is based on a Collection object, you can return the Collection object's enumerator — which naturally enough knows how to enumerate the items the Collection object is holding.
The square brackets around the Collection object's _NewEnum method are necessary because of the leading underscore in the method name. This leading underscore is a convention indicating that the method is hidden in the type library. You can't name your method _NewEnum, but you can hide it in the type library and give it the procedure ID that For Each … Next requires.
source : https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx
__________________
As finishing touch, god created the Dutch!

utterly useless, but always trying to help
Reply With Quote
  #9  
Old 09-12-2017, 09:52 AM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default

Okay, without TOO much rework, I was able to use your clsButtons class, Goggy.

But get this - if I run the PrepareButtons sub on my original file, the buttons get created but without getting added to the collection, so the click event doesn't fire.

However, if I open a new workbook and run the exact same code, it works perfectly and acts how I wanted it to act! Also, I'm able to enter break mode - so Kluz is onto something, but I'm confused.

So I don't know if there is some corruption in my original file or what's going on.

Anyway, I think I have a working version of my the program. I may re-upload it once it's all fixed in case someone revisits this thread in 5 years and wants to see the final solution.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #10  
Old 09-12-2017, 10:24 AM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default

Attached is a file that almost works.

To make it work:
  • Open the attached file
  • Open a New Workbook
  • From the VBE window, run the macro called PrepareButtons (it's good to have dual monitors)

To make it not work - make the attached workbook the active one and then run the macro.

I suspect that the reference to the Microsoft Forms object library is what's causing the problem, because I copied all of the code & class modules into a new workbook and had the same issue.
Attached Files
File Type: zip Command Button Control Array on Sheet.zip (30.0 KB, 6 views)
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #11  
Old 09-12-2017, 01:26 PM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default

I went back and looked at my original version. With a small adjustment to my HelloWorld sub, that worked if I had a different workbook active too. Curiouser and curiouser....
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #12  
Old 09-12-2017, 08:20 PM
Kluz's Avatar
KluzProgrammatically Adding Buttons to a Sheet Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

I'm noticing that the buttons are being added to the code free active workbook rather than the .xlb running the code. Have you tried using an .xls or .xlsm format?
__________________
No the other right mouse click
Reply With Quote
  #13  
Old 09-13-2017, 01:59 AM
Goggy's Avatar
Goggy Goggy is offline
Contributor
 
Join Date: Sep 2005
Location: Enschede,The Netherlands
Posts: 670
Default

I̶t̶ ̶s̶e̶e̶m̶s̶ ̶t̶o̶ ̶h̶a̶v̶e̶ ̶s̶o̶m̶e̶t̶h̶i̶n̶g̶ ̶t̶o̶ ̶d̶o̶ ̶w̶i̶t̶h̶ ̶t̶h̶e̶ ̶s̶c̶o̶p̶e̶ ̶o̶f̶ ̶t̶h̶e̶ ̶m̶y̶O̶l̶e̶B̶t̶n̶ ̶o̶b̶j̶e̶c̶t̶.̶ ̶W̶h̶e̶n̶ ̶i̶ ̶m̶a̶k̶e̶ ̶t̶h̶e̶ ̶s̶c̶o̶p̶e̶ ̶o̶f̶ ̶t̶h̶e̶ ̶v̶a̶r̶i̶a̶b̶l̶e̶ ̶g̶l̶o̶b̶a̶l̶,̶ ̶n̶o̶t̶ ̶l̶o̶c̶a̶l̶ ̶t̶h̶e̶n̶ ̶t̶h̶e̶ ̶r̶o̶u̶t̶i̶n̶e̶ ̶w̶o̶r̶k̶s̶ ̶j̶u̶s̶t̶ ̶f̶i̶n̶e̶.̶ ̶A̶l̶d̶o̶ ̶i̶'̶m̶ ̶n̶o̶t̶ ̶s̶u̶r̶e̶ ̶j̶e̶t̶ ̶,̶ ̶w̶h̶y̶ ̶t̶h̶a̶t̶ ̶i̶s̶.̶.̶.̶.̶

Spoke to soon... sorry!
__________________
As finishing touch, god created the Dutch!

utterly useless, but always trying to help

Last edited by Goggy; 09-13-2017 at 02:32 AM.
Reply With Quote
  #14  
Old 09-13-2017, 07:49 AM
MillProgrammatically Adding Buttons to a Sheet Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default

Quote:
Originally Posted by Kluz View Post
I'm noticing that the buttons are being added to the code free active workbook rather than the .xlb running the code. Have you tried using an .xls or .xlsm format?
I'm using the ActiveSheet object, so whichever is the active workbook, the buttons get created there. It's just that the code works if you have a different workbook active, but not the one with the code.

Quote:
Originally Posted by Goggy View Post
Spoke to soon... sorry!
I could barely read that...

This was more of a side project. I figured out a good way to cut a picture into pieces, and I thought I'd play with making that into a slider puzzle program.

However, picture objects don't have a Click event, so I thought about trying to put a transparent command button on top of each tile. But that led to its own set of issues.

But at least I have the tiles created from a picture (either 4, 9, 16, or 25) and that works smoothly. I just haven't figured out how I want to take that next step.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #15  
Old 09-13-2017, 08:18 PM
Kluz's Avatar
KluzProgrammatically Adding Buttons to a Sheet Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

Quote:
Originally Posted by Mill View Post
the code works if you have a different workbook active
Yep, I tried reparenting the sheet to the ThisWorkbook object and got the errors first seen even though a different workbook was the active workbook.

The Picture object lacks the event, but the "Forms.Image.1" has the click event. I've used the MouseMove event on these in a dynamically built collection.

Hmmm, maybe you can mock up a label to look like a button using its 3d formatting.
__________________
No the other right mouse click
Reply With Quote
Reply

Tags
buttons, control, sheet, adding, programmatically


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
 
Programmatically Adding Buttons to a Sheet
Programmatically Adding Buttons to a Sheet
 
-->