Check box problem

jrussellCW · Aug 12, 2011

Hi there, new to the forum, I have a quick easy question. I am trying to create a macro, using vb, that will add a check box to the cell I have selected, label the check box "Produced" and link the checkbox to the cell 2 cells to the right.
It should also then create a check box in the cell to the right of "produced", and label this new check box "Received" This new check box will be linked to the cell two cells to its right.
The result will be two check-boxes in adjacent cells, returning "true" and "false" to the next two cells over.
Then I will have Two cells that sum up the number of "true's" for each column, So that I can easily see how many commercials each month production has produced, and how many they have received. I thought it would be simple, but it's giving me trouble.
oh, and the check boxes must be limited in size to the size of the cell they are in, there are going to be lots of them down the column.

jrussellCW · Aug 12, 2011

Ideally, yes, it would be interactive. This would help ensure that we get an accurate monthly count.

Warship · Aug 13, 2011

Code:

Sub CreateCheckBoxes()
    Const cHL As Long = 10 'set to # of cols to highlight
    Dim c As Range, r As Long, x As Long
    Application.ScreenUpdating = False
    r = Application.InputBox("How many rows?", "", 1, , , , , 1) - 1
    For x = ActiveCell.Row To ActiveCell.Row + r
      Set c = ActiveCell
      ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
      With Selection
        .Characters.Text = "Produced"
        .LinkedCell = c.Offset(, 2).Address
        .Value = xlOn
        .Value = xlOff
      End With
      c.Resize(1, cHL).Select
      Selection.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=IF(AND(" & ActiveCell.Offset(, 2).Address & "=FALSE," & _
        ActiveCell.Offset(, 3).Address & "=FALSE),1,0)"
      Selection.FormatConditions(1).Interior.Color = 65535
      Set c = ActiveCell.Offset(, 1)
      ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
      With Selection
        .Characters.Text = "Received"
        .LinkedCell = c.Offset(, 2).Address
        .Value = xlOn
        .Value = xlOff
      End With
      ActiveCell.Offset(1).Select
    Next x
End Sub

kjacw · Aug 13, 2011

How can you help me with this situation for check boxes

I need help with a medical form that I want to create. I am using Excel 2007 and am somewhat new to VBA but not foreign to it.

I have patients names in column A, In column J I have a Antecedentes Patologicos column for past or present medical problems that I need to check in the case if a patient has these problems or not and sometimes each patient will have none or some may have more than one.

I would like to know how is it possible to create a combo box check box list associated with each patient so I can check each of 8 listed problems or check none if applicable. Then for each box checked it then fills appropriate cells on the sheet.

For example the 8 listed problems that can be checked for each are none, arthritis, asthma, cardiac, hypertension, stomach, HIV, kidney. When any one of these boxes are checked it returns the name of each problem to each cell next to one another.
If none is checked then cell K2 will have none,
if arthritis is checked cell L2 is filled with arthritis,
if asthma is checked cell M2 is filled with the word asthma and so on.

if none is checked then all other cells (K2:Q2) are all left blank or nothing

This follows for each row, for each patient.

Please help I hope I am making myself clear.

shg · Aug 13, 2011

Rather than do anything too fancy, ...

<TABLE style="WIDTH: 229pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=305 x:str><COLGROUP><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 37pt" width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=16 width=49>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=15> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>None</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=15> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Arthritis</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=15> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Asthma</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 11pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=15> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Cardiac</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Alan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:fmla='=IF(B2="", "", C$1)'>None</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:str="" x:fmla='=IF(D2="", "", E$1)'> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Barb</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Cain</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:fmla='=IF(F4="", "", G$1)'>Asthma</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Dana</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Eric</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:fmla='=IF(B6="", "", C$1)'>None</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Fran</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:fmla='=IF(D7="", "", E$1)'>Arthritis</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:fmla='=IF(H7="", "", I$1)'>Cardiac</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=16>Gary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30> </TD></TR></TBODY></TABLE>

The narrow columns are in Marlett font, and the check mark is the letter a.

The formula in C2 and copied down is

=IF(B2="", "", C$1)

Warship · Aug 13, 2011

To my knowledge there is no combobox with muliple checkboxes.
A listbox can be setup to contain multiple checkboxes however.
As a listbox doesn't dropdown, you would either have to use scroll controls to browse the list or set the row height to accomodate the list.

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Expanding on shg's simplicity you could use the right-click event to place his checkmark and clear the other cells upon “none” being marked.

.

kjacw · Aug 13, 2011

Warship said:
To my knowledge there is no combobox with muliple checkboxes.
A listbox can be setup to contain multiple checkboxes however.
As a listbox doesn't dropdown, you would either have to use scroll controls to browse the list or set the row height to accommodate the list.

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Expanding on shg's simplicity you could use the right-click event to place his checkmark and clear the other cells upon “none” being marked.

.

Thanks for the quick reply.
The simplicity of shg's would cause me too much time as many patients are seen and to have to check each box across my worksheet would be too much for me. My idea was that when I come to the Prior History section as indicated in my post #13 I could just open a list then check each that applies and then close the list and each prior medical history that I just checked would fill the appropriate cell for that patient.

Although the above I am not sure how to setup something like that. I have been trying to work on check boxes for each cell but that also would be time consuming because my form consists of about 1,000 or more spaces for patients

Warship · Aug 14, 2011

kjacw said:
I have been trying to work on check boxes for each cell but that also would be time consuming

This is where shg's suggestion would reduce setup time. It only involves inserting new columns and a bit of VBA placed in the Sheet Module to automate:

Code:

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column > 9 And Target.Column < 26 Then
    Select Case Target.Column
      Case 10
        If Target = "" Then
          Target = "a"
          Target.Font.Name = "Marlett"
          Target.Offset(, 2) = ""
          Target.Offset(, 4) = ""
          Target.Offset(, 6) = ""
          Target.Offset(, 8) = ""
          Target.Offset(, 10) = ""
          Target.Offset(, 12) = ""
          Target.Offset(, 14) = ""
        End If
      Case 12, 14, 16, 18, 20, 22, 24
        If Target = "" Then
          Target = "a"
          Target.Font.Name = "Marlett"
          Target.Offset(, -1 * (Target.Column - 10)) = ""
        Else
          Target = ""
          If Cells(Target.Row, 12) = "" And _
            Cells(Target.Row, 14) = "" And _
            Cells(Target.Row, 16) = "" And _
            Cells(Target.Row, 18) = "" And _
            Cells(Target.Row, 20) = "" And _
            Cells(Target.Row, 22) = "" And _
            Cells(Target.Row, 24) = "" Then
            Target.Offset(, -1 * (Target.Column - 10)) = "a"
            Target.Offset(, -1 * (Target.Column - 10)).Font.Name = "Marlett"
          End If
        End If
    End Select
    Cancel = True
  End If
End Sub

I do understand what you'd like to accomplish however, as stated, a dropdown with checkboxes doesn't exist as far as I know.

If you place a listbox (with ckbx's) in the cell and leave the row height equal to one line of the list, you will still have to be clicking scroll controls to find your items to check/uncheck which seems just as laborious as multiple ckbx's across the sheet. And you'll need alot of VBA to control cell updates.

Another option is to increase row height to accommodate the entire list leaving all ckbx's exposed all the time, of course total over-all height of sheet increases significantly. And you'll need alot of VBA to control cell updates.

Another option is to increase listbox height when the listbox gets focus but a lot of VBA would be required to control cell updates and to make it dynamic.

The only other option I see is to go with a UserForm which I’ve done here:

Create UserForm1
Create TextBox1 in Userform1

Place this code in Userform1:

Code:

Option Explicit
Dim ProbArr As Variant
Dim x As Integer, r As Long
Dim skipevent As Boolean

Private Sub UserForm_Initialize()
  r = ActiveCell.Row
  ProbArr = Array("none", "arthritis", "asthma", "cardiac", "hypertension", "stomach", "HIV", "kidney")
  Me.Height = 131.25
  Me.Width = 106.5
  Me.Caption = Cells(r, 1)
  ListBox1.Height = 105.05
  ListBox1.Width = 90
  ListBox1.Top = 0
  ListBox1.Left = 6
  ListBox1.MultiSelect = fmMultiSelectMulti
  ListBox1.ListStyle = fmListStyleOption
  ListBox1.Clear
  For x = 0 To UBound(ProbArr)
    ListBox1.AddItem ProbArr(x)
  Next x
  For x = 11 To 18
    skipevent = True
    If Cells(r, x) <> "" Then
      ListBox1.Selected(x - 11) = True
    End If
    skipevent = False
  Next x
  If NoneSelected Then
    skipevent = True
    ListBox1.Selected(0) = True
    skipevent = False
  End If
End Sub

Private Sub UserForm_Terminate()
    For x = 11 To 18
      If ListBox1.Selected(x - 11) Then
        Cells(r, x) = ProbArr(x - 11)
      Else
        Cells(r, x) = ""
      End If
    Next x
End Sub

Private Sub ListBox1_Change()
  If skipevent Then Exit Sub
  Select Case ListBox1.ListIndex
    Case 0
      If ListBox1.Selected(0) Then
        skipevent = True
        For x = 1 To 7
          ListBox1.Selected(x) = False
        Next x
        skipevent = False
      Else
        skipevent = True
        ListBox1.Selected(0) = True
        skipevent = False
      End If
    Case Else
      If ListBox1.Selected(ListBox1.ListIndex) Then
        skipevent = True
        ListBox1.Selected(0) = False
        skipevent = False
      Else
        If NoneSelected Then
          skipevent = True
          ListBox1.Selected(0) = True
          skipevent = False
        End If
      End If
  End Select
End Sub

Private Function NoneSelected()
    NoneSelected = True
    For x = 1 To 7
      If ListBox1.Selected(x) = True Then
        NoneSelected = False
        Exit For
      End If
    Next x
End Function

Place this code in Sheet Module of your Patient Data Sheet:

Code:

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 10 Then
    If Cells(Target.Row, 1) <> "" Then
      UserForm1.Show
    Else
      MsgBox "No Patient Data", vbCritical, "ERROR"
    End If
    Cancel = True
  End If
End Sub

Right-clicking in Column J will bring up the form.

kjacw · Aug 14, 2011

Ok thanks I'll give it a try.

Thanks much

kjacw · Aug 14, 2011

I tried the UserForm1 and I think you wanted me to create a ListBox1 and not TextBox1. I am correct in creating a ListBox1. I don't have to do anything with the ListBox only create it. Which I did and put the codes in the places stated above but when I right click in column J I get an error

Run-time error that says Could not get the Selected property. Invalid argument

Then when I select Debug a yellow highlight appears over the ListBox1_Change code.

code:

If ListBox1.Selected(ListBox1.ListIndex) Then

I created this on new workbook with no Patient Data and a message appeared saying No Patient Data.

Let me mention that most of my spreadsheet has Data Validation drop down list. This is not causing a failure? I must be doing something wrong.

kjacw · Aug 14, 2011

Warship if you have created this in a workbook could you PM it to me to see what I am doing wrong

Check box problem

jrussellCW

New Member

jrussellCW

New Member

Excel Facts

Warship

Well-known Member

kjacw

Board Regular

shg

MrExcel MVP

Warship

Well-known Member

kjacw

Board Regular

Warship

Well-known Member

kjacw

Board Regular

kjacw

Board Regular

kjacw

Board Regular

Similar threads

Forum statistics

Share this page

Check box problem

New Member

New Member

Excel Facts

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock