Excel VBA中Screen对象未找到报错，寻求解决方法

Hey Matt, let's sort out this issue for you.

First off, that "Variable Not Defined" error with the Screen object makes total sense—the Screen object is exclusive to Microsoft Access VBA, it doesn't exist in Excel's VBA environment at all. Adding references won't fix this because there's no type library you can reference to make Excel recognize it.

Now, let's get to the actual solution you need: making your Load sub work with whichever user form is active. Here are two solid approaches, starting with the most reliable one:

1. Pass the active form directly when calling Load (Recommended) #

This is the cleanest method because you don't have to guess which form is active—you explicitly tell the Load sub which one to work with.

First, update your Load sub to accept a user form object as a parameter:

Sub Load(formObj As Object)
    ' Grab the name of the active form that called this sub
    Dim targetFormName As String
    targetFormName = formObj.Name
    
    ' Fetch the corresponding worksheet (assuming sheet name matches form name)
    Dim dataSheet As Worksheet
    Set dataSheet = ThisWorkbook.Worksheets(targetFormName)
    
    ' Example: Populate a text box on the form with data from cell A1
    formObj.TextBox1.Value = dataSheet.Range("A1").Value
    
    ' Add your full data-filling logic here
End Sub

Then, in each user form's code (like a button click event), call the Load sub and pass Me (which refers to the current form):

Private Sub cmdLoadData_Click()
    ' Pass the current active form to the Load sub
    Call Load(Me)
End Sub

2. Auto-detect the active form using the UserForms collection + API #

If you really need the Load sub to automatically find the active form without being passed a parameter, you can use the UserForms collection (which holds all loaded user forms) and a Windows API to check which one is in the foreground.

First, add this API declaration at the top of your module (before any subs/functions):

' For 64-bit Excel; use Declare Function without PtrSafe for 32-bit
Private Declare PtrSafe Function GetForegroundWindow Lib "user32" () As Long

Then create a helper function to get the active form:

Function GetActiveUserForm() As Object
    Dim uf As Object
    ' Loop through all loaded user forms
    For Each uf In UserForms
        ' Check if the form's window handle matches the foreground window
        If uf.hWnd = GetForegroundWindow() Then
            Set GetActiveUserForm = uf
            Exit Function
        End If
    Next uf
    ' Return Nothing if no active form is found
    Set GetActiveUserForm = Nothing
End Function

Now update your original Load sub to use this helper:

Sub Load()
    Dim activeForm As Object
    Set activeForm = GetActiveUserForm()
    
    If Not activeForm Is Nothing Then
        Dim targetFormName As String
        targetFormName = activeForm.Name
        
        ' Same data-filling logic as approach 1
        Dim dataSheet As Worksheet
        Set dataSheet = ThisWorkbook.Worksheets(targetFormName)
        activeForm.TextBox1.Value = dataSheet.Range("A1").Value
    Else
        MsgBox "No active user form found!"
    End If
End Sub

A quick note: Approach 1 is way more straightforward and avoids potential issues with multiple forms being open at once. Stick with that unless you have a specific reason to auto-detect.

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