Variable input ComboBox

rutgerterhaar

New Member
Joined
Jan 21, 2012
Messages
18
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top