Excel:在保存之前必须填写一个字段，但让我保存空白表格 [英] Excel: make a field required before saving, but let me save the blank form

问题描述

简而言之，我要制作一个Excel表单，要求用户填写5个单元格，即使没有填写一个单元格也无法保存文件.问题是，在我当前的实现中，我什至无法保存要分发给我的同事的空白文档.有没有一种方法可以保存一次，然后再执行我的VBA脚本?下面是我为表单编写的代码:

Simply put, I want to make an Excel form that requires the user to fill in 5 cells, if even one isnt filled in then they cannot save the file. The issue is that with my current implementation I cant even save the blank document to be distributed to my coworkers. Is there a way to save once here and THEN have my VBA script work? below I have the code I've made for the form:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim whatCell As String
  whatCell = "B3"
  If Sheets("Report").Range(whatCell).Value = "" Then
     MsgBox "Pls enter value in Submitter"
     Cancel = True 'cancels the save event
     Exit Sub
   End If

  whatCell = "B4"
  If Sheets("Sheet1").Range(whatCell).Value = "" Then
     MsgBox "Pls enter value in Time/Date"
     Cancel = True 'cancels the save event
     Exit Sub
   End If

  whatCell = "B5"
  If Sheets("Sheet1").Range(whatCell).Value = "" Then
     MsgBox "Pls enter value in Customer Info"
     Cancel = True 'cancels the save event
     Exit Sub
   End If

  whatCell = "B6"
  If Sheets("Sheet1").Range(whatCell).Value = "" Then
     MsgBox "Pls enter value in Issue Description"
     Cancel = True 'cancels the save event
     Exit Sub
   End If

  whatCell = "B7"
  If Sheets("Sheet1").Range(whatCell).Value = "" Then
     MsgBox "Pls enter value in Repeatable"
     Cancel = True 'cancels the save event
     Exit Sub
   End If
End Sub

Private Sub Workbook_Open()

End Sub

推荐答案

找到一个空单元格(假设为A1)并在其中添加一个值.您的BeforeSave代码将检查该单元格是否已填充，如果未填充，则跳过该检查.退出之前，它会清除单元格.

Find an empty cell (let's say A1) and put a value in it. Your BeforeSave code checks to see if that cell's populated, and skips the checks if it is. Before exiting it clears the cell.

Option Explicit

Dim msg As String

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    msg = ""

    If Sheets("Sheet1").Range("A1").Value <> "" Then
        Sheets("Sheet1").Range("A1").Value = ""
        Exit Sub
    End If

    EmptyCell Sheets("Report").Range("B3"), "Submitter"
    EmptyCell Sheets("Sheet1").Range("B4"), "Time/Date"
    EmptyCell Sheets("Sheet1").Range("B5"), "Customer Info"
    EmptyCell Sheets("Sheet1").Range("B6"), "Issue Description"
    EmptyCell Sheets("Sheet1").Range("B7"), "Repeatable"

    If Len(msg) > 0 Then
        MsgBox "The following values are required before saving:" & _
                 vbLf & msg, vbExclamation
        Cancel = True
    End If

End Sub

Sub EmptyCell(rng As Range, msgErr As String)
    If rng.Value = "" Then msg = msg & vbLf & msgErr & _
       "   (" & rng.Parent.Name & "," & rng.Address(False, False) & ")"
End Sub

这篇关于Excel:在保存之前必须填写一个字段，但让我保存空白表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！