VBA新手求助：制作含特定输入项的Excel Userform及工作表锁定、自动保存问题

Hey there! Let's walk through building this Excel solution step by step—since you're new to VBA, I'll make sure each part is clear and easy to follow. Here's how to tackle your requirements:

1. Build the UserForm for Data Input #

First, let's create the form where users will enter their data:

• Open your Excel file, press Alt + F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > UserForm.
• Add these controls from the Toolbox (press Ctrl + T if you don’t see it):
  • A DatePicker (name it dpDate) for selecting the date.
  • A TextBox (name it txtTime) for time input—set its InputMask property to 00:00 to enforce HH:MM format.
  • A TextBox (name it txtProduction) for产量—we’ll add code later to block non-numeric input.
  • A ComboBox (name it cboTask) for task selection (add default items via its List property in the Properties window, or use a TextBox for free-form entries).
  • A multi-line TextBox (name it txtComments) for comments—set its MultiLine property to True and ScrollBars to 2 - fmScrollBarsVertical.
  • Two CommandButtons: one named btnSubmit (caption "Submit") and one named btnClose (caption "Close").

2. Add VBA Code to the UserForm #

Double-click the UserForm to open its code window, then paste this code (I’ve added comments to explain each part):

Private Sub UserForm_Initialize()
    ' Set default values to today's date and current time
    dpDate.Value = Date
    txtTime.Value = Format(Time, "HH:MM")
End Sub

Private Sub btnSubmit_Click()
    Dim wsData As Worksheet
    Dim nextRow As Long
    
    ' Reference your data worksheet (replace "DataSheet" with your actual sheet name)
    Set wsData = ThisWorkbook.Worksheets("DataSheet")
    
    ' Unlock the data sheet temporarily (replace "YourPassword" with your secure password)
    wsData.Unprotect Password:="YourPassword"
    
    ' Find the next empty row in column A
    nextRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Write entered data to the sheet
    wsData.Cells(nextRow, "A").Value = dpDate.Value
    wsData.Cells(nextRow, "B").Value = TimeValue(txtTime.Value)
    wsData.Cells(nextRow, "C").Value = Val(txtProduction.Value) ' Convert input to number
    wsData.Cells(nextRow, "D").Value = cboTask.Value
    wsData.Cells(nextRow, "E").Value = txtComments.Value
    
    ' Lock the sheet again
    wsData.Protect Password:="YourPassword", UserInterfaceOnly:=True
    
    ' Clear the form for next entry
    txtTime.Value = Format(Time, "HH:MM")
    txtProduction.Value = ""
    cboTask.Value = ""
    txtComments.Value = ""
    
    MsgBox "Data submitted successfully!", vbInformation
End Sub

Private Sub btnClose_Click()
    Unload Me
End Sub

' Block non-numeric input in the production text box
Private Sub txtProduction_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not (KeyAscii >= 48 And KeyAscii <= 57) And KeyAscii <> 8 Then ' Allow numbers and backspace
        KeyAscii = 0
        MsgBox "Please enter only numbers for production.", vbExclamation
    End If
End Sub

Note: Replace "DataSheet" with your actual data worksheet name, and "YourPassword" with a password of your choice.

3. Lock Input & Data Worksheets #

We want users to only interact with the UserForm, not the worksheets directly. Here’s how to set that up:

• For your input and data sheets:
  1. Go to the worksheet, right-click the sheet tab > Protect Sheet.
  2. Uncheck all boxes except "Select unlocked cells" (we’ll lock all cells first via the Format Cells menu if needed).
  3. Enter your password, then click OK.
• To auto-lock sheets when the workbook opens, add this to the ThisWorkbook module:

Private Sub Workbook_Open()
    ' Lock input sheet (replace "InputSheet" with your sheet name)
    ThisWorkbook.Worksheets("InputSheet").Protect Password:="YourPassword", UserInterfaceOnly:=True
    ' Lock data sheet
    ThisWorkbook.Worksheets("DataSheet").Protect Password:="YourPassword", UserInterfaceOnly:=True
End Sub

The UserInterfaceOnly:=True setting lets VBA modify the sheet even when it’s protected—no need to manually unlock/lock every time!

4. Auto-Save on Workbook Close #

To ensure the workbook saves automatically when closing, add this code to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Close the UserForm if it's open
    If UserForms.Count > 0 Then
        Unload UserForms(0)
    End If
    
    ' Save the workbook
    ThisWorkbook.Save
    MsgBox "Workbook saved successfully!", vbInformation
End Sub

Quick Testing & Troubleshooting Tips #

• Test the UserForm first: Press F5 while in the UserForm code window to run it, and try submitting data to confirm it writes to the correct sheet.
• If you get a "permission denied" error, double-check your password and ensure the UserInterfaceOnly setting is enabled.
• For extra time validation, add a check like If Not IsDate("00:" & txtTime.Value) Then MsgBox "Please enter a valid time!" in the submit button code.

内容的提问来源于stack exchange，提问作者T.Vega