Microsoft Excel - How to change the size of the cells with a macro

Asked By Bojo on 07-Jun-11 09:43 AM
Hi guys,

In excel 2007 there's a scale bar in the bottom right corner to change the size of the cells and embedded graphics.

Is there a way to do this with a macro? My screen is set at 1400x 900 and when I change to 1200x 768 dpi, parts of the interface are obviously out of view. It would be great to alter this or even better get excel to do this automatically.

Any ideas please?

Thanks
Bojo
Jitendra Faye replied to Bojo on 07-Jun-11 09:46 AM
Try this-

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range
    Set isect = Application.Intersect(Target, Range("D7:AK28"))
    If IsNumeric(Target) And Not (isect Is Nothing) Then
        If Target.Value - Round(Target.Value, 0) <> 0 Then
            Target.Font.Name = "Arial"
            Target.Font.Size = 8
        Else
            Target.Font.Name = "Arial"
            Target.Font.Size = 10
        End If
    End If
End Sub
Jitendra Faye replied to Bojo on 07-Jun-11 09:49 AM
Try this macro-

Sub RowHeightInInches()
    Dim inches As Single
    ' Get the desired column width.
    inches = Application.InputBox("Enter Row Height in Inches", _
        "Row Height (Inches)", Type:=1)
    ' If the cancel button was not pressed.
    If inches Then
        ' Convert and set the column height.
        Selection.RowHeight = Application.InchesToPoints(inches)
    End If
End Sub


follow this also=

http://support.microsoft.com/kb/213422
Jackpot . replied to Bojo on 07-Jun-11 12:03 PM
Hi Bojo

Try

ActiveWindow.Zoom = 75
Bojo replied to Jackpot . on 07-Jun-11 12:50 PM
Hey Jackpot...:)

I thought you had given up on me, haven't heard from you for 4 days or so...

Thankyou so much mate, you have once again saved me tons of time....I had a look at the other suggestions and was about to start manually calculating each cell and image.....

I should be able to use this as a workbook setting and not just active page....would that be

private sub workbook_open()
ActiveWindow.Zoom = 75
end sub

thanks
bojo
Jackpot . replied to Bojo on 07-Jun-11 01:34 PM
Hi Bojo

Try this workbook event...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.Zoom = 75
End Sub
Jackpot . replied to Bojo on 07-Jun-11 01:37 PM
Forgot to mention that zoom, print etc are peroperties of a window.
Bojo replied to Jackpot . on 07-Jun-11 01:57 PM
thanks Jackpot

Great help as usual
Bojo replied to Bojo on 17-Jun-11 08:00 AM
hey Jackpot,

How can I use this with checkBox so that the workbook makes all active sheets automatically get the chosen setting instead of running this at a certain size when opening

e.g 
CB1024 =73 - Cb1280=90 - CB1440 =100 - CB1680=115  and then set the book to 100% when closing

thanks
Bojo
p.s -Iknow, I'm replying to myself, but the forum doesn't give another option!, only allows one answer at a time
Jackpot . replied to Bojo on 17-Jun-11 11:31 PM
Hi Bojo

OK. Now I understood why you post to yourself.


Insert 4 checkboxes from the Developer Tab>Insert>Active X controls and try the below code..Right click the sheet and paste the below code.


Private Sub CheckBox1_Change()
If CheckBox1.Value = True Then
Reset
CheckBox1.Value = True
ActiveWindow.Zoom = 73
End If
End Sub
  
Private Sub CheckBox2_Change()
If CheckBox2.Value = True Then
Reset
CheckBox2.Value = True
ActiveWindow.Zoom = 90
End If
End Sub
Private Sub CheckBox3_Change()
If CheckBox3.Value = True Then
Reset
CheckBox3.Value = True
ActiveWindow.Zoom = 100
End If
End Sub
Private Sub CheckBox4_Change()
If CheckBox4.Value = True Then
Reset
CheckBox4.Value = True
ActiveWindow.Zoom = 120
End If
End Sub
Sub Reset()
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
End Sub
  
Private Sub Worksheet_Activate()
ActiveWindow.Zoom = 100
End Sub