vba and animations

Hi,

I am using VBA in PowerPoint 2003.
I'm new to VBA and I find that looking for informations about animation is 
quite difficult. When I want to know what informations to give when I want to 
insert a shape, it's quite easy because I can record a macro and then use 
this information. Unfortunately, it's not possible for animation. Do you know 
of any good tutorial about this ? For example, I would like to know how to 
lenghten the path if I'm using msoAnimEffectPathRight or how to include a 
sound with it.

On another subject, there is still no answer to my question here and I 
haven't found an answer by myself :

http://www.microsoft.com/office/community/en-us/default.mspx?pg=3&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.powerpoint&fltr=

Thank you for your help. It is very appreciated.

Christine./
Exemple of code I am using, exploring userform and animations :

Private Sub CheckBox1_Click()
    etoile = True
End Sub

Private Sub CheckBox2_Click()
    carre = True
End Sub

Private Sub CheckBox3_Click()
    cercle = True
End Sub

Private Sub CommandButton1_Click()
    Me.hide
    If etoile = True Then
        Set formeEtoile = 
ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShape5pointStar, 67.75, 60.12, 97.38, 84.75)
        With formeEtoile
        .Fill.ForeColor.RGB = RGB(255, 255, 0)
        .Line.Weight = 4#
        .Line.ForeColor.RGB = RGB(255, 102, 0)
        .Line.BackColor.RGB = RGB(255, 255, 255)
    End With
    End If
    If carre = True Then
        Set formeCarre = 
ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShapeRectangle, 67.75, 191.38, 88.88, 78)
        With formeCarre
        .Fill.ForeColor.SchemeColor = ppAccent2
        .Fill.Transparency = 0#
        .Line.Weight = 4#
        .Line.ForeColor.RGB = RGB(128, 128, 128)
        .Line.BackColor.RGB = RGB(255, 255, 255)
    End With
    End If
    If cercle = True Then
        Set formeCercle = 
ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShapeOval, 
67.75, 401.5, 122.75, 122.75)
        With formeCercle
        .Fill.ForeColor.RGB = RGB(255, 0, 255)
        .Line.Weight = 4#
        .Line.ForeColor.RGB = RGB(153, 204, 0)
        .Line.BackColor.RGB = RGB(255, 255, 255)
    End With
    End If
    If etoile = True And trajet = True Then
        With 
ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeEtoile, effectid:=msoAnimEffectPathRight)
            .Timing.SmoothEnd = False
            .Timing.SmoothStart = False
            .Timing.Speed = 1
            .Timing.TriggerType = msoAnimTriggerAfterPrevious
            .Timing.TriggerDelayTime = 1
        End With
    End If
    If etoile = True And emphase = True Then
        With 
ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeEtoile, effectid:=msoAnimEffectSpin)
            .Timing.Duration = 2
            .Timing.TriggerType = msoAnimTriggerAfterPrevious
            .Timing.TriggerDelayTime = 1
        End With
    End If
    If carre = True And trajet = True Then
        With 
ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCarre, effectid:=msoAnimEffectPathRight)
            .Timing.SmoothEnd = False
            .Timing.SmoothStart = False
            .Timing.Speed = 1
            .Timing.TriggerType = msoAnimTriggerAfterPrevious
            .Timing.TriggerDelayTime = 1
        End With
    End If
    If carre = True And emphase = True Then
        With 
ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCarre, effectid:=msoAnimEffectFlicker)
            .Timing.Duration = 2
            .Timing.TriggerType = msoAnimTriggerAfterPrevious
            .Timing.TriggerDelayTime = 1
        End With
    End If
    If cercle = True And trajet = True Then
        With 
ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCercle, effectid:=msoAnimEffectPathRight)
            .Timing.SmoothEnd = False
            .Timing.SmoothStart = False
            .Timing.Speed = 1
            .Timing.TriggerType = msoAnimTriggerAfterPrevious
            .Timing.TriggerDelayTime = 1
        End With
    End If
    If cercle = True And emphase = True Then
        With 
ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCercle, effectid:=msoAnimEffectWave)
            .Timing.Duration = 2
            .Timing.TriggerType = msoAnimTriggerAfterPrevious
            .Timing.TriggerDelayTime = 1
        End With
    End If
