Solved

Changes to some VB script which inserts WordArt

Posted on 2008-06-20
3
568 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
ID: 21830123
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
ID: 21830797
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
ID: 31469080
That's perfect thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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