Microsoft Excel - How to change the size of the cells with a macro
Asked By Bojo
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
Vickey F replied to Bojo
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
Vickey F replied to Bojo
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
Hi Bojo
Try
ActiveWindow.Zoom = 75
Bojo replied to Jackpot .
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
Hi Bojo
Try this workbook event...
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.Zoom = 75
End Sub
Jackpot . replied to Bojo
Forgot to mention that zoom, print etc are peroperties of a window.
Bojo replied to Jackpot .
thanks Jackpot
Great help as usual
Bojo replied to Bojo
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
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
hide and unhide rows in excel Microsoft Excel Hi My data looks like this: There would be around 40 sales persons. Customer numbers 100 200 300 457 12320394 234 121 232 323 14350323 Peter 15323923 13232990 regards shekhar Microsoft Excel (1) Intersect (1) Worksheet (1) Trigger (1) Macros (1) Module (1) Bit (1) VBA (1) from the you are working with: Option Explicit Private Sub Worksheet_Change( ByVal Target As Range) If Not Intersect(Target, Range( "A1" )) Is Nothing Then Call FilterList(Target, 4) End If End Sub Private Sub FilterList( ByVal Target As Excel.Range, ByVal intStart As Integer ) Dim arrData As Variant Dim lngLastRow As Long Dim lngCurrRow
Negative Values in Excel Excel Is it possible to have a series of cells automatically add the negative sign in front of an inputed number? Excel Miscellaneous Discussions Microsoft Excel (1) Target.Cells.Count (1) Intersect (1) Excel (1) Error (1) Application.EnableEvents (1) VB (1) Target.Value (1) Either by formula: = -A1 range, use Paste Special, Operation Multiplication, to multiply the range by -1. - Jon - -- -- -- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http: / / PeltierTech.com _ __ __ __ This
How do I find the date a cell was modified? Excel Excel Miscellaneous Discussions Microsoft Excel (1) Intersect (1) Error (1) Excel (1) Application.EnableEvents (1) Worksheet (1) GoTo (1) Alt (1) You do not unless you Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value <> "" Then .Offset(0 that worksheet module. Edit the range A1:A10 to suit. Alt + q to return to Excel. Gord Dibben MS Excel MVP keywords: How, do, I, find, the, date, a, cell, was
How to stop opening outlook in excel Excel How to stop opening outlook in excel when clicking into a cell that contains an email address. Excel Miscellaneous Discussions Microsoft Excel (1) AutoCorrect (1) Cell.Hyperlinks.Delete (1) Hyperlinks (1) Intersect (1) Excel (1) Selection.SpecialCells (1) Macro (1) Well, you can stop Excel from automatically converting email
Can i restrict input in a cell until previous cell populated? Excel Excel Miscellaneous Discussions Microsoft Excel (1) Intersect (1) Application.EnableEvents (1) Detailsthan (1) You most likely will remain frustrated if you cannot ByVal Target As Range) Const WS_RANGE As String = "B1:B10" '< = = change to suit If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Application.EnableEvents = False With Target If .Offset(0 ClearContents End If End With End If Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP keywords: Can, i, restrict, input, in, a, cell, until, previous, cell, populated?