|
Autoload UserForm TextBoxes
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 |
Autoload UserForm TextBoxes
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 |
Autoload UserForm TextBoxes
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 |
Autoload UserForm TextBoxes
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 |
Autoload UserForm TextBoxes
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 |
| All times are GMT +1. The time now is 10:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com