Using VBA to place an ActiveX command button with code

My objective here is to have a command button labeled "Add Detail" created and coded using VBA.
The attached form looks like this when opened. Notice to the far right juts below the yellowed cells there's and part of a column that is blank.
Without command buttonsAfter the command buttons have been macro created the worksheet would look like this
After command buttonsEach command button has code similar to this
Private Sub cmdJan14_Click()
End Sub

Open in new window

I did find this snippet and too my surprise a command button was created.
Sub CreateButton()            

Dim Obj As Object            
Dim Code As String            


'create button            
    Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _            
    Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)            
    Obj.Name = "TestButton"            
'buttonn text            
    ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"            

'macro text            
    Code = "Sub ButtonTest_Click()" & vbCrLf            
    Code = Code & "Call Tester" & vbCrLf            
    Code = Code & "End Sub"            
'add macro at the end of the sheet module            
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule            
        .insertlines .CountOfLines + 1, Code            
    End With            
End Sub            

Open in new window

However, the subroutine needs to be added so I have that problem. Also, based upon the code above I need to modify specific properties. The properties of a command button would look like these:
PropertiesHere's why all this automation. The client has no clue how to add a command button, it's properties, and the code behind it. They want to have the ability to add more worksheet years to this workbook as well as new workbooks that represent project and automate the process. I feel I've got that captured, although the macros I have for adding formulas could be better written. Now I'm down to the last major barrier here.

BTW, In the first code snippet the only thing that changes is the MonthYear. For example if the next year is 15 then there would be 15 command buttons labeled MonthYear
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Why do you need command buttons, particularly ActiveX ones which as you know are problematic on a sheet? If you wanted to you could add code to the Worksheet_SelectionChange sub and when a cell in V6:V17 (V17 would be variable depending on how many years there are) were selected you could execute the same code that would have been in your buttons.
Martin LissOlder than dirtCommented:
The code would look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lngLastRow As Long
Dim strSheetName As String

lngLastRow = Range("A:A").Find("Totals").Row - 2

If Not Intersect(Target, Range("V6:V" & lngLastRow)) Is Nothing Then
    strSheetName = Target.Offset(0, -21).Text & " " & Right$(Target.Offset(0, -20).Text, 2)
End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
And here the workbook. Made a small change to the SelectionChange code.
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Frank FreeseAuthor Commented:
Responding to you other post from yesterday right now
I'll look at this shortly
Frank FreeseAuthor Commented:
Let me quickly update you here. This began as a project to help someone my best friend knows named Heidie (that's how she spells it). In the beginning there was just a few ActiveX controls, primarily those for "Detail". I thought I was done.

The first version did what she wanted. However, three months later she come back with some changes. It is apparent that the organization lacks anyone there, especially in their IT department, with anything more than beginner's Excel.
She thinks a macro was a fish so a micro must be a small fish (apparently she does not care for fish).

She has really complicated this project. Each workbook represents a project and all the monthly details are in worksheets by MONTH YR. in the same workbook. The monthly detail totals rolls up to the first worksheet "Consolidated" At the end of each month are command buttons labeled "Detail" that take her to the month/year for the detail in the "Consolidated" worksheet.  

Her organization's primary purpose is to "offer their clients comprehensive engineering and consulting services throughout the project cycle."  OK, a project goes into it's second year (no pre-determined life for projects).
She wants to add new monthly tabs for the new year and update the "Consolidated" worksheet to show the total activity for the second year as well as all the formulas. But this is to be all automated. The last step of this project that I'm working on is a solution so that when a second year is added some methodology is in place where she can go to the detail tab of the added month/year. In the beginning I used a command button with just a few lines of code.

There you are - the reason for the ActiveX control.

To see how this works so far I've attached two files. .

Download and save them to a location of your choosing. Open the "Consolidated-with-Formulas" workbook. To the right of the name "ILF Consultants, LLC" are four form control command buttons, macros assigned. I plan on consolidating a lot of my macros into. Right now they are separate for I'm testing each one independently. Start with the first one, "Year" enter 2014. From there select "Append". You will need to know the location of the "Append" file. Enter the 2 digit number 14. Next select "Formulas". The last step would be to have a macro to add "Details" Finally, and I still have some mileage left on this, select the command button labeled "Add Summary Year" - that begins the next year of the project.

The last thing she want, but not from me, is a workbook title "Master Project Summary List" where she wants a report for her boss on the summary totals for all open projects. This is now a database where a field could be tagged Open/Closed. The process to get her information is mind boggling to me (You will see to the far left of the workbook Open Close radios I was going to use when it struck me "Hey this isn't Access."

There you go - the whole enchilada.
Martin LissOlder than dirtCommented:
I haven't downloaded the new workbook yet, but what the problem with the wb I attached to post ID: 40207547?
Frank FreeseAuthor Commented:
I haven't tried it yet. So I'd better get to it and let you know!
Frank FreeseAuthor Commented:
That's what I needed - and got rid of all those controls. That's pretty nifty.
Frank FreeseAuthor Commented:
Great job Martin -
Thank you sir
Martin LissOlder than dirtCommented:
Your welcome. Two things, one, you can do the same type of thing with any other buttons you have, and two, the Add Year button doesn't actually add new rows but instead just changes the year in the current years.
Frank FreeseAuthor Commented:
Understood - I need to better understand what you did on Deign cell.
I've consolidated under a form command button all my macros the "Add Year".
I believe I have completed this, but only for one year. I can get to her what I have and proceed on adding other years. I truly appreciated the way we worked on bug fixes for the Statistical workbook. I'm going to ask the same here - hopefully there will be a lot less de-bugging. OK?
Martin LissOlder than dirtCommented:
OK. If you have questions about the code I added, please feel free to ask.
Frank FreeseAuthor Commented:
I would like some time to chew on it. New debugging project being opened.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.