User form Text Box & Combo Box.

[Brian]

I have a user Form that has several Text Boxes & Combo Boxes. From this Form
I want the user to Input Data and have it placed through out the rest of the
workbook.

I got the User Form to show up when you click a cell on the worksheet, but
for some reason it shows up as a seperate screen and not as sheet 1. The name
of the sheet I want it as is named "Data Input".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Show
End Sub

How can I get the Text and Combo Box on the user form to to fill in the
worsheet. Also How can I added the different choices to the combo box?

I tried, but it did not work for some reason.

With UserForm1.ComboBox1
.AddItem "a"
.AddItem "b"
.AddItem "co"
End With

 

[Sam Wilson]

Hi Brian,

"it shows up as a seperate screen" - yes it will do. It is a seperate object
and not a worksheet.

This is the principle...

Your first sub is fine as it is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Show
End Sub

Next, you need to add code to the form itself - in the VB window right click
the form and "view code" - paste this in:

Private Sub UserForm_Initialize()

with me.combobox1
.additem "a"
.additem "b"
.additem "c"
end with

End Sub


(You need to have the form open to add items to the combo boxes, which is
why you do the adding in the initialise event)

Finally, add a command button "OK" to the form - this will populate the
sheet when the user clicks it. You can right click the button in the VB
window and add the following code:


Private Sub CommandButton1_Click()

Application.EnableEvents = False

Worksheets("Sheet1").Range("A1").Value = Me.combobox1.Value
'etc

Application.EnableEvents = True

Unload Me

End Sub


Hope that gets you started.

Sam

 

[Brian]

It worked perfect on the 1st Combo, but when I went to paste the Code in the
2nd combo box with differnt information under .AddItem "A" it is giving me a

Compile Error
Ambiguous Name Detected: UserForm1_Initialize

I assumed the code was the same for all of the Combo Boxes just differnt Info.

Please, what did I do wrong. I am new to this but really want to learn.

 

[Dave Peterson]

The name of the procedure that's built into excel is "UserForm_Initialize".

You can't change the name or make up your own event procedures.

But you can add as much as you want/need to that procedure:

Private Sub UserForm_Initialize()

with me.combobox1
.additem "a"
.additem "b"
.additem "c"
end with

with me.combobox2
.additem "x"
.additem "y"
.additem "z"
end with

End Sub

 

[Sam Wilson]

Further to Dave's comments, the idea is that the sub "UserFrom_Initialize"
runs when the form is loaded, so inside that sub you need to do all the stuff
that takes place to get it ready for the user's input - so for each combo box
you'd add all the relevant items etc.

Sam

 

[Brian]

It worked perfect.

Can you help with how to assign the text boxes (Varibles), so that when I
fll in all the text boxes I can use a command button to send the information
to the various Workbooks.

The Workbooks are going to be Titled as follows:

WorkBook Name Command Button Name
--------------------------------------------------------------------------
Engineering Spec Workbook = Update Engineerring Spec Sheet
Installer Forms = Update Installer Forms
Job Folder Label = Job Folder Label

Thanks
Brian