Solved

Select and fill in textboxes

Posted on 2014-10-30
7
163 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
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

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

Author Comment

by:Eddie_Aeffect
Comment Utility
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 9

Accepted Solution

by:
Jamie Garroch earned 500 total points
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Eddie_Aeffect
Comment Utility
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 9

Assisted Solution

by:Jamie Garroch
Jamie Garroch earned 500 total points
Comment Utility
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
Comment Utility
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 9

Expert Comment

by:Jamie Garroch
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

6 Experts available now in Live!

Get 1:1 Help Now