Looping through checkboxes and copying files based on them being 'checked'

[Jfp87]

Hi guys,

Any help on the following is appreciated:

I am trying to loop through a number of checkboxes and copy a corresponding file from one folder to another if the checkbox is checked. I have found plenty of code for looping through checkboxes, which I don't think is the problem. I am stumped on how to copy multiple files based on checkbox values. I have tried making the checkbox caption property equal the filename I want to copy, and I have tried putting the filenames & checkbox names into arrays....just cant figure it out at the moment.

Just incase it isn't clear, each of the checkboxes should copy a particular file from one folder to another based on whether it is checked or not. Thanks in advance, I think I will benefit from a fresh perspective.

Joe

[gmayor]

The following assumes legacy form field check boxes. I have used message boxes for checking the code works for you. You will need to change the check box names and the file names as appropriate. I have not included any checking to establish whether the source or target files exist.

Sub CopyDocs()
Dim off As FormField
    For Each off In ActiveDocument.FormFields
        Select Case off.name
            Case "Check1"
                If off.CheckBox.Value = True Then
                    MsgBox "1"
                    'FileCopy "C:\SourcePath\Filename1.ext", "C:\DestPath\Filename1.ext"
                End If
            Case "Check2"
                If off.CheckBox.Value = True Then
                    MsgBox "2"
                    'FileCopy "C:\SourcePath\Filename2.ext", "C:\DestPath\Filename2.ext"
                End If
            Case "Check3"
                If off.CheckBox.Value = True Then
                    MsgBox "3"
                    'FileCopy "C:\SourcePath\Filename3.ext", "C:\DestPath\Filename3.ext"
                End If
            Case "Check4"
                If off.CheckBox.Value = True Then
                    MsgBox "4"
                    'FileCopy "C:\SourcePath\Filename4.ext", "C:\DestPath\Filename4.ext"
                End If
                'etc
        End Select
    Next off
End Sub

[Jfp87]

Thanks gmayor for taking the time to respond.

I had code very similar to that which worked, but I have 30+ checkboxes and was trying to find a way to reduce the code. Do you know of any shorter way, without having to write code for each individual checkbox? I am unsure, maybe there isn't another way to do this.

Thanks again.

[gmayor]

I suppose it would be possible to use variables depending on definable values in the file, path and checkbox names, but there are no prizes for changing a code sequence that works to one of greater complexity.

Sub CopyDocs()
Dim i As Long
    For i = 1 To ActiveDocument.FormFields.Count
        If ActiveDocument.FormFields(i).Type = wdFieldFormCheckBox Then
            If ActiveDocument.FormFields("Check" & i).CheckBox.Value = True Then
                MsgBox i
                'FileCopy "C:\SourcePath\Filename" & i & ".ext", "C:\DestPath\Filename" & i & ".ext"
            End If
        End If
    Next i
End Sub

[Jfp87]

Thanks, that's helped a lot.

Cheers,
Joe

[snb]

If you name the checkboxes 'chk_1', 'chk_2', 'chk_3', etc. you can use:

Sub M_snb()
  For Each it In ActiveDocument.FormFields
    If it.Type = 71 And it.Result Then FileCopy "C:\SourcePath\Filename" & Mid(it.Name, InStr(it.Name, "_") + 1) & ".ext", "C:\DestPath\Filename" & Mid(it.Name, InStr(it.Name, "_") + 1) & ".ext"
  Next
End Sub

[Jfp87]

Thanks snb. Is there any way you can explain the code to help me understand it? It looks more advanced than I am used to.

Joe

[snb]

- loop through all items of the collection of formfields in the document
- if the item is a checkbox ( 71 is equivalent to wdFieldFormCheckBox )
- and if the result of the checkbox value = true (it can only contain 2 values true/false) then copy the file based on the number in the checkbox's name

[gmayor]

It looks more advanced than I am used to.
Joe

It is just a different way of expressing the shorter version I posted earlier, but harder for the newcomer to follow.

[Jfp87]

Thanks for your help guys. One last question...

Mid(it.Name, InStr(it.Name, "_") + 1)

I understand both functions seperately, but I am getting a bit confused trying to understand how the above statement works....

[snb]

if the name of the checkbox is 'chk_5' you have to isolate the '5'.

[gmaxey]

Cryptic code is fine for some. Especially when it works. In this case, if you have more than 9 checkboxes it won't work. Change snb's code as follows:

Sub M_snb()
  For Each it In ActiveDocument.FormFields
   If it.Type = 71 And it.Result Then FileCopy "C:\SourcePath\Filename" & Right(it.Name, Len(it.Name) - InStrRev(it.Name, "_")) & ".ext", "C:\DestPath\Filename" & Right(it.Name, Len(it.Name) - InStrRev(it.Name, "_")) & ".ext"
  Next
End Sub

I suspect that you will never be graded of fined for you style of coding so you might as well write it so it makes sense to you. In my case,

I prefer to declare my variables and use variable names that relate (at least to me) to what they are. For example, a formfield is an object so I use "oFF."
In most cases, unless you are playing "I can write that code in XX characters or less, I don't see any point in using 71 over the constant wdFormFieldCheckBox. Yes, they both mean the same thing. But one is crystal clear to even a novice where the other is clear as mud. There are cases where you must use the numerical value. For example if you are writing code that will be run with Word 2010 and 2007 that involves content control checkboxes (constant wdContentControlCheckBox) then you "must" use the numerical value "8" in your code because the constant is not defined in the Word 2007 object model and would result in a error when the code is compiled.

Unconventional I suppose, but I indent my code with 2 spaces while it seems the vast majority of people use 4. To me, 4 just seems to result in too much white space and makes the code harder for me to read.

Unless the line is very short e.g., If oFF IsNothing Then Exit Sub, I typically use If, Then, End If format over the shorter If, Then

I try to handle potential errors.

I try to (but not always) to comment my code so I will make sense to me years later when I look at it again.

Just as a matter of style, I typically end my procedures with:

lbl_Exit:
Exit Sub

I won't try to defend it. It is just my style and sort of a QA check for myself that I've looked over the procedure.

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
'Declare variables
Dim oFF As FormField
Dim strIndex As Long
  For Each oFF In ActiveDocument.FormFields
    'Is the field a checkbox?  Is it checked?
    If oFF.Type = wdFieldFormCheckBox And oFF.CheckBox.Value Then
      'Get the numerical index of the checkbox.  This is the number(s) after the "_"
      strIndex = Right(oFF.Name, Len(oFF.Name) - InStrRev(oFF.Name, "_"))
      'Handle errors
      On Error GoTo Err_Handler
      FileCopy "C:\SourcePath\Filename" & strIndex & ".ext", "C:\DestPath\Filename" & strIndex & ".ext"
    End If
ReEntry:
  Next oFF
lbl_Exit:
  Exit Sub
Err_Handler:
  MsgBox "Error (" & Err.Number & " - " & Err.Description & ") occurred while attempting" & vbCr _
        & "to copy C:\SourcePath\Filename" & strIndex & ".ext"
  Resume ReEntry
End Sub

[gmaxey]

What if it is 15 or 55 or 555?

Right(it.Name, Len(it.Name) - InStrRev(it.Name, "_"))

[snb]

It might be worth while studying VBA's very basic 'mid' function

Sub M_snb()
    For Each it In Array("chk_1", "chk_15", "chk_555")
        MsgBox Mid(it, InStr(it, "_") + 1)
    Next
End Sub

[gmaxey]

It might. But that doesn't change the fact that the cryptic code you posted will fall over with an index > 9.