Consulting

Results 1 to 19 of 19

Thread: recursively add button and code

  1. #1
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location

    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
    Last edited by trpltongue; 02-05-2007 at 11:11 AM.

  2. #2
    VBAX Contributor moa's Avatar
    Joined
    Nov 2006
    Posts
    177
    Location
    Might have something to do with declaring variables in the for loop...
    Glen

  3. #3
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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

  6. #6
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Why not use an addin or a menu toolbar that works on the activesheet? Creating controls programmatically can be problematic at best...
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  7. #7
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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

  8. #8
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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.

  9. #9
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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
    Last edited by trpltongue; 02-05-2007 at 11:12 AM.

  10. #10
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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?

  11. #11
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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

  12. #12
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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.

  13. #13
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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

  14. #14
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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.

  15. #15
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  16. #16
    VBAX Regular
    Joined
    Feb 2007
    Location
    Houston
    Posts
    8
    Location
    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

  17. #17
    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]

  18. #18
    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

  19. #19
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •