Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Diagram Style Gallery (Excel 2002)

    I want to change the diagram style through style gallery in VB / VBA code (like gradient , fire, thick outline etc)

    Record macro does not work .

    How can I do this in code . Thanks in advance.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Diagram Style Gallery (Excel 2002)

    Hitesh, this time you have broght me to my knees! I got so absorbed it trying to puzzle it out, that I even forgot to go to an appointment! It dosn't appear that the Diagram Style Gallery is exposed in VBA. The way that I tried to puzzle it out was to create a pyramid diagram with the following code:
    <pre>Option Explicit

    Sub DiagramDemo()
    Dim i As Integer

    ' Must add reference to MS Word
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim wdShp As Word.Shape
    Dim wdNodes(1 To 4) As Word.DiagramNode

    ' Excel model has bugs so create a word document
    ' and build the shape there
    Set wdDoc = wdApp.Documents.Add
    wdApp.Visible = True

    ' Add the diagram
    Set wdShp = wdDoc.Shapes.AddDiagram _
    (msoDiagramPyramid, 10, 15, 400, 475)

    ' Add the top node
    Set wdNodes(1) = wdShp.DiagramNode.Children.AddNode

    ' Add child nodes
    For i = 2 To 4
    Set wdNodes(i) = wdNodes(1).AddNode
    Next

    ' Add text to the child nodes.
    For i = 1 To 4 'Inserting text in each node
    wdNodes(i).TextShape.TextFrame.TextRange.Text = Str(i)
    Next

    ' Copy the shape to Excel.
    wdDoc.Shapes.SelectAll
    wdApp.Selection.Copy
    ActiveSheet.Paste
    ActiveSheet.Shapes(1).TextFrame

    ' Quit Word.
    ' wdApp.Quit saveChanges:=False

    End Sub</pre>


    Normally I would not make the word app visible, but I wanted to see the diagram in Word also. I should have set all of the object variables at the end to Nothing, but I forgot.

    Next, I copied the Excel diagram and pasted it beside the first one. I then manually used the AutoFormat button to set the Style to "Fire".

    I then used the Watch window and MsgBox's to look at most all of the properties of the two diagrams. The one important difference was that AutoFormat property of the diagram changed from False to True, but I could not see any other useful information (like Fire!). At a lower-level, I noticed that the fill and line style of the node shapes had changed, so I tried a brute-force method of manually setting the fill for each shape. But, there are so many readonly properties, so little documentation, and so many crashes, that I have almost given up! Be sure to save you work everytime you add a line of code: I had four or five Excel crashes!

    Have fun, hope this helps a little! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Diagram Style Gallery (Excel 2002)

    Sammy ,

    You r so wonderful . Even I (who needs this very desparately) did not give so much try. It is different that it'sbeyond my capacity also. I am an ERP programmer.

    Nevertheless I will try based on your inputs . At the moment , I will ask user to change this manually to get the great look of MS diagrams.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Diagram Style Gallery (Excel 2002)

    <hr>I am an ERP programmer<hr>
    Well, programming makes me ERP sometimes, too (especially shapes & oracle). But, isn't it spelled "urp"? <img src=/S/barf.gif border=0 alt=barf width=64 height=23>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Diagram Style Gallery (Excel 2002)

    However this situation (of not finding a solution ) is faced only by those who try difficult and complex solutions. Copy cats and dumb followers never find such situations because they never make such attempts.

    I would like to thank this forum and members like you once again for helping others voluntarily.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Diagram Style Gallery (Excel 2002)

    Two more clues:

    I can get close to Fire in the Style Gallery with
    <pre>.Fill.PresetGradient PresentGradientType=msoGradientFire</pre>

    but there are too many wrong choices for the other parameters, but maybe you can find a combination that you like by trial and error.

    Also, it may be my imagination, but the Diagram methods and properties seem to be better documented over in PowerPoint.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Diagram Style Gallery (Excel 2002)

    Thanks Sammy ,

    I'll check what u have said in 1st week of Jan. Currently I am on holiday.

    Wish u a very happy and prosperous new year to come .

    Regards

    Hitesh

Posting Permissions

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