RePost - Macro working inconsistantly

[kimkom]

Hi all,

I'm have trouble with the macro below not working on a consistant basis.
What it attempts to do is extract text from a Text Box (ActiveX) from one
slide and copy it to a table cell on another slide.

My feeling is that the problem may lie with the variable "TheText(oSh)" but
as I'm a vba newbie I'm unable to figure out a work around.

Can anyone assist please?

Thanks,
Michael


Sub UpdatePrintTables()

Dim oSh As Shape
Dim oTarget_table1 As Table
Dim oTarget_table2 As Table

Set oTarget_table1 =
ActivePresentation.Slides(3).Shapes("PrintTable1").Table
Set oTarget_table2 =
ActivePresentation.Slides(3).Shapes("PrintTable2").Table

For Each oSh In ActivePresentation.Slides(2).Shapes

If oSh.Name = "EditableBox1" Then
oTarget_table1.Cell(1, 2).Shape.TextFrame.TextRange.Text =
TheText(oSh)
oTarget_table2.Cell(1, 2).Shape.TextFrame.TextRange.Text =
TheText(oSh)
End If

If oSh.Name = "EditableBox2" Then
oTarget_table1.Cell(1, 7).Shape.TextFrame.TextRange.Text =
TheText(oSh)
oTarget_table2.Cell(1, 7).Shape.TextFrame.TextRange.Text =
TheText(oSh)
End If

Next

ActivePresentation.SlideShowWindow.View.GotoSlide 3

End Sub

 

[David Marcovitz]

I thought I would try to help out here, but you have about 5 different
threads going now so I don't think I remember all the context. As I look
at this code, I still wonder if EditableText1 (and 2) are regular text
boxes or control text boxes. I also wonder what TheText is since it is
not defined in your code.
--David

 

[kimkom]

David, Steve - thank you both for your replies, I appreciate you both taking
the time to help. Apologies if I have a couple of different posts on this
subject, I just felt I wasn't getting anywhere. I'll stick to this one from
now on.

I didn't realize TheText(oSh) was a function, but that is present in the
code you graciously provided, Steve.

David, the Text boxes in question are the kind you can enter text into when
viewing the PPS. ie the ones in the Developer tab under "Text Box (ActiveX
control) - insert a text box control"

Now onto the code...

Although there are many more repititions of the "EditableBox1" and two
tables to populate, the working code at the moment goes as follows (like I
say, just working intermitantly in PPT2003):


Function TheText(oSh As Shape)

If oSh.Type = msoOLEControlObject Then
TheText = oSh.OLEFormat.Object.Text
End If

End Function


Sub UpdatePrintTables()

Dim oSh As Shape
Dim oTarget_table1 As Table

Set oTarget_table1 =
ActivePresentation.Slides(3).Shapes("PrintTable1").Table

For Each oSh In ActivePresentation.Slides(2).Shapes

Select Case oSh.Name

Case Is = "EditableBox1"
oTarget_table1.Cell(1, 2).Shape.TextFrame.TextRange.Text =
TheText(oSh)

End Select

Next

ActivePresentation.SlideShowWindow.View.GotoSlide 3

End Sub


I don't know why there would be a problem with the code if (as it does) it
works a couple of times in PPT2003 then just gives up. But I'm unable to
think what else could be the problem.

Any help or advice will be gratefully received.

Many thanks,
Michael

 

[John Wilson]

Michael
Are you calling the code with a button on slide 2?

Try having the button/whatever on slide 3 - Don't remove the code line to
goto slide 3
--

john ATSIGN PPTAlchemy.co.uk
Custom vba coding and PPT Makeovers
Free PPT Hints, Tips and Tutorials
http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html

 

[kimkom]

John,

Thanks for your reply.

Yes, I did think of that one and gave it a go without any luck. That was a
couple of days ago though and I've made some minor changes since then, so
I'll give it another try.

Does anybody have any suspicions as to what the problem might be here?

Thanks,
Michael

 

[kimkom]

Placing the code on another module didn't work either

Once it stops working, it really stops working. Weird...

Michael

 

[kimkom]

John,

Just tried it again using a hyperlinked button from slide 2 to slide 3, with
the code placed on a button on slide 3. Worked the first time, but not the
second.

Perhaps if I put the function and sub in a new module altogether?

Clutching at straws...

Michael

 

[John Wilson]

I suspect the problem is the page with the table not updating. I would have
thought that running the gotoslide routine would have solved this.

