Setting up a UserForm

[YellowLabPro]

I have the beginnings of a UserForm. This is my first go at this... creating a UserForm. This is from the Contextures site, I have substituted in my values up until this point.

The purpose is to add terms to two sheets. I have one sheet currently in my code.
I would like to proceed one step at a time to figure out what I can along the way.

The first thing I would like to do is set up a unique list that will allow only the terms to be chosen for the .txtDisplayOnWeb.Value
The other thing I would like to do is, automatically enter the day's date in the text box, and if it needs to be written over then fine, but if it is acceptable, then it is already filled in.


ws.Cells(iRow, 2).Value = Me.txtDisplayOnWeb.Value


I have an idea to use the Advanced Filter to achieve this. I will host the list on the same page, in column C.


Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ColTab")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Name
If Trim(Me.txtEnterTerm.Value) = "" Then
Me.txtEnterTerm.SetFocus
MsgBox "Enter Original Term"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtEnterTerm.Value
ws.Cells(iRow, 2).Value = Me.txtDisplayOnWeb.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
'clear the data
Me.txtEnterTerm.Value = ""
Me.txtDisplayOnWeb.Value = ""
Me.txtDate.Value = ""
Me.txtEnterTerm.SetFocus
End Sub

[Bob Phillips]

Where will this 'list' be? AUtofilter isn't a list in the selectable sense, just a set of items.

The date is simple



Me.txtDate.Text = Format(Date, "dd/mm/yyyy")

[YellowLabPro]

The list will reside in col. "B" from B2:B?

[YellowLabPro]

Bob,
It does not have to autofilter, I can create the list and then update it as needed. That should make it easier to create....

[Bob Phillips]

Sorry Doug, I am not getting it.

The Add code just adds the TextBox details to the worksheet in the next free row.

You say it does not have to autofilter, you can create the list and update as needed. This bit is what I don't understand. Filtering column B, easy. Create the list ...?

[Zack Barresse]

Doug, are you talking about checking if the values exist already in the list? I'm not sure I'm following either..

[YellowLabPro]

Bob,
Let me start from scratch... or almost from scratch.
I have two columns of data. Col. A will be the term provided by the company's product name, entered through the first text box of the userform.
I want to assign a standardized color for these non-standard color terms; limiting the number of possible values.

The standard color term will be entered/selected through the user form via a combo box, which will only allow a limited number of choices via a list residing in Col. D.

Example:

Col. A Col.B
Poppy is Yellow
Porter is Brown
Indigo is Dk.Blue
Infrared is Red
Jet is Black


I am going to re-post all the code, this will show you changes I have made and make it easier for you not having to guess at what is going on.
I have already replaced the second me.txtDisplayOnWeb w/ .cmbDisplayOnWeb line of code.
Not knowing how to code the combo box control is where I would like your assistance in creating the code that will allow the combo box to access the list in Col. D.


Option Explicit
Private Sub CommandButton1_Click()
End Sub
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ColTab")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Name
If Trim(Me.txtEnterTerm.Value) = "" Then
Me.txtEnterTerm.SetFocus
MsgBox "Enter Original Term"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtEnterTerm.Value
ws.Cells(iRow, 2).Value = Me.cmbDisplayOnWeb.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
'clear the data
Me.txtEnterTerm.Value = ""
Me.txtDisplayOnWeb.Value = ""
Me.txtDate.Value = ""
Me.txtEnterTerm.SetFocus
End Sub
Private Sub Label1_Click()
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub EnterDate_Click()
End Sub
Private Sub txtDate_Change()
End Sub
Private Sub UserForm_Click()
End Sub

[Bob Phillips]

I bet a pound to a penny that I still haven't understood, but you can load the combo from column D with



Set rng = Range("D2").End(xlDown)
ComboBox1.List = Application.Transpose(rng)

[YellowLabPro]

Sorry Bob,
I really tried hard to write a clear concise explanation. I give up.... back to finger painting I guess.....:(

[YellowLabPro]

Hello Zack,
I had not considered that point yet, but that would be a nice enhancement to what I am trying to do.
Currently I have about 550 colors that I do a lookup and replace on.
The userform's job is to add the new color name and a standard name for the company's color name to the list when a new one is needed.

(I acutally have to added to two different sheets, and it is becoming more and more difficult to keep it straight.)

Here you can have a look: this link you will see a drop down list of the standard colors to filter on. The description contains the companies color name.

http://dev.ecboardco.com/product_listing/46

This list will continue to grow, the companies will find new ways of calling Blue, Blue.... :-)

[YellowLabPro]

Bob,
one thing that Malik told me was changing the property of the combo box in the Row Source to this: SubTerms!$D$1:$D$4
This works like I want it, but is a fixed range. I will have approximately 50 different possiblilities for the standard colors so I guess I could take this approach and change it to $D$50, but I would rather make it dynamic.

[Norie]

Doug

I know you are looking into it, and this isn't related to this thread really.

But I think it might be time that you look at using Access or another database for what you are doing.:)

[mdmackillop]

You can use a dynamic range name eg
MyColours =OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)