Solved

Select and fill in textboxes

Posted on 2014-10-30
7
172 Views
Last Modified: 2014-12-01
Hi experts:

I have a Powerpoint presentation with upwards of 100 text boxes per slide. Right now, to change the numbers in those text boxes we have to manually click on each text box. I need a macro that allows me to select all these text boxes and then, perhaps using input boxes, change all the numbers, a row at a time.

Have a great day!
0
Comment
Question by:Eddie_Aeffect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40414497
What do you mean, "a row at a time"?
0
 

Author Comment

by:Eddie_Aeffect
ID: 40414512
01 02 03 04 05 06 07 08 09
10 11 12 13 14 15 16 17 18
20 21 22 23 24 25 26 27 28

I was imagining something like an input box that would pop up and ask for the new entry for row 1/col 1 (01 becomes 12), row 1/col 2 (02 becomes 08), row 1/col 3 (03 becomes 32), etc. This input box would contain the current number in the text box so that if that number doesn't change you could just hit ENTER and go to the next text box. Ideally this would turn the process of changing an 9 x 9 matrix of text boxes from 20 minutes of click box/change box entry tedium to 3 minutes of fast typing!
0
 
LVL 11

Accepted Solution

by:
Jamie Garroch earned 500 total points
ID: 40473486
Try this Eddie:

' =====================================================================
' Macro written by Jamie Garroch of http://youpresent.biz
' =====================================================================
' Provides a quick way to change the text in all shapes within a user
' selection that contain text.
' =====================================================================

Option Explicit

Public Sub ChangeTextBoxValuesInSelection()
  With ActiveWindow.Selection
    If Not .Type = ppSelectionShapes Then _
      MsgBox "Please select some shapes containing text.", vbCritical + vbOKOnly, "Nothing Selected": Exit Sub
    If Not .ShapeRange.Count > 0 Then _
      MsgBox "Please select some shapes containing text.", vbCritical + vbOKOnly, "Nothing Selected": Exit Sub
    
    Dim oShp As Shape
    
    For Each oShp In .ShapeRange
      With oShp
        If .HasTextFrame Then
          If .TextFrame.HasText Then
            Dim newText As String
            With .TextFrame.TextRange
              newText = InputBox("Enter new text to replace the current value for" & vbCrLf & vbCrLf & _
                oShp.Name & " :", "Enter New Text", .Text)
              If Not newText = "" Then .Text = newText Else Exit Sub
            End With
          End If
        End If
      End With
    Next
  
  End With
  ' Clean up
  Set oShp = Nothing
End Sub

Open in new window

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Eddie_Aeffect
ID: 40473938
Hi Jamie!

Thanks for the macro, it is almost perfect. My problem is that the text box names are "out of order" so the macro will jump around. I thought of a fix that might work by first renaming the text boxes based on their location on the screen (i.e., NewName="txtbox_" & shape.row & shapecol.  How would I do that?

Eddie
0
 
LVL 11

Assisted Solution

by:Jamie Garroch
Jamie Garroch earned 500 total points
ID: 40474036
There are two things you can do Eddie:

1. PowerPoint orders a selection based on the Z-Indez (layer) property of the objects within the selection. So, you could just reorder the textboxes using the Selection pane (Alt+F10) to change their layer order (drag them up/down in the list).
2. If you want to do it in code by looking at the names of objects then you could do something like this:
(a) Name all of your textboxes "my textbox 1", "my textbox 2" etc.
(b) In the macro, set up a loop to look at each shape on the slide by referring to its name property: (untested code)

Dim Counter as Integer
Dim oSld as Slide
Set oSld = ActivePresentation.Slides(ActiveWindow.View.Slide.SlideIndex)
Counter = 1
For Counter = 1 to ActiveWindow.Selection.ShapeRange.Count
  With ActiveWindow.Selection.ShapeRange("my textbox " & CStr(Counter))
  ' Do the text entry part here for the shape in the right named order
  End With
Next
' Clean up
Set oSld = Nothing

Open in new window


You could also set up two dimensions for your order algorithm to specify row and column and just loop through each column within each row loop.
0
 

Author Comment

by:Eddie_Aeffect
ID: 40474185
I added this code to the beginning of your code:
'------------------------------------------------------------------------------
' First rename textboxes to they are in order
'------------------------------------------------------------------------------

With ActiveWindow.Selection

  For Each oShp In .ShapeRange
    With oShp
    NewName = "txt_" & Trim$(Int(.Left)) & Trim$(Int(.Top))
    End With
    Next

End With

I made the mistake of thinking the text boxes go in order of name but in researching a bit more I see their TAB order is ALWAYS set by creation order and can't be changed in code. That means I either need to set up a series of template slides    with the text boxes created left-to-right/top-to-bottom OR I need to set up some code that stores the text box text into an array (left-to-right/top-to-bottom), deletes the old text boxes and recreates them in the right TAB order. Am I right that I can't reset the TAB order on the fly?
0
 
LVL 11

Expert Comment

by:Jamie Garroch
ID: 40474357
The "tab" order that you describe is the same order as I mentioned before e.g. the layer (Z-Order) ordering. When shapes are created, the latest one is added to the top layer so yes, in a way, the order is as you say. But, the incorrect part is that this order CAN be changed:

1. Via the user interface's Selection Pane (Alt+F10 for PowerPoint 2013 and 2010)
2. Programmatically via the shape's Z-Order property

Your method for renaming them according to their on-slide position makes sense and you could also change the Z-Order at the same time using:

oShp.ZOrder msoBringToFront

Open in new window

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dir function fails on mapped drives 28 154
Explain Unit of Work pattern 2 86
What .NET URL re-routing tool did I use? 2 91
Input parameteres to DragOver 2 58
This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

732 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