Excel Zoom

OK, so your co-worker or boss has one of those enormous monitors, and you’re stuck working on this little tiny laptop screen. Every time they open a file, they change the zoom setting to 50%, because heaven forbid they look at things any other way. Then, they go ahead and save the file at a 50% zoom. The next time you open the file, you’re squinting to try to see the little ants marching across your spreadsheet. Of course, you could just change the zoom level in the toolbar, but that gets annoying after a while doesn’t it?

Now, it’s time for a little payback. There’s an easy way to always have Excel change the default zoom level to whatever you want (i.e. 100%, 85% etc.) Copy all text in the Auto Set Zoom Level box below. Paste it into your workbook’s Visual Basic editor, in the “ThisWorkbook” object. This will automatically set the zoom level to 100% whenever the workbook is opened.

On the other hand, if you want to be nice to your boss or co-worker, and let them get the benefits of this macro, you can set a toggle to go between their zoom level (50% in this example) and yours (100% in this example). Copy all text in the Toggle Zoom Level box below. Paste it into your workbook’s Visual Basic editor, in the “ThisWorkbook” object. Note: If you manually change the zoom level in the toolbar to anything other than 100% or 50%, the next time the workbook is opened, it will open up as 50%.

Finally, you can set all worksheets in your workbook to be the same zoom level. Copy all the code under Set Zoom Level on All Worksheets below. Paste it into your workbook’s Visual Basic editor, in the “ThisWorkbook” object, or a module if you wish. The way this macro differs from the other two is it won’t run as soon as you open the workbook. If you want it to do so, change the line that says Sub Set_All_Zoom() to Private Sub Workbook_Open(), and make sure you have it in the “ThisWorkbook” object in Visual Basic.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Auto Set Zoom Level:
Note: You can change the “100″ to whatever zoom level you wish to default to.

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
ActiveWindow.Zoom = 100
End Sub

Toggle Zoom Level:
Note: You can change the “100″ and “50″ to whatever zoom level you wish to toggle between.

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
If ActiveWindow.Zoom = 50 Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 50
End If
End Sub

Set Zoom Level on All Worksheets:
Note: You can change the “75″ to whatever zoom level you wish to set all worksheets in your file to.
This macro differs from the other two as it won’t run as soon as you open the workbook. If you want it to do so, change the line that says Sub Set_All_Zoom() to Private Sub Workbook_Open(), and make sure you have it in the “ThisWorkbook” object in Visual Basic.

Sub Set_All_Zoom()
'
'MACROS BY EXCELZOOM.COM
Sheets.Select
ActiveWindow.Zoom = 75
ActiveSheet.Select

End Sub

Ever wish you could ask someone a bunch of questions and have them entered into the appropriate cells? With this macro, you can have an input box pop up asking the user to answer a question (i.e. what is your name, what is your address, etc.). By defining the user’s response with a unique name, you can have the macro populate the file for you.

Use the example below as a guide. It asks for the user’s name, address, city, state and zip code, and enters the responses in a cell. You can change the questions, change the cells that the answers populate, and add or delete questions. Just be consistent with naming the responses (i.e. “YourCity” should be “YourCity” wherever it shows up in the code below).

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.


Sub GetInput()
'
'MACROS BY EXCELZOOM.COM
'
Dim YourName
YourName = InputBox("Enter your name")
Range("A1") = YourName
Dim YourAddress
YourAddress = InputBox("Enter your address")
Range("A2") = YourAddress
Dim YourCity
YourCity = InputBox("Enter your city")
Range("A3") = YourCity
Dim YourState
YourState = InputBox("Enter your state")
Range("B3") = YourState
Dim YourZip
YourZip = InputBox("Enter your zip code")
Range("C3") = YourZip
End Sub


Sometimes we hide certain rows or columns because we don’t want the data to print, or just to get it out of the way. Other times though we simply don’t want to share the data in a hidden row or column. Assuming that the data in visible cells isn’t dependent on the data in hidden cells (i.e. if a formula references a hidden cell), we can use the macro below to automatically delete any hidden rows and columns in the active worksheet.

