PDA

View Full Version : Solved: Commandbutton.onAction



riniheijnen
02-26-2012, 02:47 PM
I have an excelsheet with a certain range "realusedrange" that has to be pasted in a Powerpint-slide. Because I want to give the user the possibility to make complete the range I made a commandbutton on the excelsheet. When the user presses this button another macro should be started where the pasting happens.

realusedrange.Copy
Cells(lastrow + 2, 3).Select

With Selection
ActiveSheet.buttons.Add(ActiveCell.Left, ActiveCell.Top, 120,60).Select
Selection.OnAction = "PERSONAL.XLSB!macro_powerpoint_slide()"
Selection.Characters.Text = "Powerpoint" & vbCrLf & "Presentatie"
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Calibri"
.FontStyle = "Standaard"
.Size = 16
End With
End With

Sub macro_powerpoint_slide()

Dim pptApp As PowerPoint.Application
Dim pptPrs As PowerPoint.Presentation
Dim pptSld As PowerPoint.Slide
Dim pptshp As PowerPoint.Shape

Set pptApp = CreateObject("Powerpoint.application")

pptApp.Visible = True

Set pptPrs = pptApp.Presentations.Open("C:\VVVierpolders\automation\Templates\TV-sponsors template." & "potx")
Set pptSld = pptPrs.Slides(1)
Set pptshp = pptSld.Shapes(1)

'plakken excel-tabel in powerpoint slide
pptSld.Shapes.PasteSpecial(ppPasteOLEObject).Select

End Sub

When I run the code I'll get an error

"unable to set the onaction property of the button class"

When I put the text of the second macro at the end of the first macro it works ok for me.
I guess I have to pass "realusedrange"tot the macro "macro_powerpoint_slide()" but I don't know how.
Anyone has any suggesions?

frank_m
02-26-2012, 05:28 PM
Try this:

Dim Btn As Object
Set Btn = ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, 120, 60)

With Btn
.OnAction = "C:\PathToYourXlstartFolder\PERSONAL.XLSB!macro_powerpoint_slide"
.Characters.Text = "Powerpoint" & vbCrLf & "Presentatie"
With .Characters(Start:=1, Length:=23).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 16
End With
End With

Edit: I just noticed that your procedure that causes the error has the End Sub missing, but I assume that is just a copy paste mistake.

riniheijnen
02-26-2012, 11:20 PM
I changed the code as you suggested and the code goes into the macro "macro_powerpoint_slide".
I had to change the path to my Personal.XLSB in ".OnAction = "PERSONAL.XLSB!macro_powerpoint_slide" without the complete path to it because of an error:

"no access to K:\Documents and Settings\Heijnen\Application 'C:]Documents and settings\heijnen\mijn documenten oud\data\microsoft/excel/XLSTART\PERSONAL.XLSB'.
And I even don't have a drive K!!!!!!

The code goes into the macro "macro_powerpoint_slide" and in the line where the pasting is comes with another error:

"error -2147188160(80048240) while excecuting. Shapes (unknown member): invalid request. The specified data type is unavailable"

I guess this error is invoked by the ppPasteOLEObject in:

pptSld.Shapes.PasteSpecial(ppPasteOLEObject).Select

Do you have other suggestions?

frank_m
02-27-2012, 01:33 AM
I don't know, but by googling it looked to me like you might try changing this:
pptSld.Shapes.PasteSpecial(ppPasteOLEObject).Select

To this:

pptSld.Shapes.PasteSpecial DataType:=ppPasteOLEObject, link:=msoFalse

riniheijnen
02-27-2012, 01:47 AM
I have tried your suggestion but the result is the same. An error same as before. I think the problem is invoked by the range "realusedrange". In the second macro "macro_powerpoint_slide" the range isn't found to paste.

frank_m
02-27-2012, 01:52 AM
Why can't the range be set and copied right before the paste?

riniheijnen
02-27-2012, 09:14 AM
Why can't the range be set and copied right before the paste?

The range is set in the first macro and the pasting of the range happens in the second macro. In the second macro realusedrange doesn't exist and I tried to pass the range to the second macro without succes. Maybe you have a solution to pass this range to the second macro?

frank_m
02-27-2012, 10:39 AM
Try putting your range variable outside and above the first Sub. That will make it accessible to other Subs within the same module.

Option Explicit
Private realusedrange As Range
Sub Macro1()
Dim Btn As Object
Set realusedrange = Sheets("Sheet1").UsedRange 'change to your Range & sheet name.
'other code
On Error Resume Next
ActiveSheet.Shapes("MyButton").Delete 'delete button if it already exists
On Error GoTo 0
Set Btn = ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, 120, 60)

With Btn
.OnAction = "macro_powerpoint_slide"
.Characters.Text = "Powerpoint" & vbCrLf & "Presentatie"
.Name = "MyButton"
With .Characters(Start:=1, Length:=23).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 16
End With
End With

End Sub

Sub macro_powerpoint_slide()
'other code
realusedrange.Copy
pptSld.Shapes.PasteSpecial DataType:=ppPasteOLEObject, link:=msoFalse
End Sub

riniheijnen
02-27-2012, 01:20 PM
I pasted your exemple into my code and guess what.............

It Worked :clap:

I deleted the line where you set the realusedrange because it already exists in macro 1.
In macro 2 I added the realusedrange.copy and used my line of pasting, the result was the same as your line of pasting.
Probably the most important was the declaration of the reslusedrange on top of the code. Without this declaration I didn't have a realusedrange in my second macro.
So thanks a lot for your contribution. I was puzzling for 2 weeks now without any succeed so I really appreciate it. Now I am going to work on my other two buttons on the sheet which should not be so difficult. Saving the excelsheet and quiting Excel.

Maybe you can give me an answer on another question?
Are these buttons available in the powerpoint-slide also?

Hope to hear from you.

Rini

frank_m
02-28-2012, 10:41 PM
Hi riniheijnen,

Sorry but I'm not knowlegable at all on powerpoint. Plus you really should start a new thread for that most recent question. You can always provide a link to this thread for reference.

I'm wondering more about your issue in this thread though. Why can't you set the range in the macro_powerpoint_slide code? right before copying it, instead of setting it in Macro1 ?

I ask because if you save and close the workbook with the button still in place, and reopen it, and do not run macro1 before clicking the button, the range variable will be empty.

riniheijnen
02-28-2012, 11:02 PM
Hi Frank,

Thanks for your reply. I think I didn't made myself clear. In the second macro the range does exist now because of the private declaration on top of all code and the code works just fine now.
In the mean time Ihave been searching for doing the sane in a powerpointslide because I also will give the user the possibility to edit one or more cells in the embedded worksheet. I thougt it couldn't be to hard to place a button on the slide which the user had to click before the slide is exported as JPG-file. It is this jPG-file that I need to show on a TV.
I was wrong because there isn't much to find about buttons and powerpoint on the internet so right now I am thinking about saving the excelsheet directly into a jpg file. So I am trying to make the same jpg-file from the selected range. It has to be a rather large worksheet of 89 cm with (approc. 35 inches) and 50 cm high (approx. 20 inches). So I am trying to work that out.
Anyhow hank you very much and maybe you can tell me how I can change my post to SOLVED since it is.

Rini

frank_m
02-28-2012, 11:30 PM
There is a button near the top right of your first post named "Thread Tools" - Click that and one of the dropdown choices will be "Mark Solved"
If I remember right you also have to click where it says something to the effect of "Apply Marked Action"

Some Browsers such as Chrome don't show the Thread Tools button, so in that case you may need to use Internet Explorer.

Best of luck with your next task. :)