BlogGang.com : : anchan42 : VBA routine to link all check boxes

VBA routine to link all check boxes

VBA routine to link all check boxes in a work book with the cells behind them. The code runs in access but modify an excel file.

Private Sub cmdImport_Click()

'Define variable for excel
Dim wkb As Workbook
Dim wks As Worksheet
Dim appExcel As Excel.Application
Dim cb As Object, c As Range
Dim chkShape As Shape
Dim TopRow As Integer, BtmRow As Integer, MiddleRow As Integer

'Opening Excel
Set appExcel = New Excel.Application
appExcel.Visible = True
If Dir(Me.txtFile) = "" Then
MsgBox "Incorrect File Name", vbCritical
Exit Sub
End If
Set wkb = appExcel.Workbooks(Me.txtFile)

For Each wks In wkb.Sheets
wks.Activate
For Each chkShape In wks.Shapes
Set cb = chkShape.OLEFormat.Object

If InStr(cb.Name, "Check") <> 0 Then
'calculating correct row

MiddleRow = (cb.TopLeftCell.Row + cb.BottomRightCell.Row) / 2
If MiddleRow < 70 Then
Debug.Print MiddleRow
'cb.Select
cb.LinkedCell = "$G$" & MiddleRow
wks.Range("$G$" & MiddleRow).Select
End If
End If
Next

Next

End Sub

Create Date : 05 พฤศจิกายน 2553

Last Update : 5 พฤศจิกายน 2553 10:46:32 น.

0 comments

Counter : 584 Pageviews.