How to return many edit box values in a loop?????

Posted by naeblis on December 12, 2001 11:39 AM

I am trying to use a macro to return edit box values from a dialogue box.

My edit boxes are named box1,box2,...,box50

I would like to return the values of these boxes to a spreadsheet without having to list each one of these out. Like this...

counter=1
for i=1 to 50

Sheets("my_SHEET").Range("a" & counter).Value="box" & i.value
counter=counter+1
next i

As you can see above I can not figure out the right side of the equation to return the value of each box in this loop.

Can anyone help?

Thanks,
Naeblis

Posted by Anonymous on December 12, 2001 12:20 PM

Nice name. I love WOT. NT

Posted by Gary on December 12, 2001 12:30 PM

You can loop through all the EditBoxes on a dialog box by using the EditBoxes collection of the sheet in question.

Dim objEditBox as EditBox
Dim wsPaste as Worksheet
Dim intCounter as Integer

Set wsPaste=Worksheets.Add

For Each objEditBox in Sheets("Sheet with dialog").EditBoxes
intCounter=intCounter+1
wsPaste.Cells(intCounter,1).Value=objEditBox.Name
wsPaste.Cells(intCounter,2).value=objEditBox.Text
Next objEditBox

This should give you the name and value in each edit box.

Gary

Posted by Damon Ostrander on December 12, 2001 12:36 PM

Hi Naeblis,

The answer to this depends on whether you really mean a Dialog or are really referring to a TextBox on a Userform. Development with dialogs hasn't existed since Excel 5, although they are still supported for backwards compatibility pusposes. Also, Userforms don't support Forms Editbox objects, but rather TextBox controls. The answer also depends on whether you want to run the code while the "dialog" is running and where you plan to put the code (i.e., in the userform's event code area, in a macro module, etc.).

I will assume you are really talking about a Userform, and you want to run the code from the Userform's event code, such as a button click event. In this case your code should look like:

for i=1 to 50
Sheets("my_SHEET").Cells(i,1).Value=Controls("box" & i).Value
next i

If the assumptions above are not correct, feel free to follow up.

I eliminated the counter variable because in your code it always has the same value as i and therefore was interchangeable with i.

Happy computing.

Damon