• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

How to i add code to a Command button that I created in VBA.

hello all,
so I have a button that adds a new sheet to the current work book.
it asks the user for the name of the sheet.
it thens creates the sheet as the last sheet in the work book.

along with this one the first page (sort of like an index page, it adds the name of the sheet in one cell, and in the next cell i would like to create a button that will link to that sheet.

2 things
1) how do I create the button on the same row as the sheet name was added

2) I have created the button, but I would like to assign code to this button dynamically when its created. I have the code that I manually entered for the other buttons on my page but would like to know how could I do this via vba code at the time of creation of the button
0
djcolverj
Asked:
djcolverj
  • 6
  • 5
1 Solution
 
VBClassicGuyCommented:
Going out on a limb here, but it might head you in the right direction. Can you make the button an array of buttons? If so, the first button would have Index property of 0, then use the Load command to create more buttons with increasing Indexes. Then the code IN ONE PLACE would work for all buttons, just use the Index given by the Click event to do whatever you need to do. That Index property could even be tied to the row number on the first "index page".
0
 
djcolverjAuthor Commented:
that's pretty much what I intend to do.

currently when I assign buttons, I am doing it by sheet name, which is what i ask the user to input,
so I was hoping that I could create the button just before creating the sheet.

I found this, but Its failing at the maco code part

http://en.kioskea.net/faq/sujet-1105-adding-a-vba-commandbutton-with-its-respective-the-code
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

Sub Tester()
    MsgBox "You have click on the test button"
End Sub

Open in new window

0
 
patrickabCommented:
djcolverj,

The code below is in the attached file. Press 'Create new button' on Sheet1 to start the macro - it adds a new sheets and inserts the VBA code associated with the button.

If you test it and then want to start again after you may have saved the file, remember to:

1. Delete Sheets4 onwards
2. In Sheet1 VBA module, delete all the Sub CommandButton2_Click() subs except Sub CommandButton1_Click()
3. Delete all the new buttons on Sheet1 - except 'Create new button' and 'Text Button #1"
4. Save, close and re-open the file. That resets the Sheet counter, ready for you to start again.

Hope that helps

Patrick
Option Explicit

Sub CreateButton()
Dim Obj As Object
Dim Code As String
Dim numole_obj As Long
Dim str_subname As String
Dim str_classtype As String
 
Sheets("Sheet1").Select
 
'numbers of buttons - used to make unique elements
    numole_obj = ActiveSheet.OLEObjects.Count
    
'create button
    Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
            Link:=False, DisplayAsIcon:=False, Left:=64, Top:=10 + (numole_obj * 26), Width:=100, Height:=24)

'button text
    ActiveSheet.OLEObjects(numole_obj + 1).Object.Caption = "Select Sheet #" & numole_obj + 3
    
'macro text
    str_subname = "Sub CommandButton" & numole_obj + 1 & "_Click"
    Code = str_subname & vbCrLf
    Code = Code & "Sheets(Sheets.Count).Select" & 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

'add new worksheet
Sheets.Add after:=Sheets(Sheets.Count)
With Sheets("Sheet1")
    .Select
    .Activate
End With
End Sub
 
Sub Tester()
    MsgBox "You have clicked the test button"
End Sub

In Sheet1 VBA code module, macros like this are created
by the macro listed above:

Sub CommandButton2_Click()
Sheets(Sheets.Count).Select
End Sub
Sub CommandButton3_Click()
Sheets(Sheets.Count).Select
End Sub

Open in new window

IDE-djcolverj-01.xls
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
djcolverjAuthor Commented:
Hey patrickab,
I am trying to change your code around to work for my situation,

I have my user enter a name that is used for the sheet that is created in the work book. and at the end, when it come time to creat the work sheet, I am copying it from a template that I had made located in the same workbook.

is there a way to pass this name into the macro that is created.

for example
say I have the user just enter the sheet number
n = input box ("enter sheet number to go to")

lets say create a string that will have the sheet name
m = "Sheet" & n

now how to I pass m to the macro test.

so far I havne not been successful in passing this value to the macro created in your excel file.

on another note:
I tried to create the same code in my file,
I got it to create the button but with no code assigned to it..
0
 
patrickabCommented:
djcolverj,

I hope I have understood your requirements correctly. Anyhow, give it a try and see how you get on with the updated macro in the attached file.

Patrick
Sub CreateButton()
Dim Obj As Object
Dim Code As String
Dim numole_obj As Long
Dim str_subname As String
Dim str_classtype As String
Dim str_wksname As String

Application.ScreenUpdating = False

Sheets("Sheet1").Select

'numbers of buttons - used to make unique elements
numole_obj = ActiveSheet.OLEObjects.Count

'create button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Link:=False, DisplayAsIcon:=False, Left:=64, Top:=10 + (numole_obj * 26), Width:=100, Height:=24)

