VBA代码修改求助：多复选框场景下单元格数据填充问题

Hey there! I see exactly where your current code is tripping up—let's get it sorted so it handles all three selection scenarios perfectly.

What's Off with the Original Code? #

Your Do While loops have two key issues:

1. The third loop accidentally uses CheckBox7 (same as Germany) instead of the checkbox tied to Hongkong (I’m guessing that’s CheckBox8—you’ll want to fix that reference if my hunch is right).
2. The loop conditions don’t enforce filling exactly 6 rows per selected checkbox. For example, if only CheckBox6 is checked, your loop stops at i < 8, which fills B3-B7 (only 5 cells) instead of the intended B3-B8.

Updated Code That Handles All Scenarios #

Here’s a revised approach that first collects all user-selected locations, then fills the correct cell ranges in order:

Private Sub SubmitButton_Click() ' Replace with your actual button's event name
    Dim selectedLocations As Collection
    Dim loc As Variant
    Dim rowStart As Integer
    Dim i As Integer
    
    ' Set up a collection to hold only the locations the user checked
    Set selectedLocations = New Collection
    
    ' Add locations to the collection in your desired order
    With UF1_Location_and_Role
        If .CheckBox6.Value Then selectedLocations.Add "India"
        If .CheckBox7.Value Then selectedLocations.Add "Germany"
        If .CheckBox8.Value Then selectedLocations.Add "Hongkong" ' Fixed checkbox reference here
    End With
    
    ' Clear old values in B3-B17 (optional but keeps things clean)
    Range("B3:B17").ClearContents
    
    ' Fill cells in 6-row blocks for each selected location
    rowStart = 3
    For Each loc In selectedLocations
        ' Fill 6 rows starting at rowStart
        For i = rowStart To rowStart + 5
            If i <= 17 Then ' Don't go beyond B17
                Cells(i, 2).Value = loc
            End If
        Next i
        rowStart = rowStart + 6 ' Jump to the next block of rows
    Next loc
End Sub

How This Works #

• Collect Selected Locations: We use a Collection to gather only the locations the user checked, preserving the order you want (India first, then Germany, then Hongkong).
• Clean Up Old Data: Optional but ensures leftover values from previous selections don’t stick around.
• Fill Rows in Blocks: For each selected location, we fill exactly 6 rows:
  • 1 selection → B3-B8
  • 2 selections → B3-B8, then B9-B14
  • 3 selections → B3-B8, B9-B14, and B15-B17 (since 15+5=20 which is beyond 17, it stops at B17)
• Covers All Scenarios: Works seamlessly for no selections, partial selections, and full selections.

Quick Checks #

• Replace SubmitButton_Click with the actual name of your UserForm’s submit button event.
• Double-check the checkbox names (I assumed Hongkong uses CheckBox8—adjust if your checkbox has a different name).

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