trpltongue
02-05-2007, 09:32 AM
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:
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
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:
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
Any help or ideas would be greatly appreciated!
Russell
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:
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
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:
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
Any help or ideas would be greatly appreciated!
Russell