Check Boxes

EE Pros,

I had an EE Pro build out a small macro that when you double clicked, it put a check in a box.

I've now tried to adapt it to a "row" instead of a column and cannot get it to work right.  I think it may have something to do with the "offsets".   I would appreciate some help in getting this to work correctly.

Thank you in advance,

B.
Check-Boxes.xlsm
Bright01Asked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Provides check button
    If Target.Row = 15 Then
        Application.ScreenUpdating = False
        With ActiveCell
            If .Value = "P" Then
                .Value = ""
            Else
                .Font.Name = "Wingdings 2"
                .Value = "P"
            End If
        End With
        'These two lines below make it a realtime update
        Cancel = True
        Application.ScreenUpdating = True
    End If
End Sub

Open in new window

0
 
MacroShadowCommented:
Not sure exactly how you want it adapted.

In any case the first parameter of Offset is the RowOffset and the second one is the ColumnOffset.

I assume you want something like this:
    With ActiveCell
        If .Value = "P" Then
            .Value = ""
            .Offset(4, 0).ClearContents
        ElseIf .Offset(1, 0).Value <> "" Then
            .Font.Name = "Wingdings 2"
            .Value = "P"
            .Offset(4, 0).FormulaR1C1 = ""            '"=Value"    'Value is not a defined name
        End If
    End With

Open in new window

0
 
Bright01Author Commented:
MacroShadow,

I changed the code to match what you sent me but it still doesn't work.

Just to be clear, when you open the sample WB/WS, and you double click on cells in Row 15, it automatically should put a "checkmark" in the box (which is capital "P" and Wingdings2).

That's what I'm trying to get working......

b.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ess KayEntrapenuerCommented:
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
0
 
Bright01Author Commented:
Esskay,

I tried this code and unfortunately it doesn't work.  

Can you open my workbook and see what I'm trying to accomplish?  If you double click on the cells in row 15, it should automatically place a "P" that then converts to a check mark.

B.
0
 
Bright01Author Commented:
Perfect!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.