Filling a Form
 

I have a form with 78 TextBoxes on it and a WorkSheet with 78 Columns. I want
to fill the form with the data on a specific row of the WorkSheet. I've
written the following code but can't figure out why it doesn't work.

Public Sub LoadLongInfo3(ByVal selectedProject As String)
Dim databaseRow As Long
Dim Boxes As Integer
Dim BoxName As String
Set devdataSheet = Sheets("DevData")

'Find DataRows To Be Loaded Into Form
devdataSheet.Activate
devdataSheet.Cells.Find(What:=selectedProject,
After:=devdataSheet.Cells(1,1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True).Activate
databaseRow = ActiveCell.Row
'Load Data Into Form
For Boxes = 1 To 78
BoxName = "TempBox" & Boxes
BoxName.Value = devdataSheet.Cells(databaseRow, Boxes)
Next
End Sub

When I run this code I get error message Object Required and the codes stops
at the line:
BoxName.Value = devdataSheet,Cells(databaseRow, Boxes)

Any help would be appreciated.

Filling a Form
 

Where are these textboxes?

Are they on a worksheet? If yes, did you use the textboxes from the control
toolbox toolbar or the textboxes from the Drawing toolbar?

Are they part of a real userform (created in the VBE)?

I'm gonna guess that it's part of a userform:

Option Explicit
Private Sub UserForm_Initialize()
Call LoadLongInfo3("somethinggoeshere")
End Sub
Public Sub LoadLongInfo3(ByVal selectedProject As String)

Dim DataBaseRow As Long
Dim FoundCell As Range
Dim Boxes As Long
Dim BoxName As String
Dim DevDataSheet As Worksheet

Set DevDataSheet = Sheets("DevData")

'Find DataRows To Be Loaded Into Form
With DevDataSheet
Set FoundCell = .Cells.Find(what:=selectedProject, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)

If FoundCell Is Nothing Then
'it wasn't found, what should happen
Else
DataBaseRow = FoundCell.Row

For Boxes = 1 To 2 '78
BoxName = "TempBox" & Boxes
Me.Controls(BoxName).Value _
= .Cells(DataBaseRow, Boxes).Value
Next Boxes
End If
End With
End Sub




RussellT wrote:

I have a form with 78 TextBoxes on it and a WorkSheet with 78 Columns. I want
to fill the form with the data on a specific row of the WorkSheet. I've
written the following code but can't figure out why it doesn't work.

Public Sub LoadLongInfo3(ByVal selectedProject As String)
Dim databaseRow As Long
Dim Boxes As Integer
Dim BoxName As String
Set devdataSheet = Sheets("DevData")

'Find DataRows To Be Loaded Into Form
devdataSheet.Activate
devdataSheet.Cells.Find(What:=selectedProject,
After:=devdataSheet.Cells(1,1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True).Activate
databaseRow = ActiveCell.Row
'Load Data Into Form
For Boxes = 1 To 78
BoxName = "TempBox" & Boxes
BoxName.Value = devdataSheet.Cells(databaseRow, Boxes)
Next
End Sub

When I run this code I get error message Object Required and the codes stops
at the line:
BoxName.Value = devdataSheet,Cells(databaseRow, Boxes)

Any help would be appreciated.

--

Dave Peterson

Filling a Form
 

ps.

I used a userform with 2 textboxes.

Change this line to 78 (from 2) when you're done testing:
For Boxes = 1 To 2 '78

Dave Peterson wrote:

Where are these textboxes?

Are they on a worksheet? If yes, did you use the textboxes from the control
toolbox toolbar or the textboxes from the Drawing toolbar?

Are they part of a real userform (created in the VBE)?

I'm gonna guess that it's part of a userform:

Option Explicit
Private Sub UserForm_Initialize()
Call LoadLongInfo3("somethinggoeshere")
End Sub
Public Sub LoadLongInfo3(ByVal selectedProject As String)

Dim DataBaseRow As Long
Dim FoundCell As Range
Dim Boxes As Long
Dim BoxName As String
Dim DevDataSheet As Worksheet

Set DevDataSheet = Sheets("DevData")

'Find DataRows To Be Loaded Into Form
With DevDataSheet
Set FoundCell = .Cells.Find(what:=selectedProject, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)

If FoundCell Is Nothing Then
'it wasn't found, what should happen
Else
DataBaseRow = FoundCell.Row

For Boxes = 1 To 2 '78
BoxName = "TempBox" & Boxes
Me.Controls(BoxName).Value _
= .Cells(DataBaseRow, Boxes).Value
Next Boxes
End If
End With
End Sub

RussellT wrote:

I have a form with 78 TextBoxes on it and a WorkSheet with 78 Columns. I want
to fill the form with the data on a specific row of the WorkSheet. I've
written the following code but can't figure out why it doesn't work.

Public Sub LoadLongInfo3(ByVal selectedProject As String)
Dim databaseRow As Long
Dim Boxes As Integer
Dim BoxName As String
Set devdataSheet = Sheets("DevData")

'Find DataRows To Be Loaded Into Form
devdataSheet.Activate
devdataSheet.Cells.Find(What:=selectedProject,
After:=devdataSheet.Cells(1,1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True).Activate
databaseRow = ActiveCell.Row
'Load Data Into Form
For Boxes = 1 To 78
BoxName = "TempBox" & Boxes
BoxName.Value = devdataSheet.Cells(databaseRow, Boxes)
Next
End Sub

When I run this code I get error message Object Required and the codes stops
at the line:
BoxName.Value = devdataSheet,Cells(databaseRow, Boxes)

Any help would be appreciated.

--

Dave Peterson

--

Dave Peterson

Filling a Form
 

Of course, after you've edited the textboxes on the userform you'll be
able to update the sheet with a line such as this in the middle of a
similar loop:

Code:
--------------------
devdataSheet.Cells(databaseRow, Boxes).Value = UserForm1.Controls(BoxName).Value

--------------------


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166947

Microsoft Office Help

Filling a Form
 

If that code is in the userform module, then I wouldn't use the name (UserForm1)
in the code.

Instead:

devdataSheet.Cells(databaseRow, Boxes).Value = Me.Controls(BoxName).Value

Me is a keyword that refers to the object owning the code--in this case, it
would be the userform--no matter what it's called.

p45cal wrote:

Of course, after you've edited the textboxes on the userform you'll be
able to update the sheet with a line such as this in the middle of a
similar loop:

Code:
--------------------
devdataSheet.Cells(databaseRow, Boxes).Value = UserForm1.Controls(BoxName).Value

--------------------

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166947

Microsoft Office Help

--

Dave Peterson

Filling a Form
 

Dave Peterson;602379 Wrote:
If that code is in the userform module, then I wouldn't use the name
(UserForm1)
in the code.

Instead:

devdataSheet.Cells(databaseRow, Boxes).Value =
Me.Controls(BoxName).Value

Me is a keyword that refers to the object owning the code--in this
case, it
would be the userform--no matter what it's called.

p45cal wrote:

Of course, after you've edited the textboxes on the userform you'll
be
able to update the sheet with a line such as this in the middle of a
similar loop:

Code:
--------------------
devdataSheet.Cells(databaseRow, Boxes).Value =
UserForm1.Controls(BoxName).Value

--------------------

--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: 'Filling a Form - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=166947)

'Microsoft Office Help' ("http://www.thecodecage.com")

--

Dave Peterson

Absolutely true. I said as much at the end of msg #2. You don't even
need the Me. bit, but it helps generate intellisense suggestions when
developing the code.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166947

Microsoft Office Help

Filling a Form
 

And it does qualify the object, too.

I'm not sure what message #2 is.

p45cal wrote:

Dave Peterson;602379 Wrote:
If that code is in the userform module, then I wouldn't use the name
(UserForm1)
in the code.

Instead:

devdataSheet.Cells(databaseRow, Boxes).Value =
Me.Controls(BoxName).Value

Me is a keyword that refers to the object owning the code--in this
case, it
would be the userform--no matter what it's called.

p45cal wrote:

Of course, after you've edited the textboxes on the userform you'll
be
able to update the sheet with a line such as this in the middle of a
similar loop:

Code:
--------------------
devdataSheet.Cells(databaseRow, Boxes).Value =
UserForm1.Controls(BoxName).Value

--------------------

--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: 'Filling a Form - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=166947)

'Microsoft Office Help' ("http://www.thecodecage.com")

--

Dave Peterson

Absolutely true. I said as much at the end of msg #2. You don't even
need the Me. bit, but it helps generate intellisense suggestions when
developing the code.

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166947

Microsoft Office Help

--

Dave Peterson