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.

 

[DanteAmor]

In your userform, up, only :
Public dat as range

and in your doubleclick event

with myform 
set .dat = Range("A" & Target.Row & ":F" & Target.Row ) 
.Show 
end with

 

[AlexanderBB]

Stepping through the code, as soon as reaching with myform the next thing that
executes is Private Sub UserForm_Initialize() which expects Dat to be Set. Thus repeating error "Run-time error '91':
Object variable or With block variable not set"

 

[DanteAmor]

You must do it in the activate event

In DoubleClick event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With UserForm1
        Set .dat = Range("A" & Target.Row & ":F" & Target.Row)
        .Show
    End With
End Sub

In userform

[COLOR=#0000ff]Public dat As Range[/COLOR]


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

 

[AlexanderBB]

No, that does not work. Line 3

With UserForm1

executes UserForm_Initialize() which goes to error as the Range hasn't been set yet.
Anyway I can probably work out some way of doing this; my question was to see what might be the right way.

 

[Norie]

Try this.

Worksheet module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim frm As UserForm1

    Cancel = True
    
    Set frm = New UserForm1
    
    Set frm.Dat = Me.Range("A" & Target.Row & ":F" & Target.Row)
    
    frm.Show
    
End Sub

Userform module

Option Explicit

Private GetRow As Range

Property Set Dat(ByRef rng As Range)
    Set GetRow = rng
End Property

Private Sub UserForm_Activate()
    MsgBox GetRow.Address
End Sub

 

[DanteAmor]

But you must remove your initialize event, since that event is executed before loading the variables

 

[AlexanderBB]

I need the initialise event. Norie, your "Set frm.Dat..." also goes to UserForm_Initialize().

 

[Norie]

What do you need the Initialize event for?

 

[DanteAmor]

You can use the initialize event, but do not occupy the dat variable there.

Public dat As Range


Private Sub UserForm_Activate()
MsgBox dat.Address


End Sub


Private Sub UserForm_Initialize()
'your code


End Sub