Try adding a blank slide between 2 and 3 give this slide an auto transition
of zero secs so it jumps straight to the slide with the table. You will need
to change the code to reference the table now on slide 4.

Works every time here.
--

john ATSIGN PPTAlchemy.co.uk
Custom vba coding and PPT Makeovers
Free PPT Hints, Tips and Tutorials
http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html

 

[kimkom]

Okay John, I'll give that a try.

Like I say it works every time here too in PPT 2007, but not in 2003 and my
client cannot update to 2007.

Thanks,
Michael

 

[kimkom]

Damn, still no joy there John. That was a good effort too.

The thing is it's not just the slide that's not updating because once the
macro stops working it wont even go to the required slide. The button (or
anything else) with the macro applied to it just becomes completely inactive
after a couple of goes.

It's almost as if the macro security settings go up a level and stop it
working!

Michael

 

[kimkom]

Thanks again for your input Steve.

There aren't any other controls on the slide apart from some hyperlinked
buttons created in the Master, but it makes sense to put the error check in
there, thanks.

I'm not using PPT's but instead PPS's as they need to automatically open in
viewer mode.

There are many more repetitions but all are individually named. I've also
tried stripping them all out leaving just one element working but still the
problem is the same.

Thanks for your suggested error checking routines, I will apply them and see
what happens.

Michael

 

[kimkom]

Nice error checking routine there Steve!

On the 3rd or 4th time of pressing the 'infected' button your routine
displayed the following error:

Error:
-2147024809
The item with the specified name wasn't found


Just to re-iterate this happens in PPT2003 not in PPT2007, but I really nead
to sort this.

Why wouldn't it find the named item when it already did with previous
clicks? Can you suggest how to investigate this further and eliminate the
problem?

Maybe I should try and isolate the offending shape, if it is indeed the same
one causing the error all the time?

Interesting stuff...

Thank you,
Michael

 

[kimkom]

Hi

Can someone please tell me if this problem is likely to be software ie PPT
2003 specific, or machine specific ie my PC?

If so, can it be fixed?

Thanks,
Michael

 

[kimkom]

Okay, I've just discovered this problem is not just on my version of PPT2003
as it's doing the same thing on another laptop.

Help please!

Thank you,
Michael

 

[kimkom]

Thank you Steve. I will send over a stripped down file shortly.

Many regards,
Michael

 

[kimkom]

That looks fantastic Steve! I'm glad to hear it wasn't just me after all!

I'll give it a try tomorrow morning and let you know how it goes, but you've
certainly filled me with confidence that the tag system will do the trick.

Thank you so much for your in-depth help and advice, it's been a Godsend.

Fingers crossed for tomorrow morning.

All the best,
Michael

 

[kimkom]

Hi Steve,

Sorry to bug you again.

I've applied your revised code but I am now getting the following when the
Sub is run:

Error:
91
Object variable or With block variable not set

Am I missing something obvious?

Many, many thanks again for all your help,
Michael

 

[kimkom]

Hard to say ...

Set a break point at the beginning of the code then step through the code to find out where the
error occurs.

Also verify that the ShapeTagNamed function actually returns a shape; you could put a

MsgBox "Found it!" right before Exit Function for example.

If it doesn't return a shape, you'll need to figure out what happened to the tags on the shapes
you're looking for.

You did apply tags to the two tables, right? That's what the TagIt sub below was for.

Ah! That could be the 'obvious'. I appy tags to the tables how?? Sorry for
my ignorance.

 

[kimkom]

Hard to say ...

Set a break point at the beginning of the code then step through the code to find out where the
error occurs.

I'm entirely sure how the debugger works here. I've managed to set up the
breakpoint but don't know how to step through the code.

Also verify that the ShapeTagNamed function actually returns a shape; you could put a

MsgBox "Found it!" right before Exit Function for example.

I have tried this and it seems to execute fine.

If it doesn't return a shape, you'll need to figure out what happened to the tags on the shapes
you're looking for.

You did apply tags to the two tables, right? That's what the TagIt sub below was for.

SORRY, SORRY, SORRY... Just seen your TagIt Sub routine. What a dummy I am!!

 

[kimkom]

Okay Steve,

I'm now at where I should have been at this morning after applying your FULL
code additions. Sorry about that.

I've tagged the two tables with different tags and changed the "TagName"
within relevent routine to the same tag names.

Unfortunately I'm still getting the same error though

Error:
91
Object variable or With block variable not set

Like I mentioned in my previous post, I'm not sure how to go about debugging
now though.

What should I do?

Thanks,
Michael