Hi all,
in my last Post (http://www.eileenslounge.com/viewtopic.php?f=27&t=3501" onclick="window.open(this.href);return false;) i wanted to create a WBS-Structure for a project, as i did with Hans help (see the attached outcome for
an organisational or work brakedown structure). What i want to know at last is, how can i make the excel camera tool being dynamic. I tried to insert a dynamic range to do so, but the caera picture is
as the same size as showing the whole structure.... How do i have to change my named range formula to change the picture size/range of the excel camera snapshot?
thanks in advance,
stefan
Dynamic Range with Excel camera tool
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Dynamic Range with Excel camera tool
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic Range with Excel camera tool
Could you try to explain in clear and understandable language what you want?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Dynamic Range with Excel camera tool
yes, i want to set the camera range dynamic = i want to take a dynamic picture of the shown wbs structure (the visible structure). -> in table data, you can set, which structure you want to show -> in wbs structure, the wbs is shown, and in wbs graphic there should be the picture of it.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic Range with Excel camera tool
Sorry, I don't have the slightest idea what you mean.
I suspect that Microsoft Project or Microsoft Visio would be better suited to your purposes.
I suspect that Microsoft Project or Microsoft Visio would be better suited to your purposes.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Dynamic Range with Excel camera tool
i can make any picture of a range, but this range appears not to be dynamic....
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic Range with Excel camera tool
I don't understand the definition of CameraRange and I don't understand what exactly you want to do.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Dynamic Range with Excel camera tool
i only want to use the camera tool of excel to make a picture oft the wbs structure, as is shown. if the structure is changing, the picture should follow the change = > the shown range changes, but i don´t want to make it manually.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic Range with Excel camera tool
Set the formula for CameraRange to
=OFFSET('WBS Structure'!$A$1,5,39-8*INT((MAX(Activities,Tasks)-1)/2),13+4*Subtasks,8*MAX(Activities,Tasks)-1)
In German, OFFSET = BEREICH.VERSCHIEBEN and INT = GANZZAHL, MAX remains MAX.
=OFFSET('WBS Structure'!$A$1,5,39-8*INT((MAX(Activities,Tasks)-1)/2),13+4*Subtasks,8*MAX(Activities,Tasks)-1)
In German, OFFSET = BEREICH.VERSCHIEBEN and INT = GANZZAHL, MAX remains MAX.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 112
- Joined: 25 Jan 2010, 20:19
Re: Dynamic Range with Excel camera tool
For you macro, you need to copy the range in the normal manner and then paste it as a picture link. Currently you are copying as a picture and then pasting the picture.
Here is you code altered to do this:
Here is you code altered to do this:
Code: Select all
Sub DoCamera()
Dim MyPrompt As String
Dim MyTitle As String
Dim UserRange As Range
Dim OutputRange As Range
Application.ScreenUpdating = True
'Prompt user for range to capture
MyPrompt = "Select the range you would like to capture."
MyTitle = "User Input Required"
On Error Resume Next
Set UserRange = Application.InputBox(Prompt:=MyPrompt, _
Title:=MyTitle, Default:=ActiveCell.Address, Type:=8)
If UserRange Is Nothing Then End
On Error GoTo 0
'Copy range to Clipboard as picture
'Prompt user for range to paste to
MyPrompt = "Select the range on which you would like to paste."
MyTitle = "User Input Required"
On Error Resume Next
Set OutputRange = Application.InputBox(Prompt:=MyPrompt, _
Title:=MyTitle, Default:=ActiveCell.Address, Type:=8)
If OutputRange Is Nothing Then End
On Error GoTo 0
'Copy range to Clipboard as picture
UserRange.Copy
'Paste picture to output range
OutputRange.Select
ActiveSheet.Pictures.Paste Link:=True
'Selection.Formula = UserRange.Address
End Sub
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Dynamic Range with Excel camera tool
Thank You both, thats fantastic!!!!!!!!!!!!!!!!!!!!!!!