Solved

Checkbox - copy row to sheet

Posted on 2013-11-19
15
1,730 Views
Last Modified: 2013-11-25
Hi,

I have a form that record differant information into a sheet, and it put's an activeX checkbox in a row "A" when data from the form is stored. The checkbox is assign to the row where the record is stored from the form.

When the checkbox in a row is checked I want the data in the row to be copied to the next available row in the sheet called 'total'. The code must apply to all checkboxes in column A. without placing the code in 500 rows.

Does anybody have a code that could help me out,

And si it possible to add a date when it was "transferd"?
0
Comment
Question by:agepab
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39661887
Yes, it is possible to do the transfer and to add the date. However, the code would require to know precisely from where to copy data and where to deposit them. For this purpose please provide a workbook containing both sheets.
The other required clarification concerns the work flow.
1. The transfer could take place as soon as the checkbox is checked.
2. The transfer could take place periodically, always taking the items from your list which have been most recently marked.
3. The transfer could be designed to occur only once. Say, at the end of the month all checked items are transferred to a new sheet, and next month items from that sheet will be transferred but not from the first one.
In the last case the check marks should stay in place. In the other two cases they should be removed after copying, or an additional mark be added to the record indicating that items already copied aren't copied again a second time.
0
 
LVL 2

Expert Comment

by:samrad1
ID: 39665296
Hey! I created a quick file to show the checkboxes and code behind it, you can check them and it copy over to the other range with a timestamp.


'All of the following code goes in the code pane for the worksheet containing the checkboxes
Private Sub CheckBox1_Click()
    ProcessClick CheckBox1.TopLeftCell.Offset(0, 1), CheckBox1.Value
End Sub

Private Sub CheckBox2_Click()
    ProcessClick CheckBox2.TopLeftCell.Offset(0, 1), CheckBox2.Value
End Sub

Private Sub CheckBox3_Click()
    ProcessClick CheckBox3.TopLeftCell.Offset(0, 1), CheckBox3.Value
End Sub

Sub ProcessClick(sourceCell, fChecked)
    If (fChecked) Then
        'add the row
        Dim r As Range
        Set r = GetBlankRow
        r = sourceCell
        r.Offset(0, 1) = Time$
    Else
        'remove the row
        Set r = GetExistingItem(sourceCell.Value)
        Set r = Range(r, r.Offset(0, 1))
        r.Delete xlUp
    End If
    
End Sub

'finds the blank row to insert into
Function GetBlankRow()
    Dim rStart As Range
    Set rStart = Range("e2")
    
    Do Until rStart = ""
        Set rStart = rStart.Offset(1, 0)
    Loop
    
    Set GetBlankRow = rStart
End Function

Function GetExistingItem(sString)
    Dim rStart As Range
    Set rStart = Range("e2")
    
    Do Until rStart = "" Or rStart = sString
        Set rStart = rStart.Offset(1, 0)
    Loop
    
    Set GetExistingItem = rStart
End Function

Open in new window

checkbox.xlsm
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39667124
Please answer my question about the work flow. Which of the three scenarios I enumerated fits your requirement?
0
 

Author Comment

by:agepab
ID: 39668290
sorry, didn't get mail on the replies. I'll look at it now
0
 

Author Comment

by:agepab
ID: 39668376
Hi,
First of all, thank you for helping me out, and sorry for the late reply.

I want the user to go through the list in the sheet "input" manually and tick what he wants transfer/copied over to sheet "total". So no automated function on specific date or periodically.

In short:

1.

The code must apply to all checkboxes in sheet "input column A" that is there and will come there in the future.

2.

The row ticked by user should be copied to sheet "Total". including reg.date, the date he ticked the checkbox.

3.

The row whitch is checked in sheet "input" stays as is (these are connected to pivots), maybe just need conditional formatting with row color or just as it is with status change as in "input - column K".

4.

If user tick off the checkbox in sheet "input" it will delete the row in sheet "total". The chkbox can be ticked later with new reg.date.
Here's the code that generate the checkbox from the userform:
Dim lRow As Long
Dim ws As Worksheet
Dim obj As Object
    
Set ws = Worksheets("Input")

