用户窗体和范围 [英] UserForm and Range

问题描述

我有一个 Excel 工作表，其中 D 列(第 4 列)是每行的下拉列表，有 2 个选项:

I have an Excel sheet with column D (column 4) being a dropdown list for every row with 2 choices :

• 是的
• 否

当我单击否"时，我会弹出一个用户表单，其中包含一个简单的文本区域"，要求输入一个值和一个提交按钮"以进行验证.

When I click No, I have a Userform pop up with a simple "text zone" asking to enter a value and a "Submit button" to validate.

单击提交按钮"时，我希望将文本区域"中的值实现到右侧的单元格中:offset(0,1).

When the "Submit button" is clicked, I want the value from the "text zone" to be implemented into the cell to the right : offset(0,1).

例如:D5 : "No" -> "Enters 5 in Userform" -> E5: "5"

Example : D5 : "No" -> "Enters 5 in Userform" -> E5: "5"

这是我目前的代码:

工作表:

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Column = 4 Then
        If ActiveCell.Value = "no" Then
            UserForm1.Show
        End If
    End If
End Sub

用户表单:

Private Sub CommandButton1_Click()
    ActiveCell.Offset(0, 1).Value = TextBox1.Value
    UserForm1.Hide
End Sub

如果我将 UserForm1.Hide 放在 ActiveCell 之前，它会执行我想要的操作，但 UserForm 不会关闭.如果我取出 ActiveCell，用户窗体将关闭，但我似乎无法同时使两者都工作.

If I put the UserForm1.Hide before the ActiveCell it does what I want but the UserForm won't close. If I take out the ActiveCell the UserForm closes but I can't seem to make both work at once.

推荐答案

您正在更改 Worksheet_Change 处理程序中的单元格，这意味着如果您没有阻止 UI 的表单，您会很快破坏调用堆栈并遇到堆栈空间不足"错误，也称为...堆栈溢出.

You're changing cells in the Worksheet_Change handler, which means if you didn't have a form to block the UI, you'd quickly blow the call stack and run into an "Out of stack space" error, also known as a... stack overflow.

您需要防止 Worksheet_Change 处理程序递归调用自身.

You need to prevent your Worksheet_Change handler from calling itself recursively.

这可以通过在进行更改之前关闭 Application.EnableEvents 并在之后重新打开来完成:

And this can be done by turning off Application.EnableEvents before you make the change, and toggling it back on afterwards:

Application.EnableEvents = False
ActiveCell.Offset(0, 1).Value = TextBox1.Value
Application.EnableEvents = True

现在，看看有什么问题?表单如何知道它是从 Worksheet_Change 处理程序调用的，因此它需要切换 Application.EnableEvents?它不知道 - 而现在，它假设它.

Now, see what the problem is with that? How does the form know that it's being invoked from a Worksheet_Change handler and so that it needs to toggle Application.EnableEvents? It doesn't know - and right now, it's assuming it.

这是一个问题，只是因为表单正在运行.翻转一下，让表单尽可能地愚蠢，让 Worksheet_Change 处理程序负责更改工作表和切换Application.EnableEvents 状态:

This is a problem, only because the form is running the show. Flip things around, and leave the form as stupid as it can possibly be, and make the Worksheet_Change handler responsible for making the sheet changes and toggling theApplication.EnableEvents state:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Not IsError(Target.Value) Then
        If Target.Value = "no" Then
            With New UserForm1
                .Show
                If .Proceed Then
                    Application.EnableEvents = False
                    Target.Offset(0, 1).Value = .Contents
                    Application.EnableEvents = True
                End If
            End With
        End If
    End If
End Sub

几件事:

1. 触发事件的单元格是 Target - 在 ActiveCell 上使用 that.
2. 如果该单元格的值为 #N/A 或任何其他单元格错误值，您的代码就会崩溃.使用 IsError 来验证首先将单元格的值与任何内容进行比较是否安全.
3. 表单现在需要 Proceed 和 Contents 属性，并且不允许自毁.
4. 调用代码不关心任何文本框:它不知道 Contents 是如何填充的 - 这是表单的问题.

1. The cell that triggered the event is the Target - use that over ActiveCell.
2. If the value of that cell is #N/A or any other cell error value, your code blows up. Use IsError to verify whether it's safe to compare the cell's value with anything first.
3. The form now needs Proceed and Contents properties, and can't be allowed to self-destruct.
4. The calling code doesn't care about any textboxes: it doesn't know how Contents is getting populated - that's the form's concern.

那么表单的代码隐藏现在是什么样的?

So what would the form's code-behind look like now?

Option Explicit
Private mProceed As Boolean
Private mContents As String

Public Property Get Proceed() As Boolean
    Proceed = mProceed
End Property

Public Property Get Contents() As String
    Contents = mContents
End Property

Private Sub TextBox1_Change()
    mContents = TextBox1.value
End Sub

Private Sub CommandButton1_Click()
    mProceed = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

现在表单所做的就是收集数据，并将其公开以供调用代码查看:它不知道或不关心任何 ActiveCell 或工作表 - 它收集数据并公开它用于查看调用代码.不多不少.

Now all the form does, is collect data, and expose it for the calling code to see: it doesn't know or care about any ActiveCell or worksheet - it collects data, and exposes it for the calling code to see. Nothing more, nothing less.

这篇关于用户窗体和范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！