Hey there! Let's sort out your VBA userform issue right away.

核心错误原因 #

The Compile error: Method or data member not found you're hitting is directly caused by this line:

Cells(emptyRow, 1).Value = ComboBox1.Value1

VBA's ComboBox control doesn't have a Value1 property—this is a simple typo. The correct property to get the selected value from a ComboBox is just Value.

关键修复步骤 #

1. Fix the ComboBox property name
   Replace the problematic line with:

   Cells(emptyRow, 1).Value = ComboBox1.Value
   

2. Fix a syntax typo in the close button
   Your CommandButton2_Click procedure has a missing space in EndSub—this will also cause compilation issues. Correct it to:

   Private Sub CommandButton2_Click()
       Unload Me
   End Sub ' Added space between End and Sub
   

Optional Robustness Improvements #

To make your code more reliable and maintainable, consider these tweaks:

• Avoid activating sheets unnecessarily
  Instead of Sheet1.Activate, directly reference the worksheet object to prevent unexpected behavior if another sheet is active:

  Dim ws As Worksheet
  Set ws = Sheet1
  ' Use a more reliable way to find the last used row (avoids issues with blank rows in column A)
  emptyRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
  ' Assign values directly to the worksheet object
  ws.Cells(emptyRow, 1).Value = ComboBox1.Value
  ws.Cells(emptyRow, 2).Value = TextBox1.Value
  ' ... repeat for other cells
  

• Simplify checkbox caption logic
  Your current code first sets the cell to CheckBox1.Value then overwrites it with the caption. You can streamline this with the IIf function:

  ws.Cells(emptyRow, 5).Value = IIf(CheckBox1.Value, CheckBox1.Caption, CheckBox2.Caption)
  

Verify the Fix #

After making these changes, recompile your code (press F5 in the VBA editor) and test the userform. It should now successfully populate your Sheet1 columns with the form data.

内容的提问来源于stack exchange，提问作者user1773603