Dick Kusleika - Excel VBA Programming For Dummies

14 Chapter 15FIGURE 15-1: A simple message box.FIGURE 15-2: A simple message box, with two buttons.FIGURE 15-3: The MsgBox function’s buttons argument determines what appears in ...FIGURE 15-4: This dialog box, displayed by the MsgBox function, displays a titl...FIGURE 15-5: The InputBox function displays this dialog box.FIGURE 15-6: Another example of using the InputBox function.FIGURE 15-7: Using the Application.InputBox method to get a range.FIGURE 15-8: The GetOpenFilename method displays a customizable dialog box and ...FIGURE 15-9: Displaying one of Excel's dialog boxes by using VBA.FIGURE 15-10: Using the Customize Ribbon tab to identify a command name.

15 Chapter 16FIGURE 16-1: You can get information from the user by displaying a UserForm.FIGURE 16-2: A new UserForm object.FIGURE 16-3: Use the Properties windows to change the properties of UserForm co...FIGURE 16-4: The UserForm with two CommandButton controls.FIGURE 16-5: This is the UserForm after adding three OptionButton controls insi...FIGURE 16-6: Assign a shortcut key to execute the ChangeCase macro.FIGURE 16-7: Adding the ChangeChase macro to the Quick Access toolbar.FIGURE 16-8: The UserForm is in action.FIGURE 16-9: The text has been converted to uppercase.

16 Chapter 17FIGURE 17-1: A UserForm in the VBE, with a few controls added.FIGURE 17-2: Use the Properties window to make design-time changes to a control...FIGURE 17-3: Change some properties by selecting from a drop-down list of valid...FIGURE 17-4: CheckBox Controls in a UserForm.FIGURE 17-5: ComboBox controls in a UserForm.FIGURE 17-6: CommandButton controls.FIGURE 17-7: An Image control displays a photo.FIGURE 17-8: Label controls can take on many different looks.FIGURE 17-9: ListBox controls.FIGURE 17-10: Use a MultiPage control to create a tabbed dialog box.FIGURE 17-11: Two sets of OptionButton controls, each contained in a Frame cont...FIGURE 17-12: Two RefEdit controls.FIGURE 17-13: A ScrollBar control with a Label control below it.FIGURE 17-14: SpinButton controls.FIGURE 17-15: TextBox controls.FIGURE 17-16: ToggleButton controls.FIGURE 17-17: Choose Format ⇒ Align to change the alignment of UserForm con...FIGURE 17-18: The Tab Order dialog box.FIGURE 17-19: Use labels to provide direct access to controls that don’t have a...

17 Chapter 18FIGURE 18-1: This dialog box logs dinner choices for guests.FIGURE 18-2: Executing the LogDinnerGuest procedure displays the dialog box.FIGURE 18-3: Use the custom dialog box for data entry.FIGURE 18-4: Determining which item in a list box is selected.FIGURE 18-5: Determining the selected items in a list box that allows multiple ...FIGURE 18-6: This dialog box lets the user select a range.FIGURE 18-7: This dialog box contains three sets of OptionButton controls.FIGURE 18-8: A UserForm with a spin button and a companion text box.FIGURE 18-9: This UserForm functions as a progress indicator for a lengthy macr...FIGURE 18-10: The progress-indicator UserForm.FIGURE 18-11: The three tabs of a MultiPage control.FIGURE 18-12: Displaying a chart in a UserForm.

18 Chapter 19FIGURE 19-1: The Customize Ribbon tab of the Excel Options dialog box.FIGURE 19-2: The View tab with a new group named Text To Speech.FIGURE 19-3: RibbonX code displayed in the Custom UI Editor.FIGURE 19-4: The VBA callback procedure that is executed by clicking the Ribbon...FIGURE 19-5: Proof that adding a new Ribbon command using XML is actually possi...FIGURE 19-6: A control on the Add-in tab isn’t flashy, but it gets the job done...FIGURE 19-7: The Cell shortcut menu showing a custom menu item: Change Case.

