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

问题描述

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

我的代码:

将 Excel_App 作为对象变暗Dim strExcel As StringSet Excel_App = CreateObject("Excel.Application")Excel_App.Visible = TrueExcel_App.Workbooks.Open fready使用 Excel_App.CheckBox3.Value = True '此行在 VBA Excel 中使用，我需要在 Access 中使用结束于

解决方案

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

编写您的代码时，就好像复选框属于 Excel.Application 对象一样 - 这是行不通的.

首先，您需要保留对正在打开的 Workbook 对象的引用，而不是这样:

<块引用>

Excel_App.Workbooks.Open fready

你需要这个:

Dim book As Object ' 早期绑定:As Excel.WorkbookSet book = Excel_App.Workbooks.Open(fready)

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

 Dim sheet As Object ' 早期绑定:作为 Excel.Worksheet对于 book.Worksheets 中的每张纸'去做下一个

表单控件

所以我们正在寻找一个 CheckBox 表单控件.我们会在工作表的 Shapes 集合中找到它，并且当它的 Type 是 <代码>msoFormControl;当它的 FormControlType 属性返回 xlCheckBox 时，我们就会知道它是一个复选框控件:

 Dim sheet As Object ' 早期绑定:作为 Excel.Worksheet对于 book.Worksheets 中的每张纸Dim shp As Object ' 早期绑定:作为 Excel.Shape对于每个 shp 在 sheet.Shapes如果 shp.Type = 8 ' 早期绑定: msoFormControl如果 shp.FormControlType = 1 ' 早期绑定:xlCheckBox'去做万一万一下一个下一个

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

If shp.Name = "Check Box 1" Thenshp.ControlFormat.Value = 1 '选中万一

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

ActiveX 控件

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

Dim ctrl As Object ' 早期绑定:As Excel.OLEObjectFor Each ctrl In sheet.OLEObjectsIf TypeName(ctrl.Object) = "CheckBox" Then ' 早期绑定: If TypeOf ctrl.Object Is MSForms.CheckBox Then如果 ctrl.ShapeRange.Name = "CheckBox1" 然后ctrl.Object.Value = True ' 选中万一万一下一个

请注意，早期绑定的 TypeOf ctrl.Object Is MSForms.CheckBox 检查比后期绑定的 TypeName 检查更健壮.您需要通过 Tools > References 引用 MSForms 类型库才能使用它(如果您的 VBA 项目有任何 UserForm 组件，它已经被引用，在这种情况下，早期绑定代码是不费吹灰之力).

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?

My code:

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

解决方案

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

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

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

Excel_App.Workbooks.Open fready

You need this:

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

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

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

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 Controls

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

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屋！