recursively add button and code

Hello all!

I have a workbook with mulitple sheets. Each sheet represents a "case" and each case has multiple variables in column format (i.e. date, cum production, etc). I want to create a worksheet with a graph that allows the user to choose which variables and cases they want to see on the graph, and furthermore, which axis each variable should be on. I have been sucessful in creating all of this, except changing the axis of the variables. On Sheet 1 I have a list of all the cases and variables, each with a checkbox next to it. The user simply checks the case(s) and variable(s) they want shown on the graph. What I'd like to do, is add a command button next to each variable that enables me to change the axis of that variable. Of course, the number of variables and cases is dynamic so all of this has to be done programmatically. I have come across this neat bit of code for adding a commandbutton and code for it:

[vba]
Sub AddButtonAndCode()
' Declare variables
Dim i As Long, Hght As Long
Dim Name As String, NName As String
' Set the button properties
i = 0
Hght = 305.25
' Set the name for the button
NName = "cmdAction" & i
' Test if there is a button already and if so, increment its name
For Each OLEObject In ActiveSheet.OLEObjects
If Left(OLEObject.Name, 9) = "cmdAction" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
If Name >= i Then
i = Name + 1
End If
NName = "cmdAction" & i
Hght = Hght + 27
End If
Next
' Add button
Dim myCmdObj As OLEObject, N%
Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
Width:=202.5, Height:=26.25)
' Define buttons name
myCmdObj.Name = NName
' Define buttons caption
myCmdObj.Object.Caption = "Click for action"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox(" & """" & "Button Clicked!" & """" & " & vbCrLf &" & _
"""" & "Put your code here!" & """" & " & vbCrLf & " & """" & "This is " & """" & _
"& " & """" & NName & """" & ")"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
Set myCmdObj = Nothing
End Sub

[/vba]

If I run the code avove by itself a button is created without issue. I can run the code individually to create multiple buttons without problem. But if I add a For Next statement (as shown below) to do this bit of code more than 1 time, Excel crashes without any error statement:

[vba]
Sub AddButtonAndCode()
For j = 1 to 4
' Declare variables
Dim i As Long, Hght As Long
Dim Name As String, NName As String
' Set the button properties
i = 0
Hght = 305.25
' Set the name for the button
NName = "cmdAction" & i
' Test if there is a button already and if so, increment its name
For Each OLEObject In ActiveSheet.OLEObjects
If Left(OLEObject.Name, 9) = "cmdAction" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
If Name >= i Then
i = Name + 1
End If
NName = "cmdAction" & i
Hght = Hght + 27
End If
Next
' Add button
Dim myCmdObj As OLEObject, N%
Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
Width:=202.5, Height:=26.25)
' Define buttons name
myCmdObj.Name = NName
' Define buttons caption
myCmdObj.Object.Caption = "Click for action"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox(" & """" & "Button Clicked!" & """" & " & vbCrLf &" & _
"""" & "Put your code here!" & """" & " & vbCrLf & " & """" & "This is " & """" & _
"& " & """" & NName & """" & ")"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
Set myCmdObj = Nothing
Next
End Sub

[/vba]

Any help or ideas would be greatly appreciated!

Russell

Might have something to do with declaring variables in the for loop...

Unfortunately that it doesn't look like that's the problem.

I tried the following code with the same results:

[VBA]Sub AddButtonAndCode()

' Declare variables
Dim i As Long, Hght As Long
Dim Name As String, NName As String
Dim myCmdObj As OLEObject, N%

For j = 1 To 4

' Set the button properties

i = 0
Hght = 305.25
' Set the name for the button
NName = "cmdAction" & i
' Test if there is a button already and if so, increment its name
For Each OLEObject In ActiveSheet.OLEObjects
If Left(OLEObject.Name, 9) = "cmdAction" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
If Name >= i Then
i = Name + 1
End If
NName = "cmdAction" & i
Hght = Hght + 27
End If
Next
' Add button

Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=Hght, _
Width:=202.5, Height:=26.25)
' Define buttons name
myCmdObj.Name = NName
' Define buttons caption
myCmdObj.Object.Caption = "Click for action"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox(" & """" & "Button Clicked!" & """" & " & vbCrLf &" & _
"""" & "Put your code here!" & """" & " & vbCrLf & " & """" & "This is " & """" & _
"& " & """" & NName & """" & ")"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
Set myCmdObj = Nothing
Next
End Sub[/VBA]

