Excel VBA Class Module Examples

EXAMPLES OF VBA CLASS


  In three separate workbooks, we have created useful userforms using class modules :

Fast Percentage Calculation Userform


 Product Discount Calculation According To Quantity,Price And Percent Rate


         When the product quantity, price and discount percentage are entered to text boxes on the userform,   the discounted price of the product or products are automatically calculated without pressing any button.
If wanted, calculation and values on text boxes can be saved from the userform to sheet.


 Hide / Unhide The Columns With Horizontal Userform (column management with userform)


          The sheets of the workbook are listed in the combobox on the userform. The columns of the sheet are listed together with their titles as check boxes lined up side by side. 
Columns are hidden and displayed through these check boxes.
Codes of this userform :
Private Sub UserForm_initialize()
Dim lst_column, j As Long
Dim syf As Integer
With Me
    .Left = 0
    .Top = 0
End With

    'MsgBox Me.InsideWidth
For syf = 1 To ThisWorkbook.Worksheets.Count    'The sheets of workbook is added to combobox.
ComboBox1.AddItem Sheets(syf).Name
Next syf
'Dim chkBox As MSForms.CheckBox
ComboBox1.Value = ActiveSheet.Name

'lst_column = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
For i = 1 To 1                                   'Creating check boxes
For j = 1 To lst_column

    Set chkBox = Frm_Controls.Controls.Add("Forms.CheckBox.1", "CheckBox" & j)
    With chkBox
        .Top = i * 19
        .Left = (j * 70) - 65
     '   .BackColor = vbGreen
        .Font.Size = 11
     
        .Caption = Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value
    End With
    chkbx_width = (lst_column * 70) + 15
    'MsgBox chkbx_width
    If chkbx_width > Me.InsideWidth Then
    With Me
    .ScrollBars = fmScrollBarsHorizontal           'This will create a horizantal scrollbar
    .ScrollWidth = chkbx_width + 50
     End With
     Else
     Me.ScrollBars = fmScrollBarsNone
     End If
Next j
Next i

Call op
End Sub

As seen in VBA codes, a checkbox is created for each one column.

1 comment:

  1. Nice Tutorial, thanks!!!
    But i can't download the sample :(
    Can you please post the whole Code below there?
    greetz Z

    ReplyDelete