☰

检查单元格 - 如果单元格 2 包含特定的不同文本，则停止保存

tag-icon • tag-icon vba macros validation
检查单元格 - 如果单元格 2 包含特定的不同文本，则停止保存

我是 vba 新手，一直在按需要解决问题。任何帮助或线索都将不胜感激！我对宏有新要求。我有一个宏，它将复制 ws、创建文件名、将 excel 另存为 csv 并重新打开 csv。在保存之前，我需要确保两个单元格（A2 和 F3）不包含某些文本。我需要在 VBA 中使用它

检查 A2 是否包含“项目名称”，如果是，则显示消息“供应项目名称”，如果否，则继续检查 F2 是否包含“# XXXX”，如果是，则显示消息“输入数字”，如果没有，则保存（或继续执行宏）

For Each cell In Range("A2,F3")
If cell.Value = "Project Name" Then
      Cancel = True
      If Cancel Then MsgBox "You will need to Enter a Project Name before saving", vbOKOnly, "Needs a Project Name"
    ElseIf cell.Value = "# XXXX" Then
    If Cancel Then MsgBox "Please Enter a Number", vbOKOnly, "Needs a number"

End If

这是我的宏，我将在保存之前插入验证

Dim FileName As String
    Dim ws As Worksheet
    Set ws = ActiveSheet


'Creates New FileName - Concatenates username and Desktop path with for New Name
    NewName = Environ("USERPROFILE") & "\Desktop\" & Range("B2").Value & " - JIRA Import" & ".CSV"

'Makes a copy of the Worksheet
    ws.Copy

    Application.DisplayAlerts = False


<<-- Validataion would go Here-->>

'Save Worksheet as a CSV File
    Debug.Assert Not ActiveWorkbook Is ThisWorkbook
    ActiveWorkbook.SaveAs FileName:=NewName, _
    FileFormat:=xlCSV, CreateBackup:=False

'Removes List of Names from People Picker
    Range("G50:G64").Select
    Selection.ClearContents

    Application.DisplayAlerts = True

'Shows user a message
    MsgBox "File saved to Desktop for JIRA Import " & vbNewLine & NewName

'Closes CSV files
    ActiveWorkbook.Close
    Application.DisplayAlerts = False

'Reopens CSV File Without Macro - Clean CSV
  Application.Workbooks.Open (NewName)

  FileExist = False
Next Cell
End Sub

答案1

我想我解决了。下面是我的代码

If Range("A2").Value = "Project Name" Then
    Range("A2").Interior.Color = vbCyan
    MsgBox "Please provide Project Name Before Saving"
    Exit Sub
Else
Set fnd = Range("F3").Find("xx")
If Not fnd Is Nothing Then
    Range("F3").Interior.Color = vbCyan
    MsgBox "Please provide Number and Name"
Exit Sub
End If
End If

相关内容