Filling a Form

[RussellT]

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]

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

 

[Dave Peterson]

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

 

[p45cal]

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

Code

 

[Dave Peterson]

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.

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/showthread.php?t=166947

Microsoft Office Help

 

[p45cal]

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.

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.

 

[Dave Peterson]

And it does qualify the object, too.

I'm not sure what message #2 is.

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.

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/showthread.php?t=166947

Microsoft Office Help