Value from form to excel sheet

[[email protected]]

Hi guys..
I have an excel sheet and a userform.
When user open the form , they will answer some of the questions like
yes /no in the textboxes, I have to record these answers in the
worksheet.
It should be happend for a loop, like if l give the range 1 to 100, it
should open the form 100times allow to enter the data and store it in
the sheet in the cells ,lets call answer1 to answer 10.
so the cellreference will be like :

Sheets("Mysheet").Range("Answer1 " & i)=userform1.txtanswer1.text

....

How can i do this...

thanks..

[Tom Ogilvy]

XL97: How to Use a UserForm for Entering Data (Q161514)
http://support.microsoft.com/?id=161514


XL2000: How to Use a UserForm for Entering Data (Q213749)
http://support.microsoft.com/?id=213749


--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Hi guys..
> I have an excel sheet and a userform.
> When user open the form , they will answer some of the questions like
> yes /no in the textboxes, I have to record these answers in the
> worksheet.
> It should be happend for a loop, like if l give the range 1 to 100, it
> should open the form 100times allow to enter the data and store it in
> the sheet in the cells ,lets call answer1 to answer 10.
> so the cellreference will be like :
>
> Sheets("Mysheet").Range("Answer1 " & i)=userform1.txtanswer1.text
>
> ...
>
> How can i do this...
>
> thanks..
>

[[email protected]]

Hi Tom.
This is ok.. But my case is different.
I am working out this partiuclar bit in between some other works
through loops..

I am calling a loop from say 1 to 100 and it will do many other
validations and scoring in each time and after that it will display
some details of the row like name , number etc from the sheet and asks
for this answers.. so if i use the loop and way of adding, how can i
get the aorrect answers of each row into the sheet..

You got me.??
when i use the

userform1.show
the value of the 'i' for loop may be 10 ( lets say like that) , so it
should add the value to the 10th column.

I added a button on the screen to add it, but how can i get that value
of 'i' in the form .

thanks

[Tom Ogilvy]

Make i a public variable in the module with the code that calls the
userform. Then the userform can see i and use it in its code.


At the top of the general module

Public i as Long

then don't declare it again in your sub or you will be working with a
different (local) version of i.
--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Hi Tom.
> This is ok.. But my case is different.
> I am working out this partiuclar bit in between some other works
> through loops..
>
> I am calling a loop from say 1 to 100 and it will do many other
> validations and scoring in each time and after that it will display
> some details of the row like name , number etc from the sheet and asks
> for this answers.. so if i use the loop and way of adding, how can i
> get the aorrect answers of each row into the sheet..
>
> You got me.??
> when i use the
>
> userform1.show
> the value of the 'i' for loop may be 10 ( lets say like that) , so it
> should add the value to the 10th column.
>
> I added a button on the screen to add it, but how can i get that value
> of 'i' in the form .
>
> thanks
>

[David Adamson]

depending upon the crontol you are using (if dropdown or listboxes the
selcted item is 0 based)
Else
if you have named the contol its just
controlname.value

So it would be something like
For i = 1 to controlname.value
would let to loop until the value +1 stored in controlname was reached.





<[email protected]> wrote in message
news:[email protected]...
> Hi Tom.
> This is ok.. But my case is different.
> I am working out this partiuclar bit in between some other works
> through loops..
>
> I am calling a loop from say 1 to 100 and it will do many other
> validations and scoring in each time and after that it will display
> some details of the row like name , number etc from the sheet and asks
> for this answers.. so if i use the loop and way of adding, how can i
> get the aorrect answers of each row into the sheet..
>
> You got me.??
> when i use the
>
> userform1.show
> the value of the 'i' for loop may be 10 ( lets say like that) , so it
> should add the value to the 10th column.
>
> I added a button on the screen to add it, but how can i get that value
> of 'i' in the form .
>
> thanks
>

[[email protected]]

Thanks Tom...its working ...thanks...