End Sub

Private Sub OptionButton1_Click()
    trajet = True
End Sub

Private Sub OptionButton2_Click()
    emphase = True
End Sub



0
Utf
6/4/2010 4:14:11 PM
powerpoint 2011 articles. 0 followers. Follow

2 Replies
7464 Views

Similar Articles

[PageSpeed] 19

Unfortunately, there is not much available on this topic. I think the 
most you are going to find is on Shyam's site:

http://skp.mvps.org/ppttimeline1.htm

I didn't discuss animations in my book about PowerPoint and VBA.

--David

-- 
David M. Marcovitz
Microsoft PowerPoint MVP
Associate Professor, Loyola University Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/


On 6/4/10 12:14 PM, Christine Fournier wrote:
> Hi,
>
> I am using VBA in PowerPoint 2003.
> I'm new to VBA and I find that looking for informations about animation is
> quite difficult. When I want to know what informations to give when I want to
> insert a shape, it's quite easy because I can record a macro and then use
> this information. Unfortunately, it's not possible for animation. Do you know
> of any good tutorial about this ? For example, I would like to know how to
> lenghten the path if I'm using msoAnimEffectPathRight or how to include a
> sound with it.
>
> On another subject, there is still no answer to my question here and I
> haven't found an answer by myself :
>
> http://www.microsoft.com/office/community/en-us/default.mspx?pg=3&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.powerpoint&fltr=
>
> Thank you for your help. It is very appreciated.
>
> Christine./
> Exemple of code I am using, exploring userform and animations :
>
> Private Sub CheckBox1_Click()
>      etoile = True
> End Sub
>
> Private Sub CheckBox2_Click()
>      carre = True
> End Sub
>
> Private Sub CheckBox3_Click()
>      cercle = True
> End Sub
>
> Private Sub CommandButton1_Click()
>      Me.hide
>      If etoile = True Then
>          Set formeEtoile =
> ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShape5pointStar, 67.75, 60.12, 97.38, 84.75)
>          With formeEtoile
>          .Fill.ForeColor.RGB = RGB(255, 255, 0)
>          .Line.Weight = 4#
>          .Line.ForeColor.RGB = RGB(255, 102, 0)
>          .Line.BackColor.RGB = RGB(255, 255, 255)
>      End With
>      End If
>      If carre = True Then
>          Set formeCarre =
> ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShapeRectangle, 67.75, 191.38, 88.88, 78)
>          With formeCarre
>          .Fill.ForeColor.SchemeColor = ppAccent2
>          .Fill.Transparency = 0#
>          .Line.Weight = 4#
>          .Line.ForeColor.RGB = RGB(128, 128, 128)
>          .Line.BackColor.RGB = RGB(255, 255, 255)
>      End With
>      End If
>      If cercle = True Then
>          Set formeCercle =
> ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShapeOval,
> 67.75, 401.5, 122.75, 122.75)
>          With formeCercle
>          .Fill.ForeColor.RGB = RGB(255, 0, 255)
>          .Line.Weight = 4#
>          .Line.ForeColor.RGB = RGB(153, 204, 0)
>          .Line.BackColor.RGB = RGB(255, 255, 255)
>      End With
>      End If
>      If etoile = True And trajet = True Then
>          With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeEtoile, effectid:=msoAnimEffectPathRight)
>              .Timing.SmoothEnd = False
>              .Timing.SmoothStart = False
>              .Timing.Speed = 1
>              .Timing.TriggerType = msoAnimTriggerAfterPrevious
>              .Timing.TriggerDelayTime = 1
>          End With
>      End If
>      If etoile = True And emphase = True Then
>          With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeEtoile, effectid:=msoAnimEffectSpin)
>              .Timing.Duration = 2
>              .Timing.TriggerType = msoAnimTriggerAfterPrevious
>              .Timing.TriggerDelayTime = 1
>          End With
>      End If
>      If carre = True And trajet = True Then
>          With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCarre, effectid:=msoAnimEffectPathRight)
>              .Timing.SmoothEnd = False
>              .Timing.SmoothStart = False
>              .Timing.Speed = 1
>              .Timing.TriggerType = msoAnimTriggerAfterPrevious
>              .Timing.TriggerDelayTime = 1
>          End With
>      End If
>      If carre = True And emphase = True Then
>          With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCarre, effectid:=msoAnimEffectFlicker)
>              .Timing.Duration = 2
>              .Timing.TriggerType = msoAnimTriggerAfterPrevious
>              .Timing.TriggerDelayTime = 1
>          End With
>      End If
>      If cercle = True And trajet = True Then
>          With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCercle, effectid:=msoAnimEffectPathRight)
>              .Timing.SmoothEnd = False
>              .Timing.SmoothStart = False
>              .Timing.Speed = 1
>              .Timing.TriggerType = msoAnimTriggerAfterPrevious
>              .Timing.TriggerDelayTime = 1
>          End With
>      End If
>      If cercle = True And emphase = True Then
>          With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCercle, effectid:=msoAnimEffectWave)
>              .Timing.Duration = 2
>              .Timing.TriggerType = msoAnimTriggerAfterPrevious
>              .Timing.TriggerDelayTime = 1
>          End With
>      End If
> End Sub
>
> Private Sub OptionButton1_Click()
>      trajet = True
> End Sub
>
> Private Sub OptionButton2_Click()
>      emphase = True
> End Sub
>
>
>

