Wednesday, December 21, 2016

Excel Flashing Cell

The Flashing Cell When Certain Condition Is Met

         In our example, if the value of cell A1 is greater than 5, this cell starts to flash. The flashing event is realized by changing the background color and font color of the cell at a particular time (firstly red then white color) .


Tuesday, December 13, 2016

Creating New Color Palette With Macro

Creating Color Palette With Vba Codes



       If you want, you can also create new hues with this color palette.
   
The codes we assign to button on the worksheet:
"
...
Public Function Execute() As Boolean
  Dim cc As TChooseColor
  Dim Ret As Long

  With cc
    .lStructSize = Len(cc)
    .hwndOwner = Application.Hwnd
    .lpCustColors = String$(64, vbNullChar)
    .flags = 0
    If RGBINIT Then .flags = .flags + CC_RGBINIT
    If FULLOPEN Then .flags = .flags + CC_FULLOPEN
    If PREVENTFULLOPEN Then .flags = .flags + CC_PREVENTFULLOPEN
    If SHOWHELP Then .flags = .flags + CC_SHOWHELP
    .rgbResult = Color
    Ret = ChooseColor(cc)
    If Ret = 0 Then
      Execute = False
    Else
      If .rgbResult > RGB(255, 255, 255) Then
        Execute = False
      Else
        Execute = True
        Selection.Interior.Color = .rgbResult
      End If
    End If
  End With
End Function

Sub ColorTime()
Call Execute
End Sub
...
"

Sunday, December 4, 2016

Excel Change Background Color of Selected Cells With Scrollbar Control On Userform

Excel Vba Change Cell's Background Color

          The userform starts as automatically when the worksheet is opened .Background color of selected cells can be changed with scrollbar control on this userform.
The scrollbar's min value is 0, the maximum value is 56.
Related codes:
"Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ScrollBar1_Change()
TextBox1.Value = ScrollBar1.Value
Selection.Interior.ColorIndex = ScrollBar1.Value
End Sub

Private Sub UserForm_Initialize()
ScrollBar1.Min = 0
ScrollBar1.Max = 56
End Sub
"

Friday, December 2, 2016

Hide & Unhide Columns With Combobox

Displaying Selected Column From Combobox

     
            In this template, only the selected column from the combobox is displayed ,other columns are hidden.The used codes in this template :
" Private Sub CheckBox1_Click()
ActiveSheet.Cells.EntireColumn.Hidden = False
End Sub

Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Me.ComboBox1.DropDown
End Sub

Private Sub UserForm_Initialize()
  Dim lst_column As Integer
    lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
For j = 2 To lst_column
   ComboBox1.AddItem Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value
Next j
End Sub

Private Sub ComboBox1_Change()
   Dim lst_column As Integer
lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    For j = 2 To lst_column
    
     Columns(Split(ActiveSheet.Cells(1, j).Address, "$")(1)).EntireColumn.Hidden = True
Next j
Columns(ComboBox1.ListIndex + 2).EntireColumn.Hidden = False
End Sub"

excel hide unhide columns vba