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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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