-1
David
6/4/2010 6:08:58 PM
Christine,
You can change the length of the motion path animation by editing the VML 
information of the motion. Take a look at the string returned by the Path 
property and parse the information from there.
You can read more about VML. Basically the motion string is a set of space 
delimited coordinates representing lines or bezier curves. The dimensions 
are relative to the size of the slide.

Sub AddMotionPath()

   Dim shpNew As Shape
   Dim effNew As Effect
   Dim aniMotion As AnimationBehavior

   Set shpNew = ActivePresentation.Slides(1).Shapes _
       .AddShape(Type:=msoShape5pointStar, Left:=0, _
       Top:=0, Width:=100, Height:=100)
   Set effNew = ActivePresentation.Slides(1).TimeLine.MainSequence _
       .AddEffect(Shape:=shpNew, effectId:=msoAnimEffectCustom, _
       Trigger:=msoAnimTriggerWithPrevious)
   Set aniMotion = effNew.Behaviors.Add(msoAnimTypeMotion)

   With aniMotion.MotionEffect
      .Path = "M 0 0  L -0.25 0  E"
   End With

End Sub

Regards,
Shyam Pillai

Image Importer Wizard
http://skp.mvps.org/iiw.htm


> Hi there,


Regards,
Shyam Pillai

Animation Carbon
http://www.animationcarbon.com


