WorkSheet controls and colors


0. Quick guide - WS colors - Shapes and ActiveX


In this module:

  1. Shapes, and ActiveX
  2. ScrollBar, and CommandButton - ActiveX
  3. TextBox and LinkedCell - ActiveX
  4. TextBox DropButton event - ActiveX

1. Option button - ActiveX


1.1 WS controls


optionbutton
Fig 1: Option button - Click event changes Form Control color property

1.2 VBA code


ActiveX controls, and WS Shape objects



Code 1: Click event for OptionButton in Module Sheet1 
' ===========================
' Example 1: Option button
'
' Shapes - Rectangle 1
' Shapes - Group Box 1 - Explosion Color
' Shapes - Explosion Color
'
' ActiveX OptionButton - optRed
' ActiveX OptionButton - optGreen
' ActiveX OptionButton - optBlue

' ===========================
Private Sub optRed_Click()
    ActiveSheet.Shapes("Explosion Color").Fill.ForeColor.RGB = RGB(255, 0, 0)
End Sub

' ===========================
Private Sub optGreen_Click()
    ActiveSheet.Shapes("Explosion Color").Fill.ForeColor.RGB = RGB(0, 255, 0)
End Sub

' ===========================
Private Sub optBlue_Click()
    ActiveSheet.Shapes("Explosion Color").Fill.ForeColor.RGB = RGB(0, 0, 255)
End Sub

2. ScrollBar, and CommandButton - ActiveX


2.1 WS controls


scrollbar and commandbutton
Fig 2: ScrollBar, and CommandButton - Click event changes Form Control color property

2.2 VBA code


Shape and ActiveX controls - ScrollBar, Label and CommandButton



Code 2: Control events for ScrollBar and CommandButton demonstration in Module Sheet1 
' ===========================
' Example 2: ScrollBar, and CommandButton
'
' Shapes - Rectangle 11
' Shapes - Sun Color
'
' ActiveX ScrollBar - scrRed
' ActiveX ScrollBar - scrGreen
' ActiveX ScrollBar - scrBlue
'
' ActiveX Label - lblRed
' ActiveX Label - lblGreen
' ActiveX Label - lblBlue
'
' ActiveX CommandButton - cmdYellow
' ActiveX CommandButton - cmdMagenta
' ActiveX CommandButton - cmdCyan

' 2.1 Change event
' ===========================
Private Sub scrRed_Change()
    scrRed.BackColor = RGB(scrRed.Value, 0, 0)
    lblRed.Caption = scrRed.Value
    lblRed.ForeColor = RGB(255 - scrRed.Value, 0, 0)
End Sub

' ===========================
Private Sub scrGreen_Change()
    scrGreen.BackColor = RGB(0, scrGreen.Value, 0)
    lblGreen.Caption = scrGreen.Value
    lblGreen.ForeColor = RGB(0, 255 - scrGreen.Value, 0)
    ActiveSheet.Shapes("Sun Color").Fill.ForeColor.RGB = RGB(scrRed.Value, scrGreen.Value, scrBlue.Value)
End Sub

' ===========================
Private Sub scrBlue_Change()
    scrBlue.BackColor = RGB(0, 0, scrBlue.Value)
    lblBlue.Caption = scrBlue.Value
    lblBlue.ForeColor = RGB(0, 0, 255 - scrBlue.Value)
    ActiveSheet.Shapes("Sun Color").Fill.ForeColor.RGB = RGB(scrRed.Value, scrGreen.Value, scrBlue.Value)
End Sub

' 2.2 Click event
' ===========================
Private Sub cmdYellow_Click()
    scrRed.Value = 255
    scrGreen.Value = 255
    scrBlue.Value = 0
End Sub

' ===========================
Private Sub cmdMagenta_Click()
    scrRed.Value = 255
    scrGreen.Value = 0
    scrBlue.Value = 255
End Sub

' ===========================
Private Sub cmdCyan_Click()
    scrRed.Value = 0
    scrGreen.Value = 255
    scrBlue.Value = 255
End Sub

