UserForm Initialise

[AlexanderBB]

I'm attempting to Load a UserForm from a Worksheet Double Click
I also want to pass a worksheet Range to the Form so using:

Public Property Let Dat (inVal as Range)
Set MyRow= inVal
End Property

When this is called from the worksheet BeforeDoubleClick

myForm.Dat = Me.Range("A" & Target.Row & ":F" & Target.Row)

the Userform Initialise event run. But it uses the Range not yet Set.
So a boolean Exits the Initialise event and the Property Let executes to Set MyRow.

But after that a Load (or Show) UserForm skips the initialise event, which is now wanted.
I can think of some hacks to maybe get around this, but what the correct way to do it please?

Thanks for any help.

 

[AlexanderBB]

It's a pity I can't upload a file here as both you you would get a better idea of what's going on.
I need the initialise code and the range to be available there, because that's how I've written it.

Have either of you struck this scenario? You set controls up on a Form and if you step through code everything is perfect.
But if you run it without stepping, a listbox does not keep the correct width and height. And I can prove it happening every time.

I can't use Activate because this Form may call another Form... and if you do stuff on that form, then come back to This Form to Close it, it's activate
event runs and width and height are mucked up. It's unbelievably frustrating...

 

[DanteAmor]

The double click event is activated only with one cell, then in the active cell you have the row.
Let's not complicate, use the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    myform.Show
End Sub

[COLOR=#0000ff]'Up in the general statements[/COLOR]
Dim dat As Range

[COLOR=#0000ff]'Code[/COLOR]
Private Sub UserForm_Activate()
    MsgBox dat.Address
End Sub

[B][COLOR=#000000]'You can use initialize with your range, but pointing to the active cell not to the Target object.[/COLOR][/B]
[COLOR=#0000ff]Private Sub UserForm_Initialize[/COLOR]()
    [B]Set dat =[/B] Range("A" & ActiveCell.Row & ":F" & ActiveCell.Row)
End Sub

 

[AlexanderBB]

Ah, thanks Dante, that was a great tip and has solved one of the issues.
I need to repeatedly call the Form from the worksheet so I added

                NoInit = True: Unload frmSM2: NoInit = False
                frmSM2.Show

Then first line in UserForm_Initialize

If NoInit = True Then Exit Sub

That seem okay to you ?

But the remaining issue is driving me batty. I have a listbox control which is populated in UserForm_Initialize
and (if I step through the code) all is 100%/ But if I run the code the listbox loses it's height and width.

If I debug.print it's height and width just before Ending UserForm_Initialize, they're correct.
But once finished they're wrong. Printing W & H in the debug.window also shows the wrong values.

So my question is, why are they OK if I step through the code, but not if I run it; and what changes them anyway ?

 

[DanteAmor]

You can put all your code to understand what you're doing

 

[AlexanderBB]

How could I get it all to you ? You can't upload attachments here.

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[AlexanderBB]

Hi Dante

https://www.dropbox.com/s/66jfmmz1mdo5kym/Book4.zip?dl=0

I have set up a test condition to show the problem. BTW I don't need it fixed (so much) as just to understand why it's doing this.

If you double click in column Prefix it will stop in UserForm_Initialize. I then type 'frmSMM2.show' in the debug window and all is OK.
If you run or step from that Stop all is OK. Stepping shows there is no more code anyway.

But if you remove the Stop, and run again, the ListBox width and height are never right.

Thanks for looking at this !