"Christine Fournier" <ChristineFournier@discussions.microsoft.com> wrote in 
message news:BDFBC628-FA5A-40A6-9167-F3A42BF3AF5D@microsoft.com...
> Hi,
>
> I am using VBA in PowerPoint 2003.
> I'm new to VBA and I find that looking for informations about animation is
> quite difficult. When I want to know what informations to give when I want 
> to
> insert a shape, it's quite easy because I can record a macro and then use
> this information. Unfortunately, it's not possible for animation. Do you 
> know
> of any good tutorial about this ? For example, I would like to know how to
> lenghten the path if I'm using msoAnimEffectPathRight or how to include a
> sound with it.
>
> On another subject, there is still no answer to my question here and I
> haven't found an answer by myself :
>
> http://www.microsoft.com/office/community/en-us/default.mspx?pg=3&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.powerpoint&fltr=
>
> Thank you for your help. It is very appreciated.
>
> Christine./
> Exemple of code I am using, exploring userform and animations :
>
> Private Sub CheckBox1_Click()
>    etoile = True
> End Sub
>
> Private Sub CheckBox2_Click()
>    carre = True
> End Sub
>
> Private Sub CheckBox3_Click()
>    cercle = True
> End Sub
>
> Private Sub CommandButton1_Click()
>    Me.hide
>    If etoile = True Then
>        Set formeEtoile =
> ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShape5pointStar, 
> 67.75, 60.12, 97.38, 84.75)
>        With formeEtoile
>        .Fill.ForeColor.RGB = RGB(255, 255, 0)
>        .Line.Weight = 4#
>        .Line.ForeColor.RGB = RGB(255, 102, 0)
>        .Line.BackColor.RGB = RGB(255, 255, 255)
>    End With
>    End If
>    If carre = True Then
>        Set formeCarre =
> ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShapeRectangle, 
> 67.75, 191.38, 88.88, 78)
>        With formeCarre
>        .Fill.ForeColor.SchemeColor = ppAccent2
>        .Fill.Transparency = 0#
>        .Line.Weight = 4#
>        .Line.ForeColor.RGB = RGB(128, 128, 128)
>        .Line.BackColor.RGB = RGB(255, 255, 255)
>    End With
>    End If
>    If cercle = True Then
>        Set formeCercle =
> ActivePresentation.SlideShowWindow.View.Slide.Shapes.AddShape(msoShapeOval,
> 67.75, 401.5, 122.75, 122.75)
>        With formeCercle
>        .Fill.ForeColor.RGB = RGB(255, 0, 255)
>        .Line.Weight = 4#
>        .Line.ForeColor.RGB = RGB(153, 204, 0)
>        .Line.BackColor.RGB = RGB(255, 255, 255)
>    End With
>    End If
>    If etoile = True And trajet = True Then
>        With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeEtoile, 
> effectid:=msoAnimEffectPathRight)
>            .Timing.SmoothEnd = False
>            .Timing.SmoothStart = False
>            .Timing.Speed = 1
>            .Timing.TriggerType = msoAnimTriggerAfterPrevious
>            .Timing.TriggerDelayTime = 1
>        End With
>    End If
>    If etoile = True And emphase = True Then
>        With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeEtoile, 
> effectid:=msoAnimEffectSpin)
>            .Timing.Duration = 2
>            .Timing.TriggerType = msoAnimTriggerAfterPrevious
>            .Timing.TriggerDelayTime = 1
>        End With
>    End If
>    If carre = True And trajet = True Then
>        With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCarre, 
> effectid:=msoAnimEffectPathRight)
>            .Timing.SmoothEnd = False
>            .Timing.SmoothStart = False
>            .Timing.Speed = 1
>            .Timing.TriggerType = msoAnimTriggerAfterPrevious
>            .Timing.TriggerDelayTime = 1
>        End With
>    End If
>    If carre = True And emphase = True Then
>        With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCarre, 
> effectid:=msoAnimEffectFlicker)
>            .Timing.Duration = 2
>            .Timing.TriggerType = msoAnimTriggerAfterPrevious
>            .Timing.TriggerDelayTime = 1
>        End With
>    End If
>    If cercle = True And trajet = True Then
>        With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCercle, 
> effectid:=msoAnimEffectPathRight)
>            .Timing.SmoothEnd = False
>            .Timing.SmoothStart = False
>            .Timing.Speed = 1
>            .Timing.TriggerType = msoAnimTriggerAfterPrevious
>            .Timing.TriggerDelayTime = 1
>        End With
>    End If
>    If cercle = True And emphase = True Then
>        With
> ActivePresentation.SlideShowWindow.View.Slide.TimeLine.MainSequence.AddEffect(Shape:=formeCercle, 
> effectid:=msoAnimEffectWave)
>            .Timing.Duration = 2
>            .Timing.TriggerType = msoAnimTriggerAfterPrevious
>            .Timing.TriggerDelayTime = 1
>        End With
>    End If
> End Sub
>
> Private Sub OptionButton1_Click()
>    trajet = True
> End Sub
>
> Private Sub OptionButton2_Click()
>    emphase = True
> End Sub
>
>
> 
0
Shyam
6/5/2010 5:56:54 PM
Reply:

Similar Artilces:

