如何从VBA Access中打勾Excel复选框 [英] How can I tick an Excel checkbox from VBA Access

问题描述

我已经通过VBA Access打开了一个Excel文件，并且可以在Excel单元格中进行读写.如何在Excel Ckeck Box上打勾?

I have opened an Excel file via VBA Access and can read and write in the Excel Cells. How can I tick an Excel Ckeck Box?

我的代码:

Dim Excel_App  As Object
Dim strExcel As String
Set Excel_App = CreateObject("Excel.Application")
Excel_App.Visible = True
Excel_App.Workbooks.Open fready
With Excel_App
    .CheckBox3.Value = True 'This line is used in VBA Excel and I need in  Access
End With

推荐答案

复选框属于特定Worksheet上的特定集合.这是哪个集合，取决于您要寻找的控件类型.

Checkboxes belong to a specific collection on a specific Worksheet. Which collection this is, depends on the type of control you're looking for.

您的代码被编写为好像复选框属于Excel.Application对象一样-无效.

Your code is written as if the checkbox belonged to the Excel.Application object - that can't work.

首先，您需要保留对要打开的Workbook对象的引用，因此，请不要使用它:

First you need to keep a reference to that Workbook object you're opening, so instead of this:

Excel_App.Workbooks.Open fready

您需要这个:

Dim book As Object ' early-bound: As Excel.Workbook
Set book = Excel_App.Workbooks.Open(fready)

如果您不知道要在哪个工作表上找到该复选框，则必须迭代工作簿的Worksheets集合:

If you don't know which worksheet to find the checkbox on, you'll have to iterate the workbook's Worksheets collection:

Dim sheet As Object ' early-bound: As Excel.Worksheet
For Each sheet In book.Worksheets
    'todo
Next

表单控件

因此，我们正在寻找CheckBox 表单控件.我们将在工作表的Shapes集合中找到它，并且知道当>为msoFormControl时正在查看 form控件；我们会知道，当它的FormControlType属性返回xlCheckBox时，它是一个复选框控件:

Form Controls

So we're looking for a CheckBox form control. We'll find that in the sheet's Shapes collection, and we'll know we're looking at a form control when its Type is msoFormControl; we'll know that it's a checkbox control when its FormControlType property returns xlCheckBox:

Dim sheet As Object ' early-bound: As Excel.Worksheet
For Each sheet In book.Worksheets
    Dim shp As Object ' early-bound: As Excel.Shape
    For Each shp In sheet.Shapes
        If shp.Type = 8 ' early-bound: msoFormControl
            If shp.FormControlType = 1 ' early-bound: xlCheckBox
                'todo
            End If
        End If
    Next
Next

所以现在我们知道shp是一个复选框表单控件.可以通过ControlFormat对象/属性访问Value，因此您可以像这样设置名为Check Box 1的复选框的值(这是默认名称):

So now we know shp is a checkbox form control. The Value is accessible through the ControlFormat object/property, so you can set the value of a checkbox named Check Box 1 (that's the default name) like this:

If shp.Name = "Check Box 1" Then
    shp.ControlFormat.Value = 1 'checked
End If

当然，如果您已经知道要查找的特定工作表，则无需全部遍历.

Of course if you already know which specific sheet you're looking for, there's no need to iterate them all.

如果该控件是ActiveX控件，则完全不同.您可以在OLEObjects集合中找到它，该集合包含OLEObject实例，这些实例具有返回MSForms.CheckBox对象的Object属性；您可以从OLEObject.ShapeRange.Name获取该复选框的名称:

If the control is an ActiveX control, it's a whole different story; you'll find it in the OLEObjects collection instead, which contains OLEObject instances, which have an Object property that returns the MSForms.CheckBox object; you can get the name of the checkbox from the OLEObject.ShapeRange.Name:

Dim ctrl As Object ' early-bound: As Excel.OLEObject
For Each ctrl In sheet.OLEObjects
    If TypeName(ctrl.Object) = "CheckBox" Then ' early-bound: If TypeOf ctrl.Object Is MSForms.CheckBox Then
        If ctrl.ShapeRange.Name = "CheckBox1" Then
            ctrl.Object.Value = True ' checked
        End If
    End If
Next

请注意，早绑定TypeOf ctrl.Object Is MSForms.CheckBox检查比迟绑定TypeName检查要健壮得多.您需要通过工具>引用来引用MSForms类型库以使用它(如果您的VBA项目具有任何UserForm组件，则已经被引用了，在这种情况下，早期绑定的代码很容易理解)

Note that the early-bound TypeOf ctrl.Object Is MSForms.CheckBox check is MUCH more robust than the late-bound TypeName check. You need to reference the MSForms type library through Tools > References to use it (it's already referenced if your VBA project has any UserForm component, in which case early-bound code is a no-brainer).

这篇关于如何从VBA Access中打勾Excel复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！