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.
After the command buttons have been macro created the worksheet would look like this
Each command button has code similar to this
Private Sub cmdJan14_Click()
I did find this snippet and too my surprise a command button was created.
Dim Obj As Object
Dim Code As String
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)
Obj.Name = "TestButton"
ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"
Code = "Sub ButtonTest_Click()" & vbCrLf
Code = Code & "Call Tester" & vbCrLf
Code = Code & "End Sub"
'add macro at the end of the sheet module
.insertlines .CountOfLines + 1, Code
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:
Here'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