Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Selecting a Color in a UserForm

The example in this section is a function that displays a dialog box (similar in concept to the MyMsgBox function, presented earlier). The function, named GetAColor , returns a color value:

Public ColorValue As Variant Function GetAColor() As Variant UserForm1.Show GetAColor = ColorValue End Function

You can use the GetAColor function with a statement like the following:

UserColor = GetAColor()

Executing this statement displays the UserForm. The user selects a color and clicks OK. The function then assigns the user 's selected color value to the UserColor variable.

The UserForm, shown in Figure 15-23, contains three ScrollBar controls - one for each of the color components (red, green, and blue). The value range for each scrollbar is from 0 to 255.

Figure 15-23: This dialog box lets the user select a color by specifying the red, green, and blue components.

The GetAColor UserForm has another twist: It remembers the last color that was selected. When the function ends, the three Scrollbar values are stored in the Windows Registry, using this code ( APPNAME is a string defined in Module1 ):

SaveSetting APPNAME, "Colors", "RedValue", ScrollBarRed.Value SaveSetting APPNAME, "Colors", "BlueValue", ScrollBarBlue.Value SaveSetting APPNAME, "Colors", "GreenValue", ScrollBarGreen.Value

The UserForm_Initialize procedure retrieves these values and assigns them to the scrollbars:

ScrollBarRed.Value = GetSetting(APPNAME, "Colors", "RedValue", 128) ScrollBarGreen.Value = GetSetting(APPNAME, "Colors", "GreenValue", 128) ScrollBarBlue.Value = GetSetting(APPNAME, "Colors", "BlueValue", 128)

The last argument for the GetSetting function is the default value, which is used if the Registry key is not found. In this case, each color defaults to 128, which produces middle gray.

The SaveSetting and GetSetting functions always use this Registry key:

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\

Figure 15-24 shows the Registry data, displayed with the Windows Regedit.exe program.

Figure 15-24: The user's ScrollBar values are stored in the Windows Registry and retrieved the next time the GetAColor function is used.