I need to send a target.row variable to a User form Initialization sub. Is it Possible?

Hello all,

I thought I would pose another question to the forum as I'm almost out of hair to pull out! I guess when in doubt, turn to those who know more than you do, right? Here is my situation. I have a worksheet that watches a range for changes. When an entry is made in the range, a user form is called. I am pre-populating the user form with information in columns 1-4 of the activeCell.row. However, if the excel installation uses the default "After pressing enter, move selection down" the activeCell.row uses the wrong row number. Since a user form initialization does not take an argument, how can I use the target.row from the sub that called the userform instead of the activecell.row? Is there a way to return the row number of the cell that was most recently changed so that a user form can use it?

I thank you all for your continued assistance and willingness to assist in furthering my Knowledge.

Scott

When a change is made, the Change Event gets the address of the cell(s) that have changed, regardless of what the active cell is after Enter is pressed.

So Target.Row will relate to the cell that has changed. If you want to pass it on, one option is to create a global variable and store the value there. Then you can pick it up in the next routine that needs it.


Regards, TMS

Scott

Which worksheet event are you using?

instead of using userform_initialize (to which you cannot directly pass anything, although you could use a public variable as has been mentioned) you can create your own pubic routine in the form that you can call before showing the form

@JP:

you can create your own pubic routine in the form

Sounds an interesting approach

omg! edited now!