CountIf with VBA
Hi all, I am a newbie in excel and having a problem with countif function. Currently in the actual worksheet I was unable to set a CountIf formula with multiple criteria. Can we set it with excel VBA? Or just like using Dlookup function to find excatly match row contents. Please advise. Rgds, Shiro More info with data, layout and before and after examples of what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "shiro" <shiro@play.com> wrote in message news:eDMJNbQsIHA.5576@TK2MSFTNGP02.phx.gbl... > Hi all, > I am a newbie i...

Help needed with Multipage UserForm (VBA)
Bear with me folks, I'm a newbie when it comes to VBA: In John Walkenbach's book "Excel 2002 Power Programming with VBA," there is an example of a multipage UserForm (Wizard) where the data is inserted in contiguous cells of a (single) active sheet. I'm looking for the syntax (or better still, an example) for inserting the data inputs in specific cells in different worksheets, in a multi-worksheet workbook. Any help will be appreciated. Not quite sure what you're looking for, but maybe: Option Explicit Private Sub CommandButton1_Click() With ActiveWorkboo...

Excel VBA
I would like to create a function or macro to automatically enter th date when a number in an adjacent cell is entered. I am new to VBA and haven't been able to figure this out yet. An ideas? TH -- Message posted from http://www.ExcelForum.com Use this worksheet change event idea if isnumberic(target) then target.offset(0,1)=date -- Don Guillett SalesAid Software donaldb@281.com "jbchrist >" <<jbchrist.19e9da@excelforum-nospam.com> wrote in message news:jbchrist.19e9da@excelforum-nospam.com... > I would like to create a function or macro to automatically e...

Create Excel file with VBA in Access
Here are 2 examples that I have found to create an Excel file with VBA. It is great for exporting data to Excel with the format that you want. Michael '---------------------------------------------------------------- Sub Test_Excel() Dim MyXL As Object 'Excel Application Object Dim XL_File As String Dim SheetName As String 'Dim xlBook As Excel.Workbook XL_File = "C:\Excel_Documents\test.xls" SheetName = "New Sheet Name" 'Create the Excel Application Object. Set MyXL = CreateObject("Excel.Application") 'Create new Excel Workbook MyXL....

First Excel and VBA
On A spreadsheet, I would like A4 ( Dates Missed) will show all dates that were entered and A5 would sum the number of days missed from the dates in A4. Can you assist? Hi how would you enter the dates in cell A4 (separated with a coma?) -- Regards Frank Kabel Frankfurt, Germany Gazabou wrote: > On A spreadsheet, I would like A4 ( Dates Missed) will show all dates > that were entered and A5 would sum the number of days missed from the > dates in A4. Can you assist? Your description lacks a lot of detail, so this may or may not be applicable. Assume your dates are entered in Co...

Excel VBA programming
I am bringing a large amount of data into worksheet 4 in a workbook. I want to use VBA to run queries on the table in worksheet 4. I am not sure how to set up a query for data that is in the same workbook. I'm sure this can be done, can't it? EggHeadCafe - Software Developer Portal of Choice Caching Pages and Application Data with Database Dependencies http://www.eggheadcafe.com/tutorials/aspnet/f3c54d02-702d-44df-9bd5-4a8530bde25f/caching-pages-and-applica.aspx Sure, but you will have to tell us more. the macro recorder can be your friend. -- Don Guillett Microsoft MVP Exce...

VBA Macro for font styles
I'm trying to write a macro that toggles through font styles. I'm trying to get the current font style and store it in a variable s that I can reapply the original font style later after changing it When I do this, myVariable = activecell.font.color (to store the font color) and later, I try to do this. activecell.font.color = myVariable (to restore the font color) The font in the activecell does not return to what was stored i myVariable. Can anyone help me out? Also, I used font.color in my example, but I' actually interested in storing and restoring the entire font styl ...

Loop through all shapes in VBA
Does anyone have some sample code that loops through all shapes on a page ? Chuck found it in the help.... "Chuck" <chuck@nomail.com> wrote in message news:O4EDb.3341$8M7.2288@news.get2net.dk... > Does anyone have some sample code that loops through all shapes on a page ? > > Chuck > > There are VBA examples for Visio at www.mvps.org/visio/VBA.htm John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visio/common_questions.htm &...

