英文:
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:
and then click on the button again in that 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):
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 作为布尔变量 - 用于进一步检查的模块全局布尔变量
用法:
按下按钮并选择范围
代码:
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
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
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论