19 Chapter 20FIGURE 20-1: Using the Commission function in a worksheet.FIGURE 20-2: Using the Commission2 function, which takes two arguments.FIGURE 20-3: Using a custom function to sum only odd numbers.FIGURE 20-4: Using the ExtractElement function to return an element from a stri...FIGURE 20-5: Using the MonthNames function to return a 12-element array.FIGURE 20-6: Using a custom function to return a sorted range.FIGURE 20-7: By default, the Insert Function dialog box doesn’t provide a descr...FIGURE 20-8: The custom function now displays a description.FIGURE 20-9: By default, the Function Arguments dialog box displays Function ar...

20 Chapter 21FIGURE 21-1: The Add-Ins dialog box lists all the add-ins known to Excel.FIGURE 21-2: The UserForm for the Change Case add-in.FIGURE 21-3: Use the Properties section to enter descriptive information about ...FIGURE 21-4: The Add-Ins dialog box has the new add-in selected.FIGURE 21-5: Making an add-in not an add-in.

21 Chapter 22FIGURE 22-1: A single apostrophe in front of any line turns that line into a co...FIGURE 22-2: The Edit toolbar allows you to select entire blocks of code and ap...FIGURE 22-3: Holding down the Ctrl key while dragging code creates a copy of th...FIGURE 22-4: Pressing Shift+F2 with your cursor on a function or variable name ...FIGURE 22-5: Click the Procedure View button to show only the active procedure.FIGURE 22-6: Press F8 key to step through each line of your macro at your own p...FIGURE 22-7: You can click and drag the yellow arrow while stepping through you...FIGURE 22-8: A breakpoint is marked by a dot in the left margin along with shad...FIGURE 22-9: Showing the ending characters in a variable tooltip.FIGURE 22-10: Leaving an unfinished line of code, even for a second, results in...FIGURE 22-11: Uncheck the Auto Syntax Check option to prevent warning messages ...

1 Cover

2 Title Page

3 Copyright

4 Table of Contents

5 Begin Reading

6 Index

7 About the Author

1 i

2 ii

3 1

4 2

5 3

6 4

7 5

8 6

9 7

10 8

11 9

12 10

13 11

14 12

15 13

16 14

17 15

18 16

19 17

20 18

21 19

22 20

23 21

24 22

25 23

26 24

27 25

28 26

29 27

30 29

31 30

32 31

33 32

34 33

35 34

36 35

37 36

38 37

39 38

40 39

41 40

42 41

43 42

44 43

45 44

46 45

47 46

48 47

49 48

50 51

51 52

52 53

53 54

54 55

55 56

56 57

57 58

58 59

59 60

60 61

61 62

62 63

63 64

64 65

65 67

66 68

67 69

68 70

69 71

70 72

71 73

72 74

73 75

74 76

75 77

76 78

77 79

78 80

79 81

80 83

81 84

82 85

83 86

84 87

85 88

86 89

87 90

88 91

89 92

90 93

91 94

92 95

93 97

94 98

95 99

96 100

97 101

98 102

99 103

100 104

101 105

102 106

103 107

104 108

105 109

106 110

107 111

108 112

109 113

110 114

111 115

112 116

113 117

114 118

115 119

116 120

117 121

118 122

119 123

120 124

121 125

122 126

123 127

124 128

125 129

126 130

127 131

128 132

129 133

130 134

131 135

132 136

133 137

134 138

135 139

136 140

137 141

138 142

139 143

140 144

141 145

142 146

143 147

144 148

145 149

146 151

147 152

148 153

149 154

150 155

151 156

152 157

153 158

154 159

155 160

156 161

157 162

158 163

159 164

160 165

161 166

162 167

163 168

164 169

165 170

166 171

167 172

168 173

169 174

170 175

171 176

172 177

173 178

174 179

175 180

176 181

177 182