Animated Picture in Outlook
I have received an email with animated picture, can I save that picture in the animated format, I have tried to save in a different format but not with the animation. Is there any way to do that ? help Dear, Please have a look here http://www.howto-outlook.com/Howto/saveembeddedpictures.htm -- (Please respond to this question Was this post helpful to you?) Hope this helps you more Regards APK "Aslukka" wrote: > I have received an email with animated picture, can I save that picture in > the animated format, I have tried to save in a different format but not with >...

Converting Animations to Individual Slides
I need a way to convert a slide with builds into multiple slides, one slide for every stage of the animation(s). e.g. a slide with 2 text boxes, where the second box comes in after the presenter clicks forward would need to be 2 slides, one with just the 1st box, and one with both boxes. It would need to be automated so it can be done on an entire deck of slides and I cannot find a tool that does this. *We run synchronized presentations from multiple machines. One machine must convert the slides for web distribution using an unknown method, but smart art is usually ruined or...

ODBC Connection through Access VBA using Windows 7
We have a push to begin rolling out machines with Windows 7. In our testing of our Operations Access Databases, we are running into some real weird things going on when we're trying to establish an ODBC connection through Access VBA into Oracle Databases. Has anyone seen this or know anything about it? Is there a "Red Book" on anything regarding ODBC connections using Windows 7??? I appreciate your review and am hopeful for a response. Thanks. PSULionRP On Thu, 27 May 2010 14:08:20 -0700, PSULionRP <PSULionRP@discussions.microsoft.com> wrote: Not t...