If you only want the hidden rows to be deleted, remove the three lines starting with For Ip = 256 through Next. To only delete the hidden columns, remove the next three lines starting with For lp = 65536 through Next.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub DeleteHiddenRowsColumns()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next

For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
Application.ScreenUpdating = True
End Sub

We put information from all types of sources into our Excel workbooks, usually just by copying and pasting or by using another program to extract data from a database. In either case, sometimes this produces undesired results. Empty cells could play tricks on certain features in Excel, such as pivot tables, that read an empty cell as 0. If we don’t want these cells to appear in the pivot table, a simple space is all you need to keep it from messing up your data.

To use this macro, select your data, and then run the macro. If your list of data is large, give the macro a couple seconds to run, as it will evaluate each cell in your list. It won’t remove data from cells that contain text, formulas, numbers, etc. It will only add a space to cells that are truly blank. After running this macro successfully, you really shouldn’t notice a visual difference in your spreadsheet. You’ll only notice a change if you know that a particular cell did not have a space in it prior to running the macro and notice that it does after running it.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module, or Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.).

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub Add_Spaces()
'MACROS BY EXCELZOOM.COM
Application.ScreenUpdating = False
Dim SelectedCell As Range

For Each SelectedCell In Selection
Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Application.ScreenUpdating = True
End Sub

We put information from all types of sources into our Excel workbooks, usually just by copying and pasting or by using another program to extract data from a database. In either case, sometimes this produces undesired results. Cells that may appear empty really aren’t, as they will often times contain spaces. Usually this isn’t a problem, but sometimes pivot tables or other features in Excel will read the space as a value, when it really should be read as 0.

The macro below will take whatever worksheet you’re on and will remove all the spaces from the otherwise aesthetically empty cells. It won’t remove spaces from cells that contain text, formulas, numbers, etc. After running this macro successfully, you really shouldn’t notice a visual difference in your spreadsheet. You’ll only notice a change if you know that a particular cell had a space prior to running the macro and notice that it doesn’t now.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module, or Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.).

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub Remove_Spaces()
'MACROS BY EXCELZOOM.COM

Dim rangeSheet As Range
Dim rangeText As Range

Set rangeText = Cells.SpecialCells( _
xlCellTypeConstants, _
xlTextValues)
For Each rangeSheet In rangeText
If Trim(rangeSheet.Value) = "" Then
rangeSheet.ClearContents
End If
Next
Set rangeText = Nothing
Set rangeSheet = Nothing
End Sub

Have you ever had a spreadsheet, where you wanted users to be able to enter information in a form, and have it populate the next available row in a given set of columns? It’s easier than you thought, and will make entering data more efficient and accurate.

The example below will use most of the common UserForm tools to give you a feel for how they work. You can later apply this example to your own custom forms.

This example will create a form to ask users to input information about your customers, and upon successfully filling out the form will populate a spreadsheet with the appropriate information. Note: There’s a full working example of this UserForm available to download by clicking here.

The first step is to set up your spreadsheet to accept the data you’ll ask your users to populate. (Note: don’t type the quotes shown below).

• Open a new workbook in Microsoft Excel. In “Sheet1″ change the sheet’s name to “Customer Data”.
• In cell A1, type “Business Name”.
• In cell B1, type “Customer Contact Name”.
• In cell C1, type “Street Address”.
• In cell D1, type “City”.
• In cell E1, type “State”.
• In cell F1, type “Zip”.
• In cell G1, type “Phone”.
• In cell H1, type “Fax”.
• In cell I1, type “Email”.
• In cell J1, type “Active”.
• In cell K1, type “Past Due”.

The second step is to create your UserForm and add the controls that users will ultimately populate with data.

