UserForm Trouble

[Saladsamurai]

Hello there! I don't really understand how user forms work. I have read through 3 tutorials, but I am having trouble with the very basics here (like the actual flow of how the underlying code works). Here is what I have built so far:

In Excel:

A command button in a worksheet whose code reads:



Private Sub CommandButton1_Click()
LabTools_UserForm.Show
End Sub


In VBA:

A userform whose (Name) property = LabTools_UserForm

The "main" userform code is:


Private Sub UserForm_Click()
With DropDownMenu
.AddItem "Create Spreadsheet from .LVM File"
.AddItem "Create Spreadsheet from .TXT File"
End With
End Sub


A combobox whose name property = DropDownMenu

I thought that by clicking in my command button in the Excel worksheet, it would automatically run the Private Sub UserForm_Click() subroutine, which would populate my combobox with the items above. However, when I click on the combobox, it is not populated.

Can someone tell me where I am confusing things?
Thanks for your time.

[Saladsamurai]

I got it ... I had to use the "initialize" thingy (what is it called? event? or class? method?)

However, if I click on the dropdown arrow in my combobox, the 2 items above appear. Then, without closing the userform, if I click in the dropdown arrow again to change my selection, it populates the combobox again, so now there are 4 items in it.


This is what I changed the code to:

Private Sub UserForm_Click()
Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
With DropDownMenu

.AddItem "Create Spreadsheet from .LVM File"
.AddItem "Create Spreadsheet from .TXT File"

End With
End Sub



I think that it literally runs the code everytime you click on the userform. Right? I really just want to populate the form once. Is there a better way to do this?

[Bob Phillips]

No, no, no.

The Userform_click event is fired when you click the userform. In 99.99999999999\dot{9}% of applications, you won't want anything to happen if someone randomly clicks on the userform.

You just want the Initialize event to load the dropdown, and the DropDown change event to read the selected value.

[Saladsamurai]

:o: I guess it was not necessary to "Call" the initialize routine.

[Bob Phillips]

It certainly was not, that fires automatically when you load the form.

[Saladsamurai]

No, no, no.

The Userform_click event is fired when you click the userform. In 99.99999999999\dot{9}% of applications, you won't want anything to happen if someone randomly clicks on the userform.

You just want the Initialize event to load the dropdown, and the DropDown change event to read the selected value.


Haha! Is \dot{X} the Chilean version of \overbar{X} ? I.e., repeating decimal?

[Saladsamurai]

Hey xld, I have a related question: My Userform is to allow the user to chose from a number of tasks in the combobox. The subsequent code that will be executed will depend on the task chosen. Each of the possible sub routines needs totally different input form the user.

I trying to think of the cleanest way to prompt the user for these inputs. I am currently thinking that depending on the task selected, a new userform will pop up and can be used to take input.

Can you think of any other, maybe cleaner, ways of doing this? Just brainstorming a little.

[mikerickson]

Its good that you found the Userform_Click event, I use it frequently for debugging and testing since it never (minus epsilon) is used in the final code.

About your multi-task userform, its generally cleaner to use a MultiPage control than to use multiple userforms.

[Bob Phillips]

Haha! Is \dot{X} the Chilean version of \overbar{X} ? I.e., repeating decimal?

Exactly that :)

[Bob Phillips]

Its good that you found the Userform_Click event, I use it frequently for debugging and testing since it never (minus epsilon) is used in the final code.

The .00000000000000000000000000001% ROFL). But you have to remember to remove it, or have conditionally compiled code.

I always hate my progress bars when debugging - they really get in the way.

[Saladsamurai]

Hey guys, I don't really want to start a new thread for this:

I have an initialize routine as follows:

Private Sub UserForm_Initialize()
TextBox1.Text = "SpreadsheetName.xls"
TextBox2.Value = 100
TextBox3.Value = 2#
End Sub

2 questions:

1) When the user form comes up, I want the value of textbox3 to be displayed out to two decimals, e.g. 2.00. How to accomplish this?

2) Is there a way to set the properties or something so that when the userform comes up, the values/text being displayed in the textboxes, is that "highlighted blue" so that it is clear to the user that they should be changing these?

Thanks!

[Bob Phillips]

Private Sub UserForm_Initialize()
TextBox1.Text = "SpreadsheetName.xls"
TextBox1.BackColor = RGB(144, 192, 255)
TextBox2.Value = Format(100, "#,##0.00")
TextBox2.BackColor = RGB(144, 192, 255)
TextBox3.Value = 2#
TextBox3.BackColor = RGB(144, 192, 255)
End Sub

[Saladsamurai]

Private Sub UserForm_Initialize()
TextBox1.Text = "SpreadsheetName.xls"
TextBox1.BackColor = RGB(144, 192, 255)
TextBox2.Value = Format(100, "#,##0.00")
TextBox2.BackColor = RGB(144, 192, 255)
TextBox3.Value = 2#
TextBox3.BackColor = RGB(144, 192, 255)
End Sub



Hey XLD! The precision part works great, but that's not really what i meant for color. Perhaps "highlight" was a poor choice of words on my part. What meant was, if you open an excel document and then click file->save as it will automatically assume a name for your file and it will "select" that text as well. See the image below. Perhaps there is a .select I could use. I'll mess around.

http://i12.photobucket.com/albums/a220/saladsamurai/Screenshot2011-01-02at24530PM.png

[Bob Phillips]

ROFL!



Private Sub TextBox1_Enter()
With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub

Private Sub TextBox2_Enter()
With Me.TextBox2
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub

Private Sub TextBox3_Enter()
With Me.TextBox3
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = "SpreadsheetName.xls"
TextBox2.Value = Format(100, "#,##0.00")
TextBox3.Value = 2#
End Sub