Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cylce through buttons ((97))

    I'm looking for VBA-code to cycle through all my buttons that I've placed on the various sheets in my workbook.
    Initially this seemed simple if I used the shapes collection. However when I do that, the buttons I've placed on my charts (embedded) are not seen (and to be honest: these are the ones I'm really looking for).
    I could not yet find a way to:
    1. cycle through all my sheets (in my WB)
    then
    2. cylce through all my charts (on each sheet)
    then
    3. cylce through all my buttons (on each chart)

    [the more generic solution which would cycle me through all buttons on all sheets -incl. charts- would be better still].

    (I'm using EXCEL97 by the way)

    Appreciate any suggestions / code snippets you might have!

    Erik Jan <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cylce through buttons ((97))

    The code below might help you to set up your own program to loop through all objects on a sheet, a chart or a userform. I used different properties and methods of the object control to show you what you can do. To make it work, you need to add some objects on a sheet, some on a chart and also add a userform with the name UserForm1 to your VBAProject. Actually, the embedded chart can be considered as one of the shapes on the activesheet.

    <pre>Sub LoopThroughControls()
    Dim obj As Object
    Dim q As Integer
    For Each obj In ActiveSheet.Shapes
    MsgBox obj.Parent.Name & " / " & TypeName(obj) & " / " & obj.Name
    Next
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartArea.Select
    For Each obj In ActiveChart.Shapes
    MsgBox obj.Parent.Name & " / " & TypeName(obj) & " / " & obj.Name
    Next
    For Each obj In UserForm1.Controls
    q = obj.TabIndex
    MsgBox obj.Parent.Name & " / " & "TabIndex(" & TypeName(obj) & ") = " _
    & Str$(q) & " / " & obj.Name
    Next
    End Sub
    </pre>


    Hope this helps.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cylce through buttons ((97))

    Hans,

    Thanks, that triggered me to find the solution. Basically it is/was the fact that one needs to actually activate the chartobjects (line <font color=blue>ActiveSheet.ChartObjects(1).Activate</font color=blue>) before it works. That seemed (and still seems <img src=/S/confused.gif border=0 alt=confused width=15 height=20>) different from all the other "For Each" structures.

    Actually, at the end of my procedure, the code executes the line "<font color=blue>ActiveWorkbook.Close True, Nam</font color=blue>", in order to save & close the WB just created. If I do not add something like e.g. <font color=blue>Range("A1").Select</font color=blue> to get the focus away from the still activated chartobject, EXCEL (97) crashes <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>.

    Anyway, it works now, thanks again!

    Erik Jan

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cylce through buttons ((97))

    Don't you just love Microsoft's consistency? It is always best to not activate an object to use it in VBA code, except in those undocumented cases where the object must be activated.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cylce through buttons ((97))

    Talking about inconsistencies... I thought I was done but there's one issue left I'd like suggestions on.
    Let me summarize what I did upto now...
    My goal was to export the relevant sheets & graphs from my current Workbook application. To do this, I wanted to keep all except the VBA code and two worksheets (which only are used to set-up my calculations and do not contain results).
    1. <LI>Create new WB

      <LI>Copy relevant sheets

      <LI>Delete default sheet created when creating new WB in step 1

      <LI>Delete links in names (links were created to the original sheet as I COPIED the sheets over)

      <LI>Delete links in shapes (mostly on buttons pointing to code)

      <LI>Save the sheet (to create a disk-'version')

      <LI>Run the <font color=blue>ChangeLink</font color=blue> command, changing the remaining links (e.g. in graph series) from the original sheet to the filename of this export-version itself (which I just saved in the step before)

      <LI>Save the file again (and close)
    Pfft... <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    Anyway, here's the thing that still bugs me...
    <font color=red>the created (export) sheet is not smaller but larger than the original...</font color=red> <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

    Anyone who can tell me why and preferably how to 'fix' that...

    Erik Jan

Posting Permissions

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