Autoload UserForm TextBoxes

[Minitman]

Greetings,

I am trying to load a UserForm from one of a dozen different sheets.
I am using the Worksheet_BeforeDoubleClick event targeting column A to
start the UserForm. If the target cell has a date in it, I need to
load the rest of the row (6 cells) into six TextBoxes (TB! thru TB6).
All of the examples that I have are to load a named range into a
ComboBox and then load the TextBoxes from the ComboBox RowSource.

I can't do that here - No ComboBoxes!

This should be simpler, but I can't get it to work.

There is one little twist, the code to load is not in the UserForm
code section - it is in a standard module.

Here is the code that I am using:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'In the general module

Option Explicit
Public vTargetR As Variant

Sub CollectEntryData()
Dim i As Integer
Dim rTargetAddress As Range

rTargetAddress = Range("A" & vTargetR)
For i = 1 To 6
CustomerEntryForm.Controls("TB" & i).Value = _
rTargetAddress.Offset(0, i - 1).Value'<<<<Bad
Next i

End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'In each of the sheet code sections

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)

If Not Target.Count = 1 Or _
Not Target.Row > 2 Or _
Not Target.Column = 1 Then
Cancel = True
Exit Sub
Else
vTargetR = Target.Row
CustomerEntryForm.Show
Cancel = True
End If

End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++
'In the UserForm code section:

Private Sub UserForm_Initialize()

CollectEntryData

End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++

The bad code is in the CollectEntryData sub at the spot before the
"<<<<Bad".

the error message is:

Runtime error '91'
Object variable or With block variable not set

Anyone have any ideas or suggestion as to what could be wrong and/or
how to fix it?

Any help is appreciated, thanks.

-Minitman

 

[JLGWhiz]

Change this:

rTargetAddress = Range("A" & vTargetR)

To this:

Set rTargetAddress = Range("A" & vTargetR)

 

[Minitman]

Thanks JLGWhiz - That fixed the problem.

I don't understand the rules for when to use or not use "Set". And
not even sure where the rules are listed!

Thanks for the help.

-Minitman

 

[Dave Peterson]

Since you declared rTargetAddress to be an object (a range), you need to use the
Set statement:

Set rTargetAddress = Range("A" & vTargetR)

 

[JLGWhiz]

The rule is that if you want it to return an object, you use Set. Set VBA
help topic "Creating Object Variables" for more details.