Excel check box question?

Question

I have a spreadsheet where column 'a' has a checkbox (approx 75 cells will). When clicked I would like column 'b' to auto populate with the current date. The spreadsheet will need to be accessed by a team. How can I do this? The script I think may work is:
Sub CheckBoxFunction()
'
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String

LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)

'Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row A
LRange = "B" & CStr(LRow)

'Change date in column B, if checkbox is checked
If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = Date

'Clear date in column B, if checkbox is unchecked
Else
ActiveSheet.Range(LRange).Value = Null
End If


'

End Sub

Will this work and how can I adjust it so it will?

Answer 1

Your code, as written, will not process appropriately. Also, check boxes do not reside in cells. The essentially 'hover' above the worksheet. They can be sized and positioned to appear as if they reside in the cells.



Here is a method to automatically add 75 checkboxes to a worksheet, and make them appear to be in rows 1-75. This process assumes that the row height is the standard Excel default of 12.75. You will have to rename the check box text and size column A to the appropriate width to make them appear to be in the cell.



Copy the following event handler to the clipboard:



Private Sub Worksheet_Calculate()

Dim i, LastRow

LastRow = Range("IU" & Rows.Count).End(xlUp).Row

Application.EnableEvents = False

For i = 1 To LastRow

If Cells(i, "IU").Value = "T" Then

Cells(i, "B").Value = Date

ElseIf Cells(i, "IU").Value = "F" Then

Cells(i, "B").Value = ""

End If

Next

Application.EnableEvents = True

End Sub



Select the worksheet to contain the check boxes and right click the sheet tab.



Select 'View Code'



Paste the event handler code into the sheet module editing area to the right.



In the menus at the top of the VBE, select INSERT > MODULE



Copy the following two macros to the clipboard and paste them into the newly created module:



Sub Assign_Linked_Cells()

On Error Resume Next

Application.ScreenUpdating = False

Dim i

For i = 1 To 75

ActiveSheet.Shapes("check box " & i).Select

With Selection

.LinkedCell = "IV" & i

End With

Cells(i, "IU").Formula = "=IF(IV" & i & "=TRUE,""T"",""F"")"

Next

Columns("IU:IV").EntireColumn.Hidden = True

End Sub



Sub InsertCkBoxes()

Dim setTop, i

setTop = 0

For i = 1 To 75

Set cb = ActiveSheet.Shapes. _

AddFormControl(xlCheckBox, 0, setTop, 75, 1)

cb.ControlFormat.LinkedCell = "IV" & i

setTop = setTop + 12.74

Next

Assign_Linked_Cells

End Sub



Close the VBE and return to the worksheet to contain the 75 check boxes.



Press ALT + F8



When the Macros window opens, select the InsertCkBoxes macro and click 'RUN'.



Close the Macros window.



Widen column A to contain the checkboxes.



Click a check box and the current date will be entered into column B in the same row as the check box 'appears' to be in.



Edit: If your row height is not 12.75, you can modify the code to suit your row height. For example, if your row height is 15.00, change this line:



setTop = setTop + 12.74



to



setTop = setTop + 14.99

Answer 2

Adding multiple checkboxes takes up a lot of resources and fails with low powered pc some of your users may have. Also you have to recreate the whole checkbox which is extremely tedious.



Your may add this code to your worksheet however through VBE



http://bit.ly/ouAWLv



All the user need to do is to double click on column F and G will populate the time it is entered.



The whole scheme may fail however.

a) Your user PC clocks may be wrongly set

b) What happen if they unclick, will the time be erased

c) It relies on winding font, but some users may not have that standard font installed

d) Some will probably run on their iphones or in any environment where macros are automatically disabled



In conclusion I willl probably rely on some online tools if I am running a poll of some kind

Answer 3

put in the text textile container on the type. Then precise click on it, p.c.. format administration and in cellular hyperlink put in the relationship with a clean cellular, as an occasion D3. In a cellular beside it, positioned your value, so as that ought to be C3. then in cellular E3 you have right here formula: =IF(D3,C3,0) Then if the checkbox is clicked, you will see the be conscious real performing in D3 and the fee in E3. while it is not clicked fake will seem in D3 and 0 in E3. you ought to cover column D, so which you do no longer see the fee in D3. To do it for different products you may ought to repeat the technique utilising distinctive cells, say C4, D4 and E4 for the subsequent one. it may additionally be an thought to permit a cellular for the quantity of things, you may multiply the fee by utilising in case you have been finding out to purchase better than between a similar merchandise. So if the quantity replaced into in B3, your IF formula could substitute to being: =IF(D3, B3*C3,0)