'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
With ws
    Set obj = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=.Cells(lRow, 1).Left + 10, Top:=.Cells(lRow, 1).Top + 3, Width:=49, Height:=16)
    obj.Object.Caption = "Input"
    obj.LinkedCell = Cells(lRow, 1).Address
    obj.Object.Value = False
End With

Open in new window


Attached are the two sheets.
Testsheet.xlsx
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39670142
Thank you for your detailed reply. Unfortunately, I will be travelling these next four days and may not be able to complete the encoding today. Do you have time to wait a week?
Your initial checkbox.xlsm suggests that the entry in the Total sheet might be removed when the user unchecks the checkbox. That sounds like more complicated than useful. :-)
I suggest that unchecking should either be impossible or limited in some way (for example, can undo only within an hour).
I also point out that column K is a repetition of column A's checkmark and may not be required. However, if it is required then it must be tied directly to the action of the checkbox, meaning, K's value must be set by code and not accessible to the user.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39670485
You have 5 checkboxes in cell A2, several in A3 and three more in A36557.
Unfortunately, the attached tool doesn't give you access to the TopLeftCell property, but it does give you a complete list of all controls on any sheet you choose and the Top property for each. So, if you have several boxes with similar Top and Left properties you can see that they are superimposed upon each other.
Run the Sub 'SetButtonProperties' from the 'ShapeMan' module in the attached file while your other workbook is open. Select the other workbook, the sheet, and look at the names in the third drop-down.
XLK-131102-Shapes-Control.xlsm
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 14

Expert Comment

by:Faustulus
ID: 39670554
In fact, you have only one checkbox in row 3, but the one in row 4 also has A3 as its TopLeftCell. The reason is that it isn't positioned correctly - exactly - in row 3. With the system you are building this is absolutely critical because if the checkbox in row 4 overlaps into row 3 by even a little and, therefore, thinks that it is "attached" to A3 the item that will be transferred to your Totals sheet will be from row 3, not row 4, when that box is checked.
You can use the tool I just posted to align the checkboxes exactly. The measure of alignment is points. Excel also gives you the row height in points. So, you can align the first one correctly - not at the very edge but a little below that - and then calculate the correct position of all the others by adding the row height.
Consider replacing the checkboxes with a validated cell where the user can only enter Yes or No. Then, after Yes has been selected, remove No from the validation list, thereby making it impossible to change back. In place of Yes/No you could also use Fakturert/Opptjent which would combine the functions of columns A and K, making one of them obsolete.
I'm on my way to the airport now, as soon as I hit the 'Submit' button. Please reply at your leisure.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39670908
The problem with using ActiveX checkboxes is trapping the event when they are checked or unchecked. With an ActiveX checkbox, you need a separate sub for each checkbox. It would be much easier if you used Forms control checkboxes, because they can all call the same sub when they are checked or unchecked.

Here is a sub that will put a Forms control checkbox in a cell that is doubleclicked. It will link the checkbox to the cell that was doubleclicked (using a white font color in that cell), and set the .OnAction property of the checkbox to sub CheckboxClicked. The sub must be installed in the code pane of the worksheet containing the checkboxes.

'Goes in code pane for worksheet containing checkboxes
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Adds a Forms toolbar checkbox to the cell being double-clicked
'Takes its caption from the value of the cell before it was double-clicked
'Checkbox value is linked to that same cell, using a white font color
With ActiveSheet.CheckBoxes.Add(Target.Left, Target.Top + 1, Target.Width, Target.Height - 1)
        .Caption = "Fakturer"
        .LinkedCell = Target.Address
        .Name = "cb_" & Target.Address(False, False)     'Name it like "cb_A11"
        .OnAction = "CheckboxClicked"
End With
Target.Font.ColorIndex = 2     'White font color for linked cell TRUE/FALSE value
Cancel = True
End Sub

Open in new window

You will probably want to delete the sub (or rename it to xWorksheet_BeforeDoubleclick) after you have finished designing the workbook. Renaming the sub prevents it from being triggered when the user doubleclicks a cell.

