excel - 如何根据输入文本值格式验证文本并将其插入 Excel 工作表定义的位置？

Question

我想解析插入的单元格文本并使用它来正确填充相应工作表中的单元格数据。例如，根据工作B2表上单元格中的值，1.1我想将文本插入工作表上的指定位置1.2。单元格文本以下列格式指定数据的目标位置：

<destination_sheet_name>:<destination_panel_id>:<destination_module_id>

在这种情况下，工作表上的相应数据1.2应如下所示，并包含源工作表中数据的位置： <source_sheet_name>:<source_panel_id>:<source_module_id>

您认为不编写 VBA 脚本是否可行？您能否提出一种方法来为非专业 Excel 用户实现这一目标；-)？

标签： excelvbaexcel-formulaexcel-2016

Answer 1

1. Alt+F11
2. 双击左侧窗格中的工作簿模块（通常称为“ThisWorkbook”）。
3. 粘贴此代码。我试图评论重要部分。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim s() As String
  Dim shTarg As Worksheet
  Dim lngTargPanelRow As Long
  Dim lngTargModuleCol As Long
  Dim sSourcePanel As String
  Dim sSourceModule As String
  Dim c As Range

  On Error GoTo EndSub
  If Target.Count = 1 Then
    ' Checking if the new value in the cell  (ambiguously called Target) has 2 colons
    If Len(Target) - Len(Replace(Target, ":", "")) = 2 Then
      ' Making an array with elements which are separated by colons in the cell
      s() = Split(Target, ":")
      ' Associating the VBA object with the particular sheet by its name
      Set shTarg = Me.Sheets(s(0))
      ' Finding the target row
      Set c = shTarg.Range("$A:$A").Find(s(1))
      If c Is Nothing Then GoTo EndSub Else lngTargPanelRow = c.Row
      ' Finding the target column
      Set c = shTarg.Range("1:1").Find(Replace(s(2), "M", "Module "))
      If c Is Nothing Then GoTo EndSub Else lngTargModuleCol = c.Column
      ' Finding the source panel
      sSourcePanel = Sh.Cells(Target.Row, 1).Value
      ' Finding the source module
      sSourceModule = Sh.Cells(1, Target.Column).Value
      ' Shortening the source module name
      sSourceModule = Replace(sSourceModule, "Module ", "M")
      ' Putting the value into the target cell
      Application.EnableEvents = False
      shTarg.Cells(lngTargPanelRow, lngTargModuleCol) = Sh.Name & ":" & sSourcePanel & ":" & sSourceModule

    Else
EndSub:
      MsgBox "No changes were made in any other cell"
    End If

    If Err <> 0 Then MsgBox "Error " & Err & " - " & Err.Description
    Application.EnableEvents = True
  End If
End Sub