Solved

Using VBA to place an ActiveX command button with code

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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

896 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

17 Experts available now in Live!

Get 1:1 Help Now