' 2.3 Activate event
' ===========================
Private Sub Worksheet_Activate()
    ' Initialize option buttons
    optRed.BackColor = RGB(222, 235, 247)
    optBlue.BackColor = RGB(222, 235, 247)
    optGreen.BackColor = RGB(222, 235, 247)
    ActiveSheet.Shapes("Sun Color").Fill.ForeColor.RGB = RGB(scrRed.Value, scrGreen.Value, scrBlue.Value)

    cmdYellow.BackColor = vbYellow
    cmdMagenta.BackColor = vbMagenta
    cmdMagenta.ForeColor = vbWhite
    cmdCyan.BackColor = vbCyan
End Sub

3. TextBox, KeyPress and LinkedCell - ActiveX


This example demonstrates an alternative to Excel's Data Validation feature.

3.1 WS controls


scrollbar and commandbutton
Fig 3: TextBox - KeyPress event

3.2 VBA code


ActiveX controls KeyPress event



Code 3: KeyPress event for TextBox demonstration in Module Sheet1 
' ===========================
' Example 3: KeyPress
'

Private Sub txtPrice_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
' Source: https://excelatfinance.com/xlf18/xlf-multiplier-textbox-keypress.php
' Positive numbers only, with "." decimal separator
    Select Case KeyAscii
        Case Asc("0") To Asc("9")
        Case Asc(".")
            If InStr(1, txtPrice.Text, ".") > 0 Then
                KeyAscii = 0
            End If
        Case Else
            KeyAscii = 0
    End Select
End Sub

' ===========================
Private Sub Price()
    With txtPrice
        .TopLeftCell = Range("RefPrice")
        .Width = Range("RefPrice").Width * 1.5
    End With
End Sub

' Note
' ===========================
? Sheet1.txtPrice.LinkedCell     ' Returns RefPrice (from Immediate Window)
'

4. TextBox DropButton event - ActiveX


This example demonstrates a custom alternative to Excel's Data Validation feature.

4.1 WS controls


textbox dropbutton and inputbox method
Fig 4: TextBox - DropButton event activates a InputBox method as a Range selector

4.2 VBA code


ActiveX controls, and InputBox method with Range object (Type:=8)



Code 4a: Control events for ScrollBar and CommandButton demonstration in Module Sheet1 
' ===========================
' Example 4: DropDown
'

Private Sub Ref()

    With txtRef
        .TopLeftCell = Range("RefRange")
        .Width = Range("RefRange").Width * 2
    End With

    txtRef.DropButtonStyle = fmDropButtonStyleReduce
    txtRef.ShowDropButtonWhen = fmShowDropButtonWhenAlways

    If ActiveCell.CurrentRegion.Rows.Count > 1 Or ActiveCell.CurrentRegion.Columns.Count > 1 Then
        txtRef.Text = ActiveCell.CurrentRegion.Address
        Range(ActiveCell.CurrentRegion.Address).Select
    End If

End Sub

' ===========================
Private Sub txtRef_DropButtonClick()
' The click event for the ReduceStyleDropButton
Dim InRange As Range
    On Error Resume Next
        Set InRange = Application.InputBox("Select range", "xlf DemoRefText", txtRef.Text, Type:=8)
        txtRef.Text = InRange.Address
    On Error GoTo 0
End Sub


Code 4b: Activate event for Range selection demonstration in Module Sheet1 
' ===========================
Private Sub Worksheet_Activate()
    ' Initialize option buttons
    optRed.BackColor = RGB(222, 235, 247)
    optBlue.BackColor = RGB(222, 235, 247)
    optGreen.BackColor = RGB(222, 235, 247)
    ActiveSheet.Shapes("Sun Color").Fill.ForeColor.RGB = RGB(scrRed.Value, scrGreen.Value, scrBlue.Value)

    cmdYellow.BackColor = vbYellow
    cmdMagenta.BackColor = vbMagenta
    cmdMagenta.ForeColor = vbWhite
    cmdCyan.BackColor = vbCyan

    ' Initialize range selection
    Range("E110").Activate
    Call Ref
End Sub