Solved

Using VBA to place an ActiveX command button with code

Posted on 2014-07-19
13
717 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 46

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 46

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 46

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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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 46

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
 

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 46

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 46

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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