Dynamic Range with Excel camera tool

[Stefan_Sand]

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

[HansV]

Could you try to explain in clear and understandable language what you want?

[Stefan_Sand]

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.

[HansV]

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.

[Stefan_Sand]

i can make any picture of a range, but this range appears not to be dynamic....

[HansV]

I don't understand the definition of CameraRange and I don't understand what exactly you want to do.

[Stefan_Sand]

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.

[HansV]

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.

[mbarron]

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:

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

[Stefan_Sand]

Thank You both, thats fantastic!!!!!!!!!!!!!!!!!!!!!!!