Thanks for the idea though :)

Russell

Seems like a lot of work and a lot of room for problems...why not try a simple listbox to navigate between sheets?
see attached

Lucas,

That's a good solution for this particular problem, but I'm actually just using this as a testing ground for a more advanced problem. If you'll indulge me, I can explain a bit further.

I have a workbook with a macro button residing on sheet1. Users add case results (date, cum production, etc.) into a new worksheet for each case. Then they run a macro on sheet1 which creates (on Sheet1) a list of all the cases with a checkbox next to the name of the case, as well as a list of all the variables with a checkbox next to each variable. Finally, an empty chart is created. The user then uses the checkbox to choose which cases and variables are plotted on the chart.

This part works fine. However, now I want to add a commandbox next to the variable to change the axis that the variable is plotted on. To do this, I need to be able to create the commandbutton, and assign it some code so that I can manipulate the correct series on the chart.

So your solution is perfect for navigating between sheets but unfortunately I can't adapt it to changing the axis of selected series on a chart.

Thanks!

Russell

Why not use an addin or a menu toolbar that works on the activesheet? Creating controls programmatically can be problematic at best...

lucas,

I'm definitely open to ideas, but the spreadsheet needs to be self contained because it is distributed to multiple folks who are not at all excel savy, nor are they capable of installing an addin :) That's why I want to make it as easy as possible for them.

Russell

Russell

Why do you need a button for each sheet?

I would suggest you take a different approach.

What that might be I'm not 100% sure as it's not 100% clear, to me anyway, what you are doing.

PS Programming to the VBE is possible but it can be tricky and have security issues.

Norie,

I tried to clear up my intentions a bit in post 5, basically I have a workbook with mulitple sheets. Each sheet represents a "case" and each case has multiple variables in column format (i.e. date, cum production, etc). I want to create a worksheet with a graph that allows the user to choose which variables and cases they want to see on the graph, and furthermore, which axis each variable should be on. I have been sucessful in creating all of this, except changing the axis of the variables. On Sheet 1 I have a list of all the cases and variables, each with a checkbox next to it. The user simply checks the case(s) and variable(s) they want shown on the graph. What I'd like to do, is add a command button next to each variable that enables me to change the axis of that variable. Of course, the number of variables and cases is dynamic so all of this has to be done programmatically.

If there is a better way, I'm all ears :)

Russell

Russell

I still don't see why you need buttons, couldn't you just let the user put a value in the cell next to the sheet/case name.

By the way are you also progrmatically adding the checkboxes?

Norie,

Yes, everything is programatically created. I need to use the button so that I can activate a macro to change the series axis. Simply changing a value in the spreadsheet won't change the series axis, will it?

I would attach the spreadsheet, but it's 4MB in size.

Russell

Russell

You don't need individual buttons, or need to create buttons programatically as far as I can see.

You could have a single button that does what you want by searching down a column and checking for values.

PS If you want to attach try zipping the file.

PPS You say this is going to be used by non-savvy users?

Are they savvy enough to turn on Programmatic access to the VBA?

Because they'll need to.:)

Norie,

I had thought about access issues and was planning on including a msgbox that tells users how to enable programatic access.

I hadn't thought about using an "update" button to check a column of data. I could probably work that in.

Russell

Russell

It's certainly the sort of approach I would take.

With lots of buttons and checkboxes you'll more than likely end up with a rather large workbook.

Plus you'll need a different sub for each button as far as I can see.

Now that might be simple but I can't tell because I don't know what code you want behind each button.:)

Rather than a command button, could you use an option group with the default axis already selected? Your existing macro (to create the chart) could then pickup the desired axis from the option selected, rather than the user having to run a separate macro to change the axis.

Well,

After much work I gave up on trying to write the code to the button via VBA. Instead my macro now creates the chart with all series on the primary axis, then it creates a checkbox for each variable, links the checkbox to a hidden cell, and assigns a "check and change axis" macro the on action. In addition I add some code in a cell behind the checkbox to identify the current axis based on the linked cell for that checkbox.

My check and change axis macro simply reads in the true/false state of all the checkboxes and changes axis accordingly.

