Change this:

rTargetAddress = Range("A" & vTargetR)

To this:

Set rTargetAddress = Range("A" & vTargetR)






"Minitman" wrote in message
...
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