Selecting a range

In some cases, you may want the user to select a range while a dialog box is displayed. An example of this choice occurs in the second step of the Excel Chart Wizard. The Chart Wizard guesses the range to be charted, but the user is free to change it from the dialog box.

To allow a range selection in your dialog box, add a RefEdit control. The following example displays a dialog box with the current region's range address displayed in a RefEdit control, as shown in Figure 18-6. The current region is the block of nonempty cells that contains the active cell. The user can accept or change this range. When the user clicks OK, the procedure makes the range bold.

This example assumes the following: 1 You have a UserForm named UserForm1.

1 The UserForm contains a CommandButton control named OKButton. 1 The UserForm contains a CommandButton control named CancelButton. 1 The UserForm contains a RefEdit control named RefEdit1.

The code is stored in a VBA module and shown here. This code does two things: initializes the dialog box by assigning the current region's address to the RefEdit control and displays the UserForm.

Sub

BoldCells()

Exit if worksheet is not active

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

Select the current region

ActiveCell.CurrentRegion.Select

Initialize RefEdit control

UserForm1.RefEdit1.Text = Selection.Address

Show dialog

UserForml.Show

End

Sub

The following procedure is executed when the OK button is clicked. This procedure does some simple error checking to make sure that the range specified in the RefEdit control is valid.

Private Sub OKButton_Click()

On Error GoTo BadRange

Range(RefEdit1.Text).Font.Bold

= True

Unload UserForm1

Exit Sub

BadRange:

MsgBox "The specified range is

not valid."

End Sub

If an error occurs (most likely an invalid range specification in the RefEdit control), the code jumps to the BadRange label and a message box is displayed. The dialog box remains open so the user can select another range.

Figure 18-6:

This dialog box lets the user select a range.

Figure 18-6:

This dialog box lets the user select a range.

M

refedit demo.xls

UW

■J

I «

»

«

F

G I H

-i ni

1

17| 8

10

75

11

64

3

5

45

70

13

6

RefEdit Demo ¡Ü

4

30

22

15 1

3

Range to make bold:

5

73

56

87 70

24

b

86 93

69 95

49

I $A$li$F$22

-I

/ 8

31 31

'it 68

30 1

70 62

26 63

9

72

97

58

21

42

10

22

28

92

32

76

11

15

9U

55

72

22

12

75

14

21

98

18

9

13

82

79

43

96

50

15

14

89

38

92

69

32

33

15

1 38

93

78

58

45

16

37

42

11

63

0. 60 j

17

40

59

14

57

2

37

18

12

1

14

67

74

46

19

15

10

25

27

70

36

20

61

41

84 47

29

71

21

51 78

21 89

16

95

22

74

5

31

11

7

55

V

► h |\ Sheetl/

]<l

_I

> I

0 0

Post a comment