Not as elegant as I was hoping, but it works :)

So I guess for now, I'll not title this problem solved as I didn't ever find a way to add the code via VBA.

Russell

Try this
[VBA]
for

call addbuttonsandcode()
next
[/VBA]

pass to addbuttonsandcode procedure button name, left, top, everything you need

here is my code
[VBA]
Sub makebuttons()
Dim c As Range, d As String
'finding two not empty cells in the first row
For Each c In Foglio1.Rows(1).Cells
If c.Value <> "" Then
If d <> "" Then
Me.Names.Add c.Value, RefersToR1C1:="=" & Foglio1.Name & "!C" & Trim((c.Column - 1)) & ":C" & Trim((c.Column - 1 + 4))
Call AddButtonAndCode(Foglio1.Range(c))
End If
d = c.Value
Else
d = ""
End If
Next
End Sub

Sub AddButtonAndCode(c As Range)
' Declare variables

Dim i As Long, lft As Long
Dim Name As String, NName As String
Dim obj As OLEObject
Dim myCmdObj As OLEObject, N%


' Set the button properties
lft = c.Left + c.Width - 70
' Set the name for the button
NName = c.Cells(1, 2)
' Test if there is a button already and if so, exit sub
For Each obj In ActiveSheet.OLEObjects
If obj.Left = lft Then
Exit Sub
End If
Next
' Add start button
Set myCmdObj = Foglio1.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=lft, Top:=5, Width:=70, Height:=20.25)
' Define buttons name
myCmdObj.Name = NName & "start"
' Define buttons caption
myCmdObj.Object.Caption = "Start"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(Foglio1.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & myCmdObj.Name & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "update_now(foglio1.Range(" & """" & NName & """" & "))"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With

' Add stop button
Set myCmdObj = Foglio1.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=lft, Top:=26, Width:=70, Height:=20.25)
' Define buttons name
myCmdObj.Name = NName & "stop"
' Define buttons caption
myCmdObj.Object.Caption = "Stop"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(Foglio1.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & myCmdObj.Name & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "update_now(foglio1.Range(" & """" & NName & """" & "))"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With

[/VBA]

The code I wrote also has the error
it is in strings
[VBA]For Each obj In ActiveSheet.OLEObjects
If obj.Left = lft Then
Exit Sub
End If
Next[/VBA]
When the second cycle starts with this code we try to use OLEObjects that are created by current macro. If to comment this lines everything goes OK

Quote:

---

Originally Posted by trpltongue

...If I run the code avove by itself a button is created without issue. I can run the code individually to create multiple buttons without problem. But if I add a For Next statement (as shown below) to do this bit of code more than 1 time, Excel crashes without any error statement:...

---

Without looking too deeply at your code... I don't see anything to delete the previously created button and code, or to check if it already exists, so that's bound to create problems. I gave an example for this in the KB as a "how to" but I certainly wouldn't recommend that approach (it's also not a good example either).

If you're still determined, as far as access is concerned, you could call something like this from a Workbook_Open procedure...

[vba]
Private Sub AddRefsIfAccessAllowed()

Dim Response As VbMsgBoxResult

'Test to ensure access is allowed
If Application.Version > 9 Then
Dim VisualBasicProject As Object
On Error Resume Next
Set VisualBasicProject = ActiveWorkbook.VBproject
If Not Err.Number = 0 Then
Response = Msgbox("Your current security settings do not allow the code in this workbook " & vbNewLine & _
" to work as designed and you will get some error messages." & vbNewLine & vbNewLine & _
"To allow the code to function correctly and without errors you need" & vbNewLine & _
" to change your security setting as follows:" & vbNewLine & vbNewLine & _
" 1. Select Tools - Macro - Security to show the security dialog" & vbNewLine & _
" 2. Click the 'Trusted Sources' tab" & vbNewLine & _
" 3. Place a checkmark next to 'Trust Access to Visual Basic Project'" & vbNewLine & _
" 4. Save - then Close and re-open the workbook" & vbNewLine & vbNewLine & _
"Do you want the security dialog shown now?", vbYesNoCancel + vbCritical)
If Response = vbYes Then Application.CommandBars("Macro").Controls("Security...").Execute
Exit Sub
End If
End If

'Call AddReference

End Sub[/vba]