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 |
|
0 comments |
Last Update : 5 พฤศจิกายน 2553 10:46:32 น. |
Counter : 583 Pageviews. |
|
|
|