Solved

Changes to some VB script which inserts WordArt

Posted on 2008-06-20
3
555 Views
Last Modified: 2010-04-21
I have some code which inserts WordArt into excel.  I have tried to change the format of the wordart and it only applies to the first insertion.  I can see why it is doing that but do not know how to change it so that all the inserts are shaded...

I am really new to VB so any help would be much appreciated.
Thanks
 
For rw = 6 To ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row Step 20

            Set NewWordArt = ActiveSheet.Shapes.AddTextEffect( _

                PresetTextEffect:=msoTextEffect1, Text:="Generic", _

                FontName:="Arial Black", FontSize:=50, _

                FontBold:=msoFalse, FontItalic:=msoFalse, Left:=ActiveSheet.Range("B" & rw).Left, _

                Top:=ActiveSheet.Range("B" & rw).Top)

             

            With ActiveSheet.Shapes("WordArt 1").Select

                Selection.ShapeRange.Fill.Visible = msoTrue

                Selection.ShapeRange.Fill.Solid

                Selection.ShapeRange.Fill.ForeColor.SchemeColor = 22

                Selection.ShapeRange.Fill.Transparency = 0.6

                Selection.ShapeRange.Line.Weight = 0.25

                Selection.ShapeRange.Line.DashStyle = msoLineSolid

                Selection.ShapeRange.Line.Style = msoLineSingle

                Selection.ShapeRange.Line.Transparency = 1#

                Selection.ShapeRange.Line.Visible = msoTrue

                Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)

                Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

            End With

Open in new window

0
Comment
Question by:NicBrambles
3 Comments
 
LVL 4

Expert Comment

by:shekharsom
Comment Utility
use foreach statement:

following is a sample code

Dim sh As Shape

For Each sh In ActiveSheet.Shapes
    If sh.Type = msoFormControl Then
        If sh.FormControlType = xlButtonControl Then
            sh.Visible = Not sh.Visible
        End If
    End If
Next sh

change the controltype to wordart type.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
Comment Utility
Try this:

For rw = 6 To ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row Step 20

            Set newwordart = ActiveSheet.Shapes.AddTextEffect( _

                PresetTextEffect:=msoTextEffect1, Text:="Generic", _

                FontName:="Arial Black", FontSize:=50, _

                FontBold:=msoFalse, FontItalic:=msoFalse, Left:=ActiveSheet.Range("B" & rw).Left, _

                Top:=ActiveSheet.Range("B" & rw).Top)

             

            With newwordart

               With .Fill

                  .Visible = msoTrue

                  .Solid

                  .ForeColor.SchemeColor = 22

                  .Transparency = 0.6

               End With

               With .Line

                  .Weight = 0.25

                  .DashStyle = msoLineSolid

                  .Style = msoLineSingle

                  .Transparency = 1#

                  .Visible = msoTrue

                  .ForeColor.RGB = RGB(0, 0, 0)

                  .BackColor.RGB = RGB(255, 255, 255)

               End With

            End With

Open in new window

0
 

Author Closing Comment

by:NicBrambles
Comment Utility
That's perfect thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now