Passing a variable to a userform

I have a “flat” list of corrective actions in one sheet (ActionTotal), that are assigned to various people. Each action (row), has a unique number (“Ref”).
There is a userform (frmActionClose) that accepts a number as input in a textbox on the form (“txbACActionNumber”), finds the specific record, and if the “Date Closed” date is entered, closes the action for that person. To close an action requires that you know the “Ref” number for that record.
I have another sheet, with a pivot table, that will show all the open actions for one person. I would like to be able to select the “Ref” number on that sheet (“MyActions”), and pass that number to the Userform, so that the actions can be closed without having to have a separate list of “Ref” numbers.
How do I pass this number to the userform “frmCloseActions”, so that it uses the value as the value of the “txbACActionNumber”? I can read the value from the form easily enough, and open the userform, but where to then?

Thanks in advance
OldMan1950

Hi OldMan62,

You have to save the value somewhere that is accessible to the UserForm. Two ways I have done this is to:
a. Save the value in a cell on the Worksheet. You may have to clear that cell after you are done using the value.
b. Save the value in a global variable. For example, in an ordinary code Module:

Please Login or Register  to view this content.

'myGblVariable' can be used to store/retrieve the value from any macro. Notice how the global variable is declared AFTER 'Option Explicit' and BEFORE the first Macro.

Lewis

If you have selected your reference. Then just tell the userform to read the active cell value.

Please Login or Register  to view this content.

I made a couple of small changes...

Please Login or Register  to view this content.

Sent from my iPhone using Tapatalk

Originally Posted by romperstomper

Jeff,
You can't overload the Show method of a userform in VBA - it simply won't compile. Can you post a workbook demonstrating this? I'd be very interested to see it.

I'm sorry, absolutely right. It has been a while since I've done that and I got my languages confused. What I actually did was created a new Sub, rather than overloading.

Please Login or Register  to view this content.

Please Login or Register  to view this content.