Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select and fill in textboxes

Posted on 2014-10-30
7
Medium Priority
?
181 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:Ed Matsuoka
  • 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:Ed Matsuoka
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 13

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Ed Matsuoka
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 13

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:Ed Matsuoka
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 13

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
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…

575 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