Select and fill in textboxes

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!
Ed MatsuokaPartner/Senior IT SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What do you mean, "a row at a time"?
0
Ed MatsuokaPartner/Senior IT SpecialistAuthor Commented:
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
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Ed MatsuokaPartner/Senior IT SpecialistAuthor Commented:
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
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
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
Ed MatsuokaPartner/Senior IT SpecialistAuthor Commented:
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
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.