'enter worksheet name
str_wksname = InputBox("Enter name of worksheet", "Worksheet name?")

'button text
ActiveSheet.OLEObjects(numole_obj + 1).Object.Caption = "Select Sheet(""" & str_wksname & """)"

'macro text
str_subname = "Sub CommandButton" & numole_obj + 1 & "_Click"
Code = str_subname & vbCrLf
Code = Code & "Sheets(""" & str_wksname & """).Select" & 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

'add new worksheet
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = str_wksname
Sheets("Template").UsedRange.Copy ActiveSheet.[a1]

'copy Template to new
With Sheets("Sheet1")
    .Select
    .Activate
End With

Application.ScreenUpdating = True

End Sub

Open in new window

IDE-djcolverj-02.xls
0
 
djcolverjAuthor Commented:
Hey patrickab,
first thank you, this is exactly where I am going.

on your excel spread sheet that you sent, the code works fine. but when I add it on to mine, i get it to add the button but the code does not get written for the button..

also, I realized that I cant change the font color of the text that appears on the command button, unless im mistaken. how would I go about just adding a normal form button in the code instead of the control button
thanks
cheers
0
 
patrickabCommented:
djcolverj,

>on your excel spread sheet that you sent, the code works fine. but when I add it on to mine, i get it to add the button but the code does not get written for the button..

Please upload your file so that I can include all the VBA in the right places and make it all work. If needs be provide a small sample of anonymous data.

Patrick
0
 
djcolverjAuthor Commented:
hi Patrickab,
here is a file that consists of the project status page, template page and the sheet1.
sheet1 contains some info that is used through out the book, mainly colors and options

project status sheet is where I would like to the buttons to be create. on the top right you will see the button for adding a new COR/ Project
then it was ask you for a COR number enter anything, I do not have a check to see if the cor number entered exhists. then it will ask for the name of the project, date it started and a link to a file(specification)
it then crates a tab with the entered number(it will show up as COR#xxxx, x being the numbers you entered) the date n names get added to the newly created sheet.

I am a noob at this, but this is what I have done so far. It mite not look pretty but it works... :)

on another note, I have a weird issue, say after you added a new project and all goes well... I delete the just created project from the status page and tab and then try to add a new project, for some reason it doesn't pass the tab name... but if I shut the workbook down and try again it works.. I must not be resetting something correctly but i do not know what it is..
thanks again
Book2.xls
0
 
patrickabCommented:
djcolverj,

Thanks for uploading your file. Unfortunately, even after having read several times what you've written I do not know what you want me to do now. Do please explain - thanks.

Patirck
0
 
patrickabCommented:
djcolverj,

I have done my best to implement what I believe you want. It's in the attached file. To add worksheets, create a button and write the associated VBA automatically, press the button on the 'Project Status' sheet located at the top of column G entitled 'Insert New Sheet'. The VBA code for that is below. I have assumed that you would be entering the COR numbers for the worksheet names.

Patrick
Sub CreateButton()
Dim Obj As Object
Dim Code As String
Dim numole_obj As Long
Dim str_subname As String
Dim str_classtype As String
Dim str_wksname As String

With Sheets("Project Status")
    .Activate
    .Select
End With

'numbers of buttons - used to make unique elements
numole_obj = ActiveSheet.OLEObjects.Count

'create butt on
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Link:=False, DisplayAsIcon:=False, Left:=330, Top:=95 + (numole_obj * 21.85), Width:=110, Height:=18)

'enter worksheet name
str_wksname = InputBox("Enter name of worksheet", "Worksheet name?")

'button text
ActiveSheet.OLEObjects(numole_obj + 1).Object.Caption = "Select Sheet " & """" & str_wksname & """"
ActiveSheet.OLEObjects(numole_obj + 1).Object.Font.Size = 10

'add new worksheet
Sheets.Add after:=Sheets(Sheets.Count)
'name new worksheet
ActiveSheet.Name = str_wksname
'copy Template to new worksheet
Sheets("Template1").UsedRange.Copy ActiveSheet.[a1]
With ActiveSheet
    .Cells.Columns.EntireColumn.AutoFit
    .[F1] = "COR#" & Mid(str_wksname, 4, 4)
End With

'macro text
str_subname = "Private Sub CommandButton" & numole_obj + 1 & "_Click()"
Code = str_subname & vbCrLf
Code = Code & "Sheets(" & """" & str_wksname & """" & ").Select" & vbCrLf
Code = Code & "End Sub" & vbCrLf

Sheets("Project Status").Activate
'add macro at the end of the sheet module
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Index).CodeModule
    .InsertLines .Countoflines + 1, Code
End With

End Sub

Open in new window

djcolverj-04.xls
0
 
djcolverjAuthor Commented:
sorry for the delay, had more project fall on my desk
0
 
patrickabCommented:
djcolverj - Thanks for the grade - Patrick
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now