0

I'm trying to run a macro that corrects a linked cell designation with checkboxes within a set of workbooks.

There are many (around 100) workbooks that need adjusting in one file.

As such I am looping through these files and running the reassignment, however, it only ever applies to the file in which I wrote the macro:

Sub CheckBoxesControl()

On Error Resume Next
    Dim path As String
    Dim file As String
    Dim wkbk As Workbook
    Dim i As Long


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    path = "C:\file\path\"
    file = Dir(path)

    Do While Not file = ""
        Workbooks.Open (path & file)
        Set wkbk = ActiveWorkbook

    For i = 1 To 400
        ActiveWorkbook.Sheet4.CheckBoxes("Check Box " & i).LinkedCell = "ChkBoxOutput!AA" & i
        ActiveWorkbook.Sheet21.CheckBoxes("Check Box " & i).LinkedCell = "ChkBoxOutput!AB" & i
        Activekbook.Sheet22.CheckBoxes("Check Box " & i).LinkedCell = "ChkBoxOutput!AC" & i

    Next i

    wkbk.Save
    wkbk.Close
    file = Dir
    Loop

End Sub

Can anyone tell me how to adjust it so that it is applied to each file?

The macro runs without errors (and indeed each file in the file does seem to be opened and closed).

Community
  • 1
  • 1
Gideon
  • 1,878
  • 4
  • 40
  • 71
  • 1
    do **all** the workbooks have sheets named *Sheet4,Sheet21, and Sheet22*? – Our Man in Bananas Jun 12 '13 at 08:13
  • yes, each workbook is formed from a template, and all the sheet designations are identical. – Gideon Jun 12 '13 at 08:17
  • [see this answer it may be useful](http://stackoverflow.com/questions/16957334/trying-to-open-the-workbook-in-separate-instance/16959325#16959325) –  Jun 12 '13 at 08:36

1 Answers1

0

Don't use ActiveWorkbook, it can confuse matters (same as using ActiveCell / Selection) - do it like this:

Do While Not file = ""
    Set wkbk = Workbooks.Open (path & file)

For i = 1 To 400
    wkbk.Sheet4.CheckBoxes("Check Box " & i).LinkedCell = "ChkBoxOutput!AA" & i
    wkbk .Sheet21.CheckBoxes("Check Box " & i).LinkedCell = "ChkBoxOutput!AB" & i
    wkbk .Sheet22.CheckBoxes("Check Box " & i).LinkedCell = "ChkBoxOutput!AC" & i

Next i

wkbk.Save
wkbk.Close
file = Dir
Loop
MattCrum
  • 1,110
  • 1
  • 6
  • 5
  • Thanks for this advice. This has not resolved the issue however, it still seems to run on each sheet, but when I go to see if the LinkedCells have be reassigned, no effect. (The macro work incidentally if I just run it in each sheet, so it is well formed to that extent) – Gideon Jun 12 '13 at 16:48
  • Try amending the `"ChkBoxOutput!AA" & i` statements to include the workbook name too - perhaps it's linking everything to the "ChkBoxOutput!AA" in `ThisWorkbook`. The first statement would look something like: `wkbk.Sheet4.CheckBoxes("Check Box " & i).LinkedCell = "[" & wkbk.Name & "]ChkBoxOutput!AA" & i` – MattCrum Jun 13 '13 at 09:19