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

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

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


On Mon, 8 Feb 2010 22:30:09 -0500, "JLGWhiz"
wrote:

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

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.



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


On Mon, 8 Feb 2010 22:30:09 -0500, "JLGWhiz"
wrote:

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

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

Set rTargetAddress = Range("A" & vTargetR)

Minitman wrote:

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

--

Dave Peterson