Add the value taken from the user to my range for ActiveX ComboBox

[niksirat2030]

Hi everyone,

I defined



a ActiveX ComboBox named "cmbPeymankar",
a Text Box (ActiveX control) called "txtNewPeymankar".
a Command Button (ActiveX control) called "Add".

I have a table named "Table1" in sheet "DATA" so that the first column of Table1 contains the names that should be displayed in the cmbPeymankar list and 10 items are already written in it.

I have three questions:



How to define the value of "ListFillRange" of cmbPeymankar so that the values of column 1 of Table1 are displayed in its list?
What command should I write for Add button to add the value taken from the user for txtNewPeymankar to the end of the column 1 of Table1?
The value of cmbPeymankar can be selected from its list. If the user wants to type its value, how can I write an error message if the entered value is not equal to any of the predefined items for cmbPeymankar (in column 1 of Table1)?

[Aussiebear]

Use Data Validation to ensure the input is acceptable

[niksirat2030]

Use Data Validation to ensure the input is acceptable

I new in excel and VBA, how can i do that?

[p45cal]

Are these ActiveX controls on a sheet or in a proper UserForm?
What version of Excel are you using?
Best attach a workbook with all this in.

[niksirat2030]

file attached.

[p45cal]

I have a table named "Table1" in sheet "DATA" so that the first column of Table1 contains the names that should be displayed in the cmbPeymankar list and 10 items are already written in it.

No you don't,
you have a Table1 in sheet Data_peymankar with 19 items
you have a table called Table in sheet DATA with about 15 items
you have a Table3 on sheet Sheet1 with 10 items.

What do you want?


ps. pay attention to the cross posting guides/rules, they're probably more important (to you) than you think!

[niksirat2030]

No you don't,
you have a Table1 in sheet Data_peymankar with 19 items
you have a table called Table in sheet DATA with about 15 items
you have a Table3 on sheet Sheet1 with 10 items.

What do you want?


ps. pay attention to the cross posting guides/rules, they're probably more important (to you) than you think!

I want column 1 of Table1 be the range of combobox "cmbName" in sheet Form.

With textbox txtNewPeymankar in sheet Form, add item to column 1 of Table1.

[p45cal]

I want column 1 of Table1 be the range of combobox "cmbName" in sheet Form.

With textbox txtNewPeymankar in sheet Form, add item to column 1 of Table1.

Both these are already in place.
There are a couple of things you can do to make things run more smoothly:
1. Add this line:

cmbName.ListFillRange = "ListOfPeymankar"
immediately after the line:
.Range("A" & iRow1).Value = txtNewPeymankar.Value

2. Make sure the bottom of Table1 doesn't have any empty rows, so change the extents of the table by dragging the table bottom right corner handle up, so from:
30094

to:

30095
You'll only ever have to do this if you manually delete values from the sheet.

You can also use the Table properties to set the listfillrange as suggested in one of your multiple undeclared cross posts.