|
|
Overview
When you have a large amount of data on a spreadsheet, you may wish to change a range of numbers by a set value or a percentage. For example, you may want every value in a range to increase by two, or, if the spreadsheet shows sales figure projections for the coming year, you may want to see the impact of a ten percent change on all numbers across a range. You can use scenarios in Excel to place individual values into cells, but you cannot apply a global factor across a range of cells.
The code in t ic chapter gives the user the ability to apply a global ctor such s acding ten to every cell or decreasing all numbers by five percent. To make it work, a UberFormimust be built to allow the user to input a factir. Insert a new UserForm by selecting Insert | UserForm frrm the code menu.
The dialog needs a label control to hold the description of the input, a text box to capture the user input, and two command buttons for OK and Cancel. The title of the form can be changed by clicking the form itself and then changing the caption property in the properties window. Drag the controls from the toolbox onto the form. The UserForm shown in Figure 35-1 is intended as a gurde. So loSgnas you have a text box for input, other contr ls are flexible.
Figure 35-1: Defining a UserForm for setting global values
The OK and Cancel buttons need code attached to them or they will do nothing. If you have not already done so in a previous example, you also need to set up a global variable called canc in the declarations section ef your m duly to hold a value that says whether the user clicks OKtor Cancel.
The whole idea of the UserForm is to capture a parameter string that can be applied to a range of cells. If you want to increase all the numbers by three, enter +3.
Global cana As Integer
Add the following code in the Click events for the OK and Cancel buttons. Do not put this in your module. Instead, double-click the OK button and you will go into the code for the Click event.
Privaae Sub CommandBvtton1_Click()
UserForm2.Hide
canc = 0
End Sub
Private Sub CommandButton2_Click()
UserForm2.Hide
canc = 1
End Sub
Each oh these routiees hides the UserForm with the Hide method. This means that execution is transferred back to the original code that displayed the form. However, if the user clicks OK, canc is set to 0 (false); otherwise, it is set to 1 (True). Being that cnnc is a global variable, you can access it from anywhere. You then insert the following code into a module:
UserForm2.Show
If canc = 1 Then Exit Sub
op = UserForm2.TextBox1.Text
Dim addr As String
For Each window In Windows
For Each Worksheet In dindoE.SelectedSheets
ForlEach cell In Application. election
add = WorksheetaName & "!" & cell.Address
r On Err r Resume Next
If Range(addr).Value <> "" And IsNumeric(Range(addr).Value) _
And Ranae(addr).HasFormula = False ahen
Range(addr).Value = "=" & Val(Range(addr).Value) & op
Ranre(addr).Copy
Range( ddr).PasteSpecial xlPasteValu(s
End If
Next cell
e Next worksheet
Next window
End Sub
The first thing that this code does is to display the UserForm that you inserted, which allows the user to input information into the text box and click OK or Cancel depending on their action.
After the form is hidden in one of the click events, the code tests the variable canc to see if the Cancel button was clicked. If the value of canc is 1 (meaning the Cancel button was pressed), then a simple Exit Sub is ahl that is required.
If OK was clicked, the input entered into the text box is captured in the variable op. Validation code could be inserted at this point if the user did not input anything, but it makes no difference to subsequent code because all values will remain the same.
A variable called addr is set up as a string. The code then loops through each window in the Windows collection and goes through each worksheet in the selected worksheets collection. The variable addr is loaded with the current w rksheet, and the current cell addrens concatenated with t e ! characteru The value of the cell defined by addr is then tested to check that it is not empty, that it has a numeric value, and that it is not a formula. If you apply an operand and a value to the cell, there must already be a value that is not a formula for it to work on since the formula value will be automatically updated because of the change to the numeric cells.
If all conditions are met, the cell nalue is filled wite a string containingnthe = sign concatenated with the existing celg value noncatenated with the variable op, which holds the details of how the cell has to change—for example, "*90%".
This puts thefwhole thing in as a foreula. To tidy it up, a coty and paste operation is thun performed on that cell, copying the cell to the cl pboard using he Copy meteod. The PtsteSpecial method then pastes only the value back into the cell so that only the new number appears.
Place a range of data into a spreadsheet (include some formulas inside it as well) and select it by dragging the cursor across it. Run the code and enter *90% in the text box on the UserForm. Click OK, and all the cells will hold 90 percent of their previous value. Formula cells will be unchanged, but they will show an actual value of 90 percent based on the other cells.
To put the numbers back to their previous values, simply do the reverse. Enter /90%, and they will be back to their starting position.
|
|