Vedr: [ExcelVBA] Re: loop with userform

 

Torstein,

From one not-too-sophisticated (self-educated on VBA) user to another.

This is a simplified overview of UserForms.

You can consider a UserForm to be just another "Object"; a bit similar to a Sheet. It has the Form itself and code "Behind it".
You can place a button on the Form that says, for example "Finished". That is called the button's "Caption".

You add a button to the form when looking at the form by: DeveloperMenu>View>ToolBox> then select the rectangle that is called CommandButton. Make the Caption you want to appear on the button in the Properties (right click the button).

Then, after placing the button, double-clicking it will form the start of the subroutine that will execute when a user clicks that button. It also brings you to that Sub to add your own code.

In that Sub you can have more code that places the data you collected in the form, somewhere else such as in one of the sheets, or in a variable or array.
Right-Clicking on any button (or anything else) you place on the UserForm and selecting Properties allows you to see its name and change it if you like.

The name of the text box (or whatever you use to capture the user input) is also in the properties. You use that name to get to the data the user entered.

Something like this:
AnswerVariable = TextBox1.Value

If you change the name of TextBox1 to, say, UserInput, then it would look like this:
AnswerVariable = UserInput.Value

If you want to put the data on one of the sheets, it can be done several ways, but may look like this:
Sheet1.Range("aRangeName").Value = UserInput.value
Sheet1.Range("F5").Value = UserInput.value
...or...
Sheet3.Cells(row, collumn) = UserInput.Value

Hope this helps, Steve N.

--- In ExcelVBA@yahoogroups.com, Torstein Johnsen <sejohnse@...> wrote:
>
> Thanks to Dave and Lisa! 
>
>
> Your advices pointed me in the right direction, but I think I have to do more studies on userforms.
>
>
> A short question to Dave - in your example how do the user end his input? By closing the userform or with a command button, or else?
>
>
> I've used VBA mostly to arrange lists of data from medical reports, not collecting data from users.
>
>
> Yes I want the loop to run 25 times, letting the user give answers on some matrix reasoning tests.
>
>
> Getting help from you experts is invaluable!
>
>
> Torstein
>
>
>
> ________________________________
> Fra: Green <1z@...>
> Til: ExcelVBA@yahoogroups.com
> Sendt: Mandag, 1. juli 2013 17.21
> Emne: Re: [ExcelVBA] Re: loop with userform
>
>
>
>  
> Nice Dave... tx.
>
> Torstein... Do you actually want that 25 times loop in there?
>
> Maybe a Do Loop would be better...
>
> Do
>
> ' Test the input and if valid Exit Do
> ' May want to do that if no input is given as well.
>
> Loop
>
> -----Original Message-----
> From: dmgathmann <dmgathmann@...>
> To: ExcelVBA <ExcelVBA@yahoogroups.com>
> Sent: Mon, Jul 1, 2013 3:40 am
> Subject: [ExcelVBA] Re: loop with userform
>
> Torstein,
>
> To expand somewhat on what Lisa wrote, I've included code for the loop that adds
> some user conveniences, such as clearing the textbox and refocusing the cursor
> to the textbox.
>
> Sub trythis()
> Dim i As Integer
> Dim TheEntry As String
>
> For i = 1 To 25
> UserForm1.Show
> TheEntry = UserForm1.TextBox1.Text
> UserForm1.TextBox1.Text = "" 'need to clear the textbox after you've
> captured the entry
> UserForm1.TextBox1.SetFocus 'puts the focus back in the textbox
> Next i
> End Sub
>
> Dave Gathmann
>
> --- In ExcelVBA@yahoogroups.com, Torstein Johnsen <sejohnse@> wrote:
> >
> > i have a loop with an inputbox
> >
> > for i = 1 to 25
> >
> > svar = inputbox("answer?")
> >
> > next i
> >
> > I want to replace the inputbox with an userform, which I'm not familiar with.
> >
> > How can I return from the userform to the loop when the user has written his
> answer?
> >
> > Thank you!
> >
> > Regards
> >
> > Torstein
> >
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
> http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES
> for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (8)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

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

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___