Listing Providing Dynamic Scaling to Your Worksheets

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Me.Range("ScaleFactor").Address Then

ScaleData End If End Sub

Private Sub ScaleData()

If Me.Range("ScaleFactor").Value = "Normal" Then Me.Range("ScaleRange").NumberFormat = "#,##0"

Else

Me.Range("ScaleRange").NumberFormat = "#," End If End Sub

How easy is that? Just place this code in the worksheet's code module and set up two named ranges. ScaleRange is the range containing the data to be scaled while ScaleFactor is the range that specifies how to scale it. For this example, I just applied a data validation list on the ScaleFactor cell to provide a drop-down list with the scaling options. As Figure 10.13 demonstrates, this is just a simple example of how to do it. Apply your imagination and I bet you can come up with some excellent ideas that are easy to implement.

Figure 10.13

From the user's perspective, toggling back and forth is as simple as choosing from a drop-down list.

L' Microsoft office EHcel2003 lit ! , 11 <>.,: t'r 10 Examplesjtfs

EC®

File Edt View insert Format Tools

• Window Hs^

Typs a quBation icr help

- _ 5 x

il

. - : 10 . B [71 s 5 a 3 %

• Tog a m *

§■A-A.

i

i -J

j _| J -'J Jld « ä Al lita 1 Only with Chames.

.. EndRewew... M

1 ScaleFaclor f* In thousands

A

B

a

D

E

F A

1

SIMPLE INCOME STATEMENT

2

SIMPLE CORP.

3

September 26. 2003

¿

5

Scaled |

\!n thoussri

[q

6

1 Normal

7

till '''itii,:

■ Feb

Mai

1st Oti

3

SALARIES

16

16

16

47

3

COMMISSIONS

3

3 3

8

10

FRINGE BENEFITS

I I

2' 2^

6

11

OTHER EMPLOYEE EXPENSES

1

12

TRAVEL & ENTERTAINMENT t

1 1

2

13

OTHER COS

14

FREIGHT 4 POSTAGE 1

T V.

2

IS

MANUFACTURING

to

ID

10

30

16

MARKETING

S

S

E:

15

17

TELEPHONE & NETWORK CHARGES

2

2

2

G

18

STATIONARY & SUPPLIES t

1 1

2

IS

MAINTENANCE & FACILITIES 8

6 8

14

20

PROFESSIONAL FEES _| 1

Ï 1

21

DEPRECIATION

3

3

3

9

22

MISCELLANEOUS EXPENSES

2

2

2

5

23

TOTAL EXPENSES

53

53

53

15S

i i

V

Ii 1

k H XscaHnfl/Basic Chert / ,

jfljl

; _

> J

Was this article helpful?

0 0

Post a comment