Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate form filling | Excel Discussion (Misc queries) | |||
Form Filling | Excel Discussion (Misc queries) | |||
Filling in a form from a list | Excel Discussion (Misc queries) | |||
filling a form with an array | Excel Programming | |||
filling a form in webpage | Excel Discussion (Misc queries) |