VBA用户表单作为输入框 [英] VBA Userform as Inputbox

问题描述

我有一个用户窗体，您可以在其中编辑Excel工作表单元格中的文本.由于输入框没有跳过按钮，因此我制作了一个用户窗体，您可以在其中搜索整个工作簿中的值，并将其替换为另一个值条目.问题是我要一个一个地编辑发现的值，而不是一次全部编辑.我有以下代码，但是它的作用是，它将替换每个工作表中发现的值的第一次出现.我该如何更改替换第一张纸然后再替换第二张纸的值的逻辑.

I have an userform on which you can edit the text in cells of an excel sheet. Since the inputbox doesn't have a skip button, I made an Userform where you can search a value in the whole workbook and replace it with another value entry. The thing is I want to edit the found values one by the other and not all at the same time. I have the following code but what this does is, it replaces every first occurance of the found value in every sheet. How could I change the logic that it replaces the values from first sheet then from second and so on.

Dim ws As Worksheet
Dim Loc As Range
Dim StrVal As String
Dim StrRep As String
Private Sub CommandButton1_Click() 
    StrVal = UserForm1.TextBox3.Text

    If Trim(StrVal) = "" Then Exit Sub
    For Each ws In ThisWorkbook.Worksheets
        With ws.UsedRange
            Set Loc = .Cells.Find(What:=StrVal)
            If Not Loc Is Nothing Then
                'Do Until Loc Is Nothing
                    Application.Goto Loc, False 
                    StrRep = TextBox1.Text
                    If Not StrRep = "" Then
                    Loc.Value = StrRep
                    Set Loc = .FindNext(Loc)
                    Else
                    Exit Sub
                    End If
                'Loop
            End If
        End With
        Set Loc = Nothing

    Next
End Sub

我创建了一个可行的方法，但是现在有了一个问题，即使没有现成的值，它也会跳过.

I created this which works but has now the problem that it skips though even if there is no existing value.

Set ws = ThisWorkbook.ActiveSheet
Set Loc = ws.Cells.Find(what:=StrVal)
If Not Loc Is Nothing Then
    Application.Goto Loc, False
    StrRep = TextBox1.Text
    If Not StrRep = "" Then Loc.Value = StrRep
Else
    Worksheets(ActiveSheet.Index + 1).Select
End If

推荐答案

尝试一下

For Each ws In ThisWorkbook.Worksheets
    Application.Goto ws.Range("A1"), True
    Set Loc = ws.Cells.Find(What:=strVal, After:=ws.Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 'You can change MatchCase to True for better checking
    If Not Loc Is Nothing Then
        strFirstAddress = Loc.Address
        changeBool = False
        Do
            strRep = Me.TextBox1.Text
            If Not strRep = "" Then
                Application.Goto ws.Range(Loc.Address), False
                If MsgBox("Do you want to change the value of cell " & Loc.Address(0, 0) & " of worksheet " & ws.Name & "?", vbYesNo + vbQuestion, "Question") = vbYes Then
                    Loc.Value = strRep
                    If Loc.Address = strFirstAddress Then
                        changeBool = True
                    Else
                        changeBool = False
                    End If
                Else
                    changeBool = False
                End If
            End If
            Set Loc = ws.Cells.FindNext(After:=Loc)
            If Not Loc Is Nothing Then
                If Loc.Address = strFirstAddress Then Set Loc = Nothing
            End If
            If Not Loc Is Nothing And changeBool = True Then strFirstAddress = Loc.Address
        Loop While Not Loc Is Nothing
    End If
Next ws

这篇关于VBA用户表单作为输入框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！