Variable input ComboBox

[rutgerterhaar]

Hi there,
I have a problem with a ComboBox. I hope the following text makes you guys understand my problem.

I have 3 sheets (sheet1, sheet2 en sheet3).

In sheet1 I have column A with in each row an integers (A1 =1; A2 = 2, …). This number of rows is variable, so there could be 10 but also 100 rows in column A. Column A has to be the input for my ComboBox1. In Cell B1 I have a made a function that counts the number of integers in column A.

The ComboBox is placed in sheet3 and when I click on the ComboBox1 I want to see all the numbers in Sheet1 column A.

In sheet 2 I have a button to go to sheet3. In de code for this button I want to load the ComboBox1_Click code, so when I enter sheet3 via the button in sheet2 the ComboBox1 is up to date. When de input in sheet1 column A changes I want to make sure the selected item in the ComboBox1 in sheet 3 will not alter, but when I click the Combobox1 I want to see the new input from ColumnA.

This is the code I’ve written:

Public Sub ComboBox1_Click()

Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim x As Integer
Dim nCount As Range
Dim y As Integer

Set wsSource = Sheets("Sheet1")
Set nCount = wsSource.Range("B1")
y = 0

For x = 1 To nCount
y = y + 1
wsDest.ComboBox2.AddItem y
Next x

End Sub


Sub Go_To_Next()

'Update input ComboBox1
Sheets3.ComboBox1.Clear
Call Sheets3.ComboBox1_Click

End Sub

The problem:
The ComboBox fills and I can select a value and when I change the list in sheet1 column A the new data is visible, but it is added to the input of the ComboBox that was already there. And the selected input is gone, so I have to select the ComboBox again.

Hope you guys know a solution for this problem!

Thanks!!

Rutger