?
Solved

Select and fill in textboxes

Posted on 2014-10-30
7
Medium Priority
?
178 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 12

Accepted Solution

by:
Jamie Garroch earned 2000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 12

Assisted Solution

by:Jamie Garroch
Jamie Garroch earned 2000 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 12

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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