Solved

Using VBA to place an ActiveX command button with code

Posted on 2014-07-19
13
699 Views
Last Modified: 2014-07-20
Folks,
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()
Worksheets("Jan14").Activate
Worksheets("Jan14").Range("A1").Select
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            

Sheets("Sheet1").Select            

'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
Consolidaed-with-Formulas.xlsm
0
Comment
Question by:Frank Freese
  • 7
  • 6
13 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40207444
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.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40207465
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)
    Worksheets(strSheetName).Activate
    Worksheets(strSheetName).Range("A1").Select
End If
End Sub

Open in new window

0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40207547
And here the workbook. Made a small change to the SelectionChange code.
Consolidaed-with-Formulas.xlsm
0
 

Author Comment

by:Frank Freese
ID: 40207554
Thanks
Responding to you other post from yesterday right now
I'll look at this shortly
0
 

Author Comment

by:Frank Freese
ID: 40207608
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.
Consolidaed-with-Formulas.xlsm
Append.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40207627
I haven't downloaded the new workbook yet, but what the problem with the wb I attached to post ID: 40207547?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Frank Freese
ID: 40207635
I haven't tried it yet. So I'd better get to it and let you know!
0
 

Author Comment

by:Frank Freese
ID: 40207648
That's what I needed - and got rid of all those controls. That's pretty nifty.
0
 

Author Closing Comment

by:Frank Freese
ID: 40207653
Great job Martin -
Thank you sir
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40207741
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.
0
 

Author Comment

by:Frank Freese
ID: 40207753
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40207761
OK. If you have questions about the code I added, please feel free to ask.
0
 

Author Comment

by:Frank Freese
ID: 40207782
I would like some time to chew on it. New debugging project being opened.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now