VBA Calling a private function inside of a userfor

问题:

I have a userform defined as "SingleListForm". I have a private sub named "loadSingleListForm(ID As Double)" I am attempting to pass a variable into the form and I was implementing it in the fashion that loadSingleListForm would set the form up based on ID, basically re-using one form to show variable data from variable sources in the listbox.

But calling a standard Intersect from outside the form (Worksheet_SelectionChange) these two options compile but do not work.

Application.Run "SingleListForm.loadSingleListForm", ID 'ID already declared and assigned

This doesn't work either

Call ActiveWorkbook.UserForm("SingleListForm").loadSingleListForm(ID)

Where it says UserForm I have also tried SingleListForm. Here the runtime error is:

I am trying hard not to use a Global Variable here to pass to the form. Perhaps I should go to Initialize and try something there. I am trying to pass the variable to the form and then of course set up the form based on this case and then show the form. you can't pass with show so you have to find another way to set up.

I just realized I have not called a userform private function from outside of the form before, but I do it with modules all the time. The first case works in that instance.

Cheers, -WWC

回答1:

The better way to do this is to declare a property to the form. In the form's module enter

  Option Explicit
  Private myID as double

  Property Set ID(i as double)
     myID = i
  End Property

Then your function

   Private Sub loadSingleListForm()
     can refer to myID with in it's code

To Use this from outside modules you use

   Load SingleListForm
   SingleListForm.ID = ID 'ID variable already declared

回答2:

Declare your sub in the form as Public Public Sub loadSingleListForm(ID As Double) and then call it like this SingleListForm.loadSingleListForm ID

回答3:

Just to cover this. Empty workbook, one button. The button calls to a private function in the form that does nothing but open a message box. Testing concept here. This is all there is:

Doesn't work:

UserForm1.you_made_it

Error at compile, method or data member not found Same if this:

With ThisWorkbook
    UserForm1.you_made_it
  End With

Then try this:

Application.Run "UserForm1.you_made_it"

Error: Cannot run macro . . . .

Try this from first comment:

ActiveWorkbook.UserForm("UserForm1").you_made_it

Error: Object doesn't support this property or method

So this is the winner from above. Not sure I wanted to go Public but it works. Doesn't solve how to use a private member in the form but it gets the coding going forward.

Public Sub you_made_it()

    MsgBox ("you made it")

End Sub

So far: 1) Move the private to a module and then call it 2) make the function Public and it works

Thank you, -WWC