VBA Advanced Filter Criteria (How to insert UsedRange in Range Sta
Using XL 2003 & 97 My Code is: Sheets("Filtered Data").Range("A1:Q5217").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria Sheet").Range(BEM_CUY), _ CopyToRange:=Range( "A1"), Unique:=False How do I get the Range statement to sense the used range? Otherwise Q5127 is a constant. TIA Dennis How about this (not tried I am afraid) Set oRng = Sheets("Filtered Data").UsedRange oRng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria Sheet").Range(BEM_CUY), _ CopyToRange:=Range( &...

Looking for example VBA code for MS Access DB sync
Need to store Access database data in a central location where multiple users can update the data. The users must be able to make modifications while off line (i.e. not connected to the central DB). These changes are to be uploaded to the central data location when the user has internet access. Anyone have example VBA code for an application of this type? Assuming you're using Access 2003 or earlier, or the MDB file format in Access 2007, look into using Replication. One of the best references for replication is David Fenton's Jet Replication Wiki at http://www.dfen...

Use VBA to access SQL database
I am attempting to open a query within a userform that interrogates a SQL database (password protected) and then return results from other columns for the same client code as entered by the user in the form. I set up a link to the database for mail merge within word, but accepted that I would need to take that a step further to get the interrogation into the userform. In setting up the link to the server I created and .odc which I thought would then be accessible within VBA, but I don't appear to be able to access that data feed within VBA. Sure I am missing something tri...

Is it possible to add an animation within an excel spread sheet?
Through at least Excel XP: No, not in Excel, but, yes, if you view the spreadsheet in a browser. HTH, Bernie MS Excel MVP "Captainm" <Captainm@discussions.microsoft.com> wrote in message news:B132D8D9-876A-4F97-A584-D087033B74E0@microsoft.com... > ...

How to create multiple subfolders using VBA
Hello and thanks for reading my question. I need some VBA programming help. I have a main folder with subfolders in it. I want to add a new subfolder to each subfolder in the directory. It would look something like this Main Folder Subfolder1 NewSubfolder Subfolder2 NewSubfolder Subfolder3 NewSubfolder Subfolder4 NewSubfolder I was able to use this VBA to do it for one folder, but I need it to look through all Subfolders MkDir ("C:\MainFolder\Subfolder1\Subfolder1.1") How do I look through each subfolder and add a new subfolder under each...

vba reference...
is it possible or a reference to allow me to extract data from a html file... example, i needed my vba code to return me all href and src attribs values from a few selected tags. do i have to clearly write a code from scratch? someone in my office said to look up on DOM, something on object model or something, but non of us here know enough about vba to make a clue in the vast sea of tech abbriviation. could someone point me in a direction? anything that helps would be greatly appriciated. ps: could anyone past a small code that would allow me to open a simple text file and pull i...

How can I group/outline UP using VBA
By using Selection.rows.group, [-] icon comes at the bottom of the selection. It might be usefull when we have several lines followed by total. But, I want to use grouping function for WBS(work breakdown structure). [-] MyMoving [-] Pack furnitures . Pack Dining Room . Pack Bed Room 1 . Pack Bed Room 2 .... [-] Load into Moving Car . Stuffs from Dining Room . Stuffs from Bed Room In other words, I want use Excel like MS Project summary tasks. Is there any way to implement this? I don't use MSProject, but in xl2003 menus, I can use:...

Dynamic Forms VBA
I am trying to create a dynamic form. Here is how it works: the user selects a query, and hits submit on a menu form. A form is then created and the record source is assigned based on the users initial query selection. Now the question/problem: Once the form is loaded, all of the captions/labels are generic. I would like to change this using vba. Is there any way to get column header information on the query programmatically? Ideas? dmericksen wrote: >I am trying to create a dynamic form. Here is how it works: the user selects >a query, and hits submit on a menu form. A form is then...

Cell Value in Header / Not Using VBA
Does anyone know of a way to get a cell value in a page header without using VBA? It's not possible using the GUI. You could create a similar effect by setting a link in a cell in Row 1 to your cell of interest, then have that row print on every page (set the top margin very small). In article <1187194057.971417.256730@k79g2000hse.googlegroups.com>, Gojavid <david.lachowsky@gmail.com> wrote: > Does anyone know of a way to get a cell value in a page header without > using VBA? Go through the menu and type it in???? Gojavid wrote: > > Does anyone know of a ...

Tab Index Order, Setting With VBA
The code below is supposed to assign control tab index order. However, it bugs out in the FOR loop of the sub SetTabsInPhoneBookOrder: 'Run-time error 2184: The value you used for the TabIndex property isn't valid. The correct values are from 0 through 9. I don't recall where I got the code and the author did not include his email address. I've search for his name and searched for ways to assign tabindex via VBA (Access 2002), without luck. Any help would be appreciated. Thanks for looking, Bob Option Compare Database Option Explicit Const CACHE_SIZE = 50 Sub Cachin...

how to make data together by VBA
i sent worksheetbooks to my workmates, with title line like this: "NO.,name, gender, project name, finish date, price" and so on, they fill the worksheet and then sent back to me. i kept all the files they sent back in a fold named "all data" i have to copy all the data to a new workbook and save it as "original data".xls. the problem is i dont know how much files they would sent back, maybe some one won't sent back, and i dont have regular names of the files, and in each file, the data may be not just one row, maybe several rows. how could i do t...

extract SQL sentence from a query via vba code
Hi all, is it posible to extract SQL sentence from a query via VBA code? ...

Calling a SQL stored procedure from modified form button by VBA C
Hi All, I have a requirement to call the SQL stored procedure from GP modified form through button press and I do not want to use dextertiy development for this, I so can anyone show me how to do this through VBA code. Thanks in advance. -- Developer Dear, Use the following code: Dim conMain as New ADODB.Connection(ConnectionString) conMain.Execute("SQLCommand") Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Software Development Manager +962 - 79 - 999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com "Dexdev" ...

How to switch worksheets with vba ?
I have a vba macro with a lot of references like... Range("A" & 1).Select strData = ActiveCell.Value This has defaulted to WorkSheet 1 Now I want to put every thing on WorkSheetX down in the Workbook Isn't there a vba statment that says... in effect... any references from here on till I say stop are to WorkSheetX. Like... References are now to WorksheetX code code code References are now to WorksheetABC code code etc thanks for any help. tmb If you drop the .select and .activates, you can usually work on the range directly: with worksheets("sheet1") ...