接手VBA宏重构：用户窗体需从新录入数据单元格启动问题咨询

Hey there! It sounds like you want to tweak your inherited VBA macro so the userform pops up directly near the cell where you'll be entering new data—way more intuitive for users. Let's modify your existing userform code to add this positioning logic, plus clean up a small issue with the "End" button.

Here's the revised code with all the changes:

Option Explicit

Private Sub UserForm_Initialize()
    ' Keep your existing dropdown population logic
    Me.ComboBox2.List = Sheets("Lookups").ListObjects("HilvlActivitySourceLookup").ListColumns(1).DataBodyRange.Value
    
    ' Position the form right by the active cell when it loads
    PositionFormNearActiveCell
End Sub

Private Sub CommandButton2_Click()
    ' Write selected value to active cell (added a safety check)
    If Not ActiveCell Is Nothing Then
        ActiveCell.Value = ComboBox2.Value
    End If
    Unload Me
End Sub

Private Sub EndButton_Click()
    ' Replace "End" with Unload Me to avoid abruptly terminating all VBA code
    Unload Me
End Sub

' Custom sub to position the form near the active cell
Private Sub PositionFormNearActiveCell()
    Dim cellScreenLeft As Double, cellScreenTop As Double
    
    ' Exit if there's no active cell (edge case safety)
    If ActiveCell Is Nothing Then Exit Sub
    
    ' Calculate the active cell's position on the screen
    cellScreenLeft = ActiveCell.Left + ActiveCell.Parent.WindowLeft
    ' Position form just below the cell (adjust this to move it above/right/left if needed)
    cellScreenTop = ActiveCell.Top + ActiveCell.Parent.WindowTop + ActiveCell.Height
    
    ' Override default startup position and set our custom location
    Me.StartUpPosition = 0
    Me.Left = cellScreenLeft
    Me.Top = cellScreenTop
End Sub

Key Changes Explained: #

• Added Positioning Logic: The PositionFormNearActiveCell sub calculates the active cell's screen coordinates and moves the form right below it. You can adjust the cellScreenTop calculation (e.g., remove + ActiveCell.Height to place the form over the cell) to fit your layout preference.
• Replaced End with Unload Me: Using End in your original code would abruptly terminate all running VBA code—Unload Me just closes the form cleanly.
• Added Safety Checks: We added a check for ActiveCell Is Nothing to avoid errors if no cell is selected when the form loads.
• Tied Positioning to Initialization: The form positions itself as soon as it loads, so it's in the right spot from the moment the user sees it.

If your "new data entry cell" isn't the active cell (e.g., it's a specific cell in a newly added row), just replace ActiveCell in the PositionFormNearActiveCell sub with your target cell reference (like Sheets("YourDataSheet").Range("B" & NewRowNumber)).

内容的提问来源于stack exchange，提问作者Someday I'll be great at VBA