CODEWITHSUNDEEP
vbaexcelscreenshot
TJYen
TJYen

Reputation: 373

VBA Adjust for Screen Resolution

To my experience, opening excel files containing picture objects that are not snapped to a cell tend to shift around depending on the computer that it is being opened in.

I was wondering if there is a way around this nature such as opening the excel file with a set resolution parameter?

My utilization uses screen captures and cells to reformat the pasted picture to desired sizes. Such as :

dim rng as range
set shtemp=worksheets.add
Set rng = shtemp.[B14:Q49]
rng.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

shtemp.Shapes("Picture 1").Delete
Range("A1").Select
ActiveSheet.Paste
'Reset the altered cells to normal in order to maintain congruency in    picture size alteration
'This line is where I have to re-adjust cell settings to match the computer/screen I am using and capture the needed range.
            Rows("49:49").RowHeight = 13.2
            Columns("Q").ColumnWidth = 8.11
Set r = Range("B5:T45")
With Selection.ShapeRange
        .LockAspectRatio = False
        .Top = r.Top
        .Left = r.Left
        .Width = r.Width
        .height = r.height
    End With

Upvotes: 2

Views: 3399

Answers (1)

Josh Anstead
Josh Anstead

Reputation: 328

Try adding this to the code. It selects a range and the adjusts the zoom to that range. So, no matter the size of the excel sheet the range will fit within the window. Play around with the range size and the final range select to focus on the correct part of the sheet. 

Range("A1:S1").Select
ActiveWindow.Zoom = True
Range("B5").Select

Upvotes: 1

Related Questions