ckBox automation

macropod · #7 09-11-2012, 04:22 PM

Hi coconutt,

I added the macro I posted to your previous attachment and checked its performance on every checkox formfield. None of the checkboxes in column 1 or your added column 6 threw an error and the corresponding checkboxes in columns 4 and 9 updated correctly.

The only anomolies concerned items 1016, 1180 and 15610 at the bottom of the first column. There, I believe, none of the checkboxes should trigger the on-exit function - and all of the checkboxes should be enabled (allow manual update). However, some of those checkboxes do trigger the macro and some are not enabled. Triggering of the macro from a column other than 1 on those rows generates the error.

For those three rows, you could trigger the following macro from column1 (to ensure columns 3 & 4 are cleared if column 1 is cleared):

Code:

Sub ClearChks()
Application.ScreenUpdating = False
Dim TblRow As Long, TblCol As Long
With Selection
  TblRow = .Cells(1).RowIndex
  TblCol = .Cells(1).ColumnIndex
  If .Tables(1).Cell(TblRow, TblCol).Range.FormFields(1).CheckBox.Value = False Then
    .Tables(1).Cell(TblRow, TblCol + 3).Range.FormFields(1).CheckBox.Value = False
    .Tables(1).Cell(TblRow, TblCol + 4).Range.FormFields(1).CheckBox.Value = False
  End If
End With
Application.ScreenUpdating = True
End Sub

And, to ensure only one of the checkboxes in columns 3 & 4 on those rows can be checked (and even then, only if column 1 is checked):

Code:

Sub ExclusiveCheck()
Application.ScreenUpdating = False
Dim TblRow As Long, TblCol As Long, iOffset As Long
With Selection
  TblRow = .Cells(1).RowIndex
  TblCol = .Cells(1).ColumnIndex
  If .Tables(1).Cell(TblRow, 1).Range.FormFields(1).CheckBox.Value = False Then
    .Tables(1).Cell(TblRow, TblCol).Range.FormFields(1).CheckBox.Value = False
    Exit Sub
  End If
  If TblCol = 4 Then iOffset = 1 Else iOffset = -1
  .Tables(1).Cell(TblRow, TblCol + iOffset).Range.FormFields(1).CheckBox.Value = _
    Not .Tables(1).Cell(TblRow, TblCol).Range.FormFields(1).CheckBox.Value
End With
Application.ScreenUpdating = True
End Sub

See attached.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Document automation	coconutt	Word	1	05-02-2012 01:45 PM
Automation Word-Excel	stephen_pen	Mail Merge	0	09-22-2011 05:17 PM
Urgent help regarding automation.	aligahk06	Excel	1	01-14-2010 01:55 PM
What is Application Automation and How can I use it in VBA	KramerJ	Excel	0	03-30-2009 12:59 PM
COM Automation Errors	ivanm	Word	0	03-23-2009 07:02 PM