ActiveX Controls In Excel with VBA Code
Dear All,
From this video you can learn use of
Check Box
Option Button
Label
Command Button
Group Box
Download Excel From Here
Below Code has been used.
Private Sub CommandButton1_Click()
If TextBox1.Value = “” Or Len(TextBox1.Value) < 5 Then
TextBox1.Value = “”
Exit Sub
End If
If Range(“j5000”).End(xlUp).Value = “Roll” Then
Range(“j5000”).End(xlUp).Offset(1, 0).Value = 1
Else
Range(“j5000”).End(xlUp).Offset(1, 0).Value = Range(“j5000”).End(xlUp).Value + 1
End If
TX = TextBox1.Value
Range(“j5000”).End(xlUp).Offset(0, 1).Value = UCase(TX)
If OptionButton1.Value = True Then
op1 = OptionButton1.Caption
ElseIf OptionButton2.Value = True Then
op1 = OptionButton2.Caption
ElseIf OptionButton3.Value = True Then
op1 = OptionButton3.Caption
End If
Range(“j5000”).End(xlUp).Offset(0, 2).Value = op1
If OptionButton4.Value = True Then
op2 = OptionButton4.Caption
ElseIf OptionButton5.Value = True Then
op2 = OptionButton5.Caption
ElseIf OptionButton6.Value = True Then
op2 = OptionButton6.Caption
End If
Range(“j5000”).End(xlUp).Offset(0, 3).Value = op2
If CheckBox1.Value = True And CheckBox2.Value = True Then
ch1 = “Term1,Term2”
ElseIf CheckBox1.Value = True And CheckBox2.Value = False Then
ch1 = “Term1”
ElseIf CheckBox1.Value = False And CheckBox2.Value = True Then
ch1 = “Term2”
ElseIf CheckBox1.Value = False And CheckBox2.Value = False Then
ch1 = “”
End If
Range(“j5000”).End(xlUp).Offset(0, 4).Value = ch1
TextBox1.Value = “”
OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False
CheckBox1.Value = False
CheckBox2.Value = False
End Sub
Private Sub CommandButton2_Click()
CheckBox1.Value = False
CheckBox2.Value = False
TX = TextBox2.Value
For Each cell In Range(“J2”, Range(“J2”).End(xlDown))
If cell.Value = TX + 0 Then
TextBox1.Value = Range(cell.Address).Offset(0, 1).Value
SB = Range(cell.Address).Offset(0, 2).Value
If SB = “phy” Then
OptionButton1.Value = True
ElseIf SB = “chem” Then
OptionButton2.Value = True
ElseIf SB = “bio” Then
OptionButton3.Value = True
End If
cls = Range(cell.Address).Offset(0, 3).Value
If cls = “first” Then
OptionButton4.Value = True
ElseIf cls = “2nd” Then
OptionButton5.Value = True
ElseIf cls = “3rd” Then
OptionButton6.Value = True
End If
trm = Range(cell.Address).Offset(0, 4).Value
If trm = “Term1” Then
CheckBox1.Value = True
ElseIf trm = “Term2” Then
CheckBox2.Value = True
Else
CheckBox1.Value = True
CheckBox2.Value = True
End If
End If
‘MsgBox Range(cell.Address)
Next cell
End Sub
Private Sub OptionButton1_Change()
If OptionButton1.Value = False Then
OptionButton1.BackColor = vbWhite
End If
End Sub
Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
OptionButton1.BackColor = vbGreen
End If
End Sub
Private Sub OptionButton2_Change()
If OptionButton2.Value = False Then
OptionButton2.BackColor = vbWhite
End If
End Sub
Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
OptionButton2.BackColor = vbGreen
End If
End Sub
Private Sub OptionButton3_Change()
If OptionButton3.Value = False Then
OptionButton3.BackColor = vbWhite
End If
End Sub
Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
OptionButton3.BackColor = vbGreen
End If
End Sub
Private Sub TextBox1_Change()
If Len(TextBox1.Value) = 0 Then
CommandButton1.Enabled = False
CommandButton1.BackColor = vbRed
ElseIf Len(TextBox1.Value) > 0 Then
CommandButton1.BackColor = vbGreen
CommandButton1.Enabled = True
End If
End Sub