• Open the Visual Basic Editor (Alt+F11).
• While in the Visual Basic Editor, insert a new UserForm (Insert | UserForm). Also make sure the Toolbox menu appears when you do this. If not, click View | Toolbox.
• To insert a control, select it from the Toolbox and drag it to where you want it in the form. Note: you can drag the edges of the UserForm and the controls to make them bigger/smaller, depending on how much data you’ll be entering.

You’ll want to enter the following controls below into your UserForm.

• 8 TextBoxes. 1 for each of the following: Business Name; Customer Contact Name; Street Address; City; Zip; Phone; Fax; Email. Note: You may also want to include a Label for each textbox to visually tell the user what information should be entered into each box.
• 1 ComboBox for the State field.
• 1 CheckBox. This is to check if the customer is an active customer.
• 1 Frame. Within this frame put five OptionButtons. This is where you’ll indicate if they’re past due, or not and if so, how long past due. This will populate column K above.
• Finally, 3 CommandButtons. One that will say “OK”, another that will say “Cancel” and a third that will say “Reset Form”.

Use the following configuration settings for each of the controls in your form.

Now that you’ve created your form in Visual Basic, you need to initialize it.

• View the form’s code, by clicking View | Code or F7.
• You’ll use the names of each form control in this process, so make sure you know what each is.
• Copy and paste the following code into your form’s code.


Private Sub UserForm_Initialize()

txtBName.Value = ""
txtCustContact.Value = ""
txtZip.Value = ""
txtStreet.Value = ""
txtPhone.Value = ""
txtFax.Value = ""
txtEmail.Value = ""
txtCity.Value = ""
With cboState

txtBName.SetFocus
End Sub

• This will clear any old data out of your UserForm and get it set for editing, by setting default values. If, for example the majority of your customers are from Texas, you could change the value for cboState.Value = “” to cboState.Value = “TX”. This saves the end user one additional step in the process. Also, for purposes of this example, we’ll assume that the majority of our customers are not past due with payments, so we’ll set opNo = True to set the Past Due option button of Not Overdue to be the default option.

Now, go back to the UserForm, by clicking frmCustInfo in the left pane. Now double click on your Cancel button. This brings you back to the code editor. You’ll see the following code:
Private Sub cmdCancel_Click()

End Sub

Enter the following in between those two lines: Unload Me
You code should look like this:
Private Sub cmdCancel_Click()

Unload Me

End Sub

Now do the same for the Reset Form button. This time, in between Private Sub cmdReset_Click() and End Sub, enter Call UserForm_Initialize, so your code should look like this:
Private Sub cmdReset_Click()

Call UserForm_Initialize

End Sub

Next, we’ll code the OK button, so we’ll follow the same process, but this time the code that will go in between Private Sub cmdOK_Click() and End Sub is:
ActiveWorkbook.Sheets(“Customer Data”).Activate
Range(“A2″).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtBName.Value
ActiveCell.Offset(0, 1) = txtCustContact.Value
ActiveCell.Offset(0, 2) = txtStreet.Value
ActiveCell.Offset(0, 3) = txtCity.Value
ActiveCell.Offset(0, 4) = cboState.Value
ActiveCell.Offset(0, 5) = txtZip.Value
ActiveCell.Offset(0, 6) = txtPhone.Value
ActiveCell.Offset(0, 7) = txtFax.Value
ActiveCell.Offset(0, 8) = txtEmail.Value
If chkActive = True Then
ActiveCell.Offset(0, 9).Value = “Yes”
Else
ActiveCell.Offset(0, 9).Value = “No”
End If
If opL90 = True Then
ActiveCell.Offset(0, 10).Value = “Less Than 90 Days”
ElseIf op90 = True Then
ActiveCell.Offset(0, 10).Value = “Over 90 Days”
ElseIf op180 = True Then
ActiveCell.Offset(0, 10).Value = “Over 180 Days”
ElseIf op360 = True Then
ActiveCell.Offset(0, 10).Value = “Over 360 Days”
Else
ActiveCell.Offset(0, 10).Value = “Not Overdue”
End If
Range(“A1″).Select
End Sub

Since we set the past due options “Enabled” property to False, we need to be able to tell the form when to set it to True. We’ll assume that we only want the past due options to be editable if the customer’s account is active. We can do so with the following code:
Private Sub chkActive_Change()
If chkActive = True Then
frPastDue.Enabled = True
opNo.Enabled = True
opL90.Enabled = True
op90.Enabled = True
op180.Enabled = True
op360.Enabled = True
Else
frPastDue.Enabled = False
opNo.Enabled = False
opL90.Enabled = False
op90.Enabled = False
op180.Enabled = False
op360.Enabled = False
End If
End Sub

Finally, we need to be able to let users bring up this form from within Excel. We can do this with a simple macro that looks like this:
Sub OpenCustomerInfoForm()
frmCustInfo.Show
End Sub
This macro needs to be placed either in a module, or in a Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.). It should not be placed in with the rest of your form’s code, as you won’t be able to run the code from Excel.

Download a full working example of the customer contact user form we just created.

Sometimes we setup Excel worksheets so that they flow logically on the screen. Often times they’re set up like you would read a book, top to bottom, left to right, but this isn’t always the best setup for printing.

Suppose you have a spreadsheet with detailed information in the first 50 rows, and then a summary in the next 10. There could be times where you don’t care to print the details, and only want the summary, or the opposite. Of course, you could set up your spreadsheet so that the details are in one worksheet and the summary is in another, each with their print areas set up correctly. But that would make viewing the spreadsheet on the screen that much more difficult, as you would have to click between the two in order to analyze the data.

This is where custom views help. Read below for a step-by-step example on how to setup custom views.

This example assumes that you have your detailed information (print area # 1) in the first 50 rows in columns A through M, and your summary information (print area # 2) in the next 10 rows in the same columns. You can set more than just two print areas using custom views, but for simplicity’s sake, we’ll use two for now.

• First, select print area # 1 (cells A1:M50)
• Click File | Print Area | Set Print Area
• Click File | Page Setup and set your print settings for that area (i.e. header/footer, portrait/landscape, letter/legal/other paper size, fit page to 1 by 1 or 100%, etc.)
• Click View | Custom Views and then click the “Add” button. In the dialog box, enter a description of the view (in this case, “print_detail” without the quotes would work)
• Save your file.

Now that you have your first print area setup, we’ll repeat the previous steps to setup the second print area for the summary section.

• Select print area # 2 (cells A51:M60)
• Click File | Print Area | Set Print Area
• Click File | Page Setup and set your print settings for that area (i.e. header/footer, portrait/landscape, letter/legal/other paper size, fit page to 1 by 1 or 100%, etc.)
• Click View | Custom Views and then click the “Add” button. In the dialog box, enter a description of the view (in this case, “print_summary” without the quotes would work)
• Save your file.

OK, both print areas are setup. Now we need to be able to use them.

• Click View | Custom Views
• You should see the two custom views we just created in the box, print_details and print_summary
• Click on the one you wish to print and then click the “Show” button
• You can double check that the print area is set to your liking by clicking File | Print Preview. This will show how your printout will look after printing. If you don’t need to make any changes, simply click File | Print.

Added Bonus – Set a Macro to Print Your Custom View

So far we’ve covered how to set a custom view to make printing easier. We’ll lets go one step further and make it even easier.

Insert the following macro code in your Visual Basic Editor to have Excel select the custom view and print it out all in one click!

Note: The macro code will print one custom view (currently set to print the details from the example above). You’ll need to copy this macro for as many custom views as you have. For each custom view change print_custom_view() to a unique name and also change the line “print_details” to whatever you have named your custom view.

Sub print_custom_view()

End Sub

It’s always useful to have a calendar in Excel. Having one lets you save it on a shared network drive, so everyone involved in a particular project knows what’s going on and important dates coming up. Creating a calendar in Excel can be a tedious task if done manually. Thankfully, using the Visual Basic Editor, you can easily create a calendar in Excel.

By creating a simple macro, you can have Excel ask a user to input the month and year, and let Excel do the rest!

I’m assuming that if you’re reading this you’ve already taken a look at my guide that describes how to install and use macros. If not, read up on the Single Workbook Use section for purposes of this macro.

I’ll explain each step of this macro code, and then put it all together at the end, to help you understand what it’s doing.

The first part of the code is to make sure the current sheet is unprotected. This will enable the macro to make the necessary edits to the sheet. This is accomplished with the following line:
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Scenarios:=False
Application.ScreenUpdating = False

Next, you’ll want to make sure that any errors from data entry are resolved so that the macro can work properly. (More on this later).
On Error GoTo ErrorMessage

Here, we’ll tell the macro to clear whatever is in the cells where we want the calendar to appear.
Range(“A1:G14″).Clear

In order to get users to input a date, you’ll need to tell Excel to open an input box and ask the user to input the date. You’ll also want the date the user puts into the box to be defined as a variable. If they don’t input anything, or cancel out of the input box, you’ll want to end the process.
InputDate = InputBox(“Type in Month and Year for Calendar “)
If InputDate = “” Then Exit Sub

In order for the macro to properly format the calendar in Excel, it needs to know what the first day of the month is. Since we just had our users input the month and year, we need to convert this into a number that Excel recognizes as a date. You’ll also want to make sure that the date it is formatting is the first day in the month, otherwise tell it to change to the first day.
FirstDay = DateValue(InputDate)
If Day(FirstDay) <> 1 Then
FirstDay = DateValue(Month(FirstDay) & “/1/” & _
Year(FirstDay))
End If

Next, you’ll want to start formatting your cells so it starts to look like a calendar.
Range(“A1″).NumberFormat = “mmmm yyyy” — This formats the header to display just the month and year.
Here, we set up the cells that will contain the days of the week
With Range(“A1:G1″)
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.Font.Size = 18
.Font.Bold = True
.RowHeight = 35
End With
With Range(“A2:G2″)
.ColumnWidth = 14
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
.Font.Size = 12
.Font.Bold = True
.RowHeight = 20
End With
Range(“A2″) = “Sunday”
Range(“B2″) = “Monday”
Range(“C2″) = “Tuesday”
Range(“D2″) = “Wednesday”
Range(“E2″) = “Thursday”
Range(“F2″) = “Friday”
Range(“G2″) = “Saturday”

Now we want to input the dates, and most importantly we want to put them in the correct location.
With Range(“A3:G8″)
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Font.Size = 18
.Font.Bold = True
.RowHeight = 21
End With
Range(“A1″).Value = Application.Text(InputDate, “mmmm yyyy”)
DayofWeek = Weekday(StartDay)
CurrenttYear = Year(StartDay)
CurrentMonth = Month(StartDay)
LastDay = DateSerial(CurrentYear, CurrentMonth + 1, 1)
Select Case DayofWeek
Case 1
Range(“A3″).Value = 1
Case 2
Range(“B3″).Value = 1
Case 3
Range(“C3″).Value = 1
Case 4
Range(“D3″).Value = 1
Case 5
Range(“E3″).Value = 1
Case 6
Range(“F3″).Value = 1
Case 7
Range(“G3″).Value = 1
End Select

OK, now we have the first day of the month in, now we need to get the rest in, but considering that months all have different numbers of days, and some months (February) can have a different number of days from year to year, we need to tell the macro to only put as many days as there are in the month.
For Each cell In Range(“A3:G8″)
RowCell = cell.Row
ColCell = cell.Column
If cell.Column = 1 And cell.Row = 3 Then
ElseIf cell.Column <> 1 Then
If cell.Offset(0, -1).Value >= 1 Then
cell.Value = cell.Offset(0, -1).Value + 1
If cell.Value > (LastDay – FirstDay) Then
cell.Value = “”
Exit For
End If
End If
ElseIf cell.Row > 3 And cell.Column = 1 Then
cell.Value = cell.Offset(-1, 6).Value + 1
If cell.Value > (LastDay – FirstDay) Then
cell.Value = “”
Exit For
End If
End If
Next

Now that we have the dates, we’ll want to create a little space to enter in some data. To do this we’ll create a new row under each row that contains the dates.
For x = 0 To 5
Range(“A4″).Offset(x * 2, 0).EntireRow.Insert
With Range(“A4:G4″).Offset(x * 2, 0)
.RowHeight = 65
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Font.Size = 10
.Font.Bold = False
.Locked = False
End With

Now we’ll format the calendar and the rest of the sheet so it actually looks like a calendar.
With Range(“A3″).Offset(x * 2, 0).Resize(2, 7).Borders(xlLeft)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Range(“A3″).Offset(x * 2, 0).Resize(2, 7).Borders(xlRight)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Range(“A3″).Offset(x * 2, 0).Resize(2, 7).BorderAround Weight:=xlThick, ColorIndex:=xlAutomatic
Next
If Range(“A13″).Value = “” Then Range(“A13″).Offset(0, 0) .Resize(2, 8).EntireRow.Delete
ActiveWindow.DisplayGridlines = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1

Now we’ll end the macro, assuming there were no errors encountered along the way.
Application.ScreenUpdating = True
Exit Sub

If there were errors, we’ll need to define the error message from above.
ErrorCorrect:
MsgBox “You may not have entered your Month and Year in the correct format.” _
& Chr(13) & “Spell the Month correctly” _
& ” (or use 3 letter abbreviation)” _
& Chr(13) & “and 4 digits for the Year”
InputDate = InputBox(“Type in Month and Year for Calendar”)
If InputDate = “” Then Exit Sub
Resume
End Sub

Now that you’ve seen the pieces, here’s the complete code.

Sub Create_Calendar()
'
'MACROS BY EXCELZOOM.COM
'
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Scenarios:=False

Application.ScreenUpdating = False

On Error GoTo ErrorMessage

ErrorMessage:
MsgBox "You may not have entered your Month and Year correctly." _
& Chr(13) & "Spell the Month correctly" _
& " (or use 3 letter abbreviation)" _
& Chr(13) & "and 4 digits for the Year"
InputDate = InputBox("Type in Month and Year for Calendar")
If InputDate = "" Then Exit Sub
Resume
End Sub


Sometimes it is useful to be able to generate random numbers in Excel that are limited to a certain range.

Maybe you have an “employee of the month” contest and you just can’t pick one. Assign a number to each employee, enter the first and last numbers and run the macro. And who said decision making was difficult!

Or, on the serious side you could be doing some testing that requires a random number to fall within a certain range. You can select as many cells as you wish to populate with a random number, and each one will be assigned a truly random numbers. (i.e. All numbers within the range have the ability to be repeated).

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module. The only two spots you’ll need to change are the numbers 1 and 200 after “Lowest =” and “Highest =”. Replace these values with whatever high/low values you wish to use.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub RandomNumber()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
Dim cell As String
Dim cellRg As Range
Dim cellselection As Range
Dim Lowest As Double
Dim Highest As Double
Dim R As Double
Lowest = 1 'ENTER THE LOWEST NUMBER HERE
Highest = 200 'ENTER THE HIGHEST NUMBER HERE
For Each cellselection In Selection
R = Int((Highest - Lowest + 1) * Rnd() + Lowest)
cell = cellselection
cell = R
cellselection = cell
Next
Application.ScreenUpdating = True
End Sub

Insert your macro code into a module, start with Sub Auto_Open(). Enter whatever commands you wish to have executed on opening the file and end with End Sub.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “Msgbox “Hello” ” with whatever macro commands you wish to have run whenever your file is opened.

Need help? Use our nifty guide to help figure out how to install and use your macros.


Sub Auto_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub


Use the following if you wish to put the macro in your workbook as opposed to in a module. Again replace “Msgbox “Hello”" with your macro command:

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub