如何解决Excel VBA中的refedit错误?

huangapple go评论118阅读模式
英文:

How can I resolve this Error in refedit in excel VBA

问题

当我通过refedit在Excel工作表中选择一个单元格,然后点击以下表单中的箭头按钮:

然后再次点击refedit中的按钮

并选择一个单元格,在框中将显示两个单元格,它们之间没有逗号(尽管第一个单元格的地址有时会更改为第二个单元格,但通常第一个单元格的地址不会被删除):

如果我想将此字符串用作范围,这会导致错误。如何解决这个问题?通过一些附加代码?还是我的Excel 2016存在问题?

如果我可以在小窗口出现时清除refedit,那将非常好。但是refedit.value = ""或refedit.delete的代码无效。

英文:

When I select a cell in an excel sheet by a refedit and click on the arrow button in the following form:

如何解决Excel VBA中的refedit错误?

and then click on the button again in that refedit

如何解决Excel VBA中的refedit错误?

and select a cell, in the box there will be two cells that between them there is no comma(Although the address of the first cell sometimes changes to the second cell but usually the address of the first cell is not deleted):

如何解决Excel VBA中的refedit错误?

and if I want to use this string as a range , this cause an Error. How can I solve this problem? By some addition code? or
Does my excel-2016 have a problem?

If I could clear the refedit when the small window appear, that was very good. But the codes refedit.value="" or refedit.delete do not work.

Private Sub RefEdit1_Change()

Dim ra11 As Range
Set ra11 = Range(RefEdit1.Value)
k = ListBox8.ListCount
Dim ra22 As Range
For Each ra22 In ra11
    ListBox8.AddItem
    ListBox8.List(k, 0) = sh2.Cells(ra22.Row, rff1p_s.Column)
    ListBox8.List(k, 1) = sh2.Cells(ra22.Row, icolsar1p(14))
    k = k + 1
Next ra22

End Sub

答案1

得分: 1

在VBA中,**RefEdit**控件历来存在问题。以下的`Exit`事件代码尝试在输入错误时清理文本(范围引用)。希望这段代码对你有帮助。

~~~
Private Sub RefEdit1_Exit()
    Ref = Me.RefEdit1.Text
    If VBA.InStr(Ref, ",") = 0 Then
        If Len(Ref) - Len(Replace(Ref, "!", "")) > 1 Then Me.RefEdit1.Text = ""
    Else
        For Each subRef In Split(Ref, ",")
            If Len(subRef) - Len(Replace(subRef, "!", "")) > 1 Then
                Me.RefEdit1.Text = ""
                Exit For
            End If
        Next
    End If
End Sub
~~~
英文:

The RefEdit control in VBA has historically had the issue. The following Exit event code attempts to clean the text (range reference) when it is wrong. I hope the code is helpful.

Private Sub RefEdit1_Exit()
    Ref = Me.RefEdit1.Text
    If VBA.InStr(Ref, ",") = 0 Then
        If Len(Ref) - Len(Replace(Ref, "!", "")) >1  Then Me.RefEdit1.Text = ""
    Else
        For Each subRef In Split(Ref, ",")
            If Len(subRef) - Len(Replace(subRef, "!", "")) >1  Then
                Me.RefEdit1.Text = ""
                Exit For
            End If
        Next
    End If
End Sub

答案2

得分: 1

在我的评论后,将添加一个解决方法,以避免使用有问题的 RefEdit 控件。

先决条件。
控件:

  • 用户窗体
  • 文本框
  • 命令按钮

变量:

  • rangeToSet 作为范围 - 要分配的主要范围
  • inptResult 作为范围 - InputBox 结果变量以执行选择检查
  • isRangeSelected 作为布尔变量 - 用于进一步检查的模块全局布尔变量

用法:
按下按钮并选择范围 如何解决Excel VBA中的refedit错误?

代码:

Option Explicit

Dim isRangeSelected As Boolean

Private Sub selectRangeButton_Click()
Dim rangeToSet As Range
Dim inptResult As Range

Me.rangeTextBox.Text = ""

On Error Resume Next
Set inptResult = Application.InputBox(Prompt:="Select range", Type:=8)
Err.Clear
On Error GoTo 0

If Not inptResult Is Nothing Then
    Set rangeToSet = inptResult
    isRangeSelected = True
    Me.rangeTextBox.Text = rangeToSet.Address
Else
    Me.rangeTextBox.Text = "The range is not selected"
    isRangeSelected = False
End If

End Sub
英文:

Following my comment, will add a workaround to avoid using a glitchy RefEdit control.

Prerequisites.
Controls:
-UserForm
-TextBox
-CommandButton

如何解决Excel VBA中的refedit错误?

Variables:
-rangeToSet As Range - main range to be assigned
-inptResult As Range - InputBox result variable to perform the selection check
-isRangeSelected As Boolean - module global boolean variable for further checks

Usage:
Press the button and select range 如何解决Excel VBA中的refedit错误?

The code:

Option Explicit

Dim isRangeSelected As Boolean

Private Sub selectRangeButton_Click()
Dim rangeToSet As Range
Dim inptResult As Range

Me.rangeTextBox.Text = ""

On Error Resume Next
Set inptResult = Application.InputBox(Prompt:="Select range", Type:=8)
Err.Clear
On Error GoTo 0


If Not inptResult Is Nothing Then
    Set rangeToSet = inptResult
    isRangeSelected = True
    Me.rangeTextBox.Text = rangeToSet.Address
Else
    Me.rangeTextBox.Text = "The range is not selected"
    isRangeSelected = False
End If

End Sub

huangapple
  • 本文由 发表于 2023年7月28日 00:39:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76781829.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定