Excel 2000 VBA (reference control on dialogue form)

[scobiej]

I'm putting together a spreadsheet app that prompts the user to enter values on a wizard dialogue within Excel, then it builds the spreadsheet in code etc etc.

I have pretty much finished it but now need to get values from the user by building a FORM. Up until now I just used a simple input box for most values.

anyway, i had a go and kinda cannot figure out how to do simple things like reference a text box i put on the form, about all i can do is get to its click event to enter code.

Does anyone know the syntax or know of any examples out there on the www that will get me up and running with forms within excel?

cheers
john

[Garratt]

Do some Google searches for things like "Excel VBA form example". For example, look at this page:
http://www.xl-logic.com/pages/vba.html

Once you have that page open, find (ctrl+F) the word "form" on that page over and over to quickly see which of the downloadable example vba project workbooks have forms.

Here's a starter:

'-------------------------------------------------
Let's say:
A) You make a textbox called "MyTextbox"
B) You have a Form called "Form1"



Code inside the Form somewhere:
MyTempString = MyTextbox.text

Code outside the Form (like a module):
Cells(1,3).value = Form1.MyTextbox.Text
'------------------------------------------------

[Garratt]

I forgot to mention....

When you are in the 'Form Builder', at the top of the window are two drop down listboxes.

The left one shows all available objects, including the form object itself. The right one shows a list of all event triggers you can use.

Once you play with it for awhile, you can just type the "Sub MyObject_(event)" subroutine definitions yourself.


Like the second example I typed above, the best thing to do is to call a form from a sub-routine in a module and then refer to the objects on the form after it has closed (form1.mytextbox.text).

Probably the best thing is if you have some input boxes and a button on the form like "go"

When you hide a form (form1.hide), the program will pick up right after the call that broght the form up.

(module1)
Sub MySub

Form1.show
'Form1 is visible
'When the user click a 'finish' button the form hides itself
'When the form hides itself the code picks back up here.....

Cells(1,2).value = Form1.mytextbox.text
If Form1.MyRadioButton1.value = True then Cells(1,3).value = "true"

End MySub



Another thing to mention is that if you need things to be done as soon as the form opens, in the form code you can use the "activate" event.

Sub UserForm1_Activate ()

'Do some stuff

End Sub


For example, you might want to load some listboxes or something on the form as soon as it opens.