Hi,
I am using two macros in workbook for student self assessment.
The first macro is used to create multiple check boxes; and the second to add auto dates when the check box is clicked.
………………………………………………..
1 - Insert multiple checkboxes
………………………………………………..
Option Explicit
Sub insertCheckboxes()
Dim myBox As CheckBox
Dim myCell As Range
Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String
cellRange = InputBox(Prompt:="Cell Range",
Title:="Cell Range")
linkedColumn = InputBox(Prompt:="Linked Column",
Title:="Linked Column")
cboxLabel = InputBox(Prompt:="Checkbox Label",
Title:="Checkbox Label")
With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top,
Width:=.Width, Left:=.Left, Height:=.Height)
With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = "checkbox_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
……………………………………………………………………
2 - Insert date alongside a checkbox:
……………………………………………………………………
Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LCol As Long
Dim LRow As Long
Dim Rng As Range
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
'Find row that checkbox resides in
LCol = cBox.TopLeftCell.Column
LRow = cBox.TopLeftCell.Row
Set Rng = ActiveSheet.Cells(LRow + 0, LCol + 2)
'Change date in cell to the right of CheckBox, if checkbox is checked
If cBox.Value > 0 Then
Rng.Value = Date
'Clear date in column B, if checkbox is unchecked
Else
Rng.ClearContents
End If
End Sub
----------------------------------------
What I get when the checkbox is clicked is as follows:
C D E
Checkbox/True/Date
Checkbox/True/Date
Checkbox/True/Date
Checkbox/True/Date
My question is, how to get a #1 instead of "True" in column D?
Would much appreciate a solution, thanks in advance.
I am using two macros in workbook for student self assessment.
The first macro is used to create multiple check boxes; and the second to add auto dates when the check box is clicked.
………………………………………………..
1 - Insert multiple checkboxes
………………………………………………..
Option Explicit
Sub insertCheckboxes()
Dim myBox As CheckBox
Dim myCell As Range
Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String
cellRange = InputBox(Prompt:="Cell Range",
Title:="Cell Range")
linkedColumn = InputBox(Prompt:="Linked Column",
Title:="Linked Column")
cboxLabel = InputBox(Prompt:="Checkbox Label",
Title:="Checkbox Label")
With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top,
Width:=.Width, Left:=.Left, Height:=.Height)
With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = "checkbox_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
……………………………………………………………………
2 - Insert date alongside a checkbox:
……………………………………………………………………
Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LCol As Long
Dim LRow As Long
Dim Rng As Range
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
'Find row that checkbox resides in
LCol = cBox.TopLeftCell.Column
LRow = cBox.TopLeftCell.Row
Set Rng = ActiveSheet.Cells(LRow + 0, LCol + 2)
'Change date in cell to the right of CheckBox, if checkbox is checked
If cBox.Value > 0 Then
Rng.Value = Date
'Clear date in column B, if checkbox is unchecked
Else
Rng.ClearContents
End If
End Sub
----------------------------------------
What I get when the checkbox is clicked is as follows:
C D E
Checkbox/True/Date
Checkbox/True/Date
Checkbox/True/Date
Checkbox/True/Date
My question is, how to get a #1 instead of "True" in column D?
Would much appreciate a solution, thanks in advance.