The sub CheckboxClicked goes in a regular module sheet. It is triggered by checking or unchecking a checkbox (that has CheckboxClicked as its OnAction property). If the checkbox is checked (linked cell is TRUE), then data will be copied over to the Total worksheet along with a timestamp. If the checkbox is unchecked (linked cell is FALSE), then the associated data on the Total worksheet will be deleted.
Sub CheckboxClicked()
Dim cel As Range, dest As Range, rw As Range, targ As Range
Set cel = ActiveSheet.Shapes(Application.Caller).TopLeftCell
Set rw = Intersect(cel.EntireRow, Range("B:K"))
With Worksheets("Total")
    Set dest = .Columns("A").Find(Application.Caller)
    If dest Is Nothing Then
        If cel.Value = True Then
            Set dest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            dest.Offset(0, 1).Resize(1, rw.Columns.Count).Value = rw.Value
            dest.Offset(0, rw.Columns.Count + 1).Value = Now
            dest.Value = Application.Caller
        End If
    Else
        If cel.Value = False Then dest.EntireRow.Delete
    End If
End With
End Sub

Open in new window

TestsheetQ28298465.xlsm
0
 

Author Comment

by:agepab
ID: 39671215
I'm on travel myself this weekend, I'll try to get some time to test it out.

Thanks a mill so far :)
0
 

Author Comment

by:agepab
ID: 39671254
Read through it now:)

Personally I like checkboxes, it is easy for a user to understand and so on. So if there is a possibility to use this, I would prefer it.

The information that is in the Input sheet is entered by a userform where the user fill in. (this userform was not attached to the two sheets, it is connected to databases etc). The activeX checkboxes will be added to the input sheet when the user saves his information in the userform.

If a userform checkbox will suite better for this, can we put in a userform checkbox instead?

I'll see if I can get a copy of the workbook with the userform out to you by the weekend.

Have a great weekend!
0
 
LVL 80

Expert Comment

by:byundt
ID: 39671430
The activeX checkboxes will be added to the input sheet when the user saves his information in the userform. If a form checkbox will suit better for this, can we put in a userform checkbox instead?
The code to create the Forms control checkbox, link it to the proper cell, assign a caption and assign an OnAction property is shown in the Worksheet_BeforeDoubleClick event sub in my previous Comment. You'll want to use those statements instead of your existing code that creates an ActiveX checkbox when your userform is closed.
0
 

Author Comment

by:agepab
ID: 39674419
Thanks byundt!

I get your VBA to work in the actual workbook. But the user have to double click everytime he wants a checkbox, is there a way to get it automated like it was with the activeX? I'm not so familiar with VBA to make the script.
Dim lRow As Long
Dim ws As Worksheet
Dim obj As Object
    
Set ws = Worksheets("Input")

'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
With ws
    Set obj = .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=.Cells(lRow, 1).Left + 10, Top:=.Cells(lRow, 1).Top + 3, Width:=49, Height:=16)
    obj.Object.Caption = "Input"
    obj.LinkedCell = Cells(lRow, 1).Address
    obj.Object.Value = False
End With

Open in new window

0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39675476
I moved your snippet into a sub NewCheckbox and put in code to use Forms checkboxes.
Sub NewCheckbox()
Dim lRow As Long
Dim target As Range
Dim obj As Object
'Adds a Forms toolbar checkbox to the cell
'Checkbox value is linked to that same cell, using a white font color
    
With Worksheets("Input")
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row       'find  first empty row in database
    Set target = .Cells(lRow, 1)
    With .CheckBoxes.Add(target.Left, target.Top + 1, target.Width, target.Height - 1)
            .Caption = "Fakturer"
            .LinkedCell = target.Address
            .Name = "cb_" & target.Address(False, False)     'Name it like "cb_A11"
            .OnAction = "CheckboxClicked"
    End With
    target.Font.ColorIndex = 2     'White font color for linked cell TRUE/FALSE value
    target.Value = False
End With
End Sub

Open in new window

TestsheetQ28298465.xlsm
0
 

Author Comment

by:agepab
ID: 39676189
this worked out just great, Super! I feel so stupid, was trying to get it together myself, got down some of the code, well some of the contents but way off in many places. Thank god for you guys!

Most of all BIG thanks byundt for sticking with me on this!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now