Don't add item in combobox ...

[Marie J-son]

Hi,

I have combobox's directly in the worksheet (not using a form) and on
Worksheet_activate and ws_deactivate I run macros. At first I started with
only the first sub at ws_activate, but the list continued to add items all
the time. Therefore I added the one to clear the list on deactivation.
Please tell me how I should do this in a better way. The list tell how many
columns should be visibel and now every time I activate the ws, all columns
are visible again and the combobox cell is blank (but the list is ok .;-).

Of cource, I want it to stay as it is when I leave the ws, and the right
number of columns should show in the combobox cell :

Please tell me if you know how to do this.

/ Regards


SUBS:

Sub Worksheet_activate()
Application.EnableEvents = False
Application.ScreenUpdating = False
With Blad3.ComboBox1
.AddItem "1 st"
.AddItem "2 st"
.AddItem "3 st"
End With

With Blad3.ComboBox2
.AddItem "1 st"
.AddItem "2 st"
.AddItem "3 st"
.AddItem "4 st"
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub Worksheet_deactivate()
Application.EnableEvents = False
Application.ScreenUpdating = False

Blad3.ComboBox1.Clear
Blad3.ComboBox2.Clear

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Chip Pearson]

In your Activate event code, test whether the ListCount property
of the Combobox is 0. If so, load the box, else do nothing.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Marie J-son]

OK, it work alright, I have deleted the deactivate event also, of cource.
However, I feel wrong. The code with additem just run once, actually. It is
not an dynamic list, just 3 or 4 alternatives. Shouldn't I just define the
list once and for all somehow?

/Regards

 

[Chip Pearson]

I don't quite follow what you're doing. You can fill a combobox
in two ways, 1) using AddItem, or, 2) defining a ListFillRange, a
range from which the list will take its contents.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Steven]

You can add a worksheet into your workbook with all the combo box
options and populate it using variables.

For example, this piece of code populates all the week numbers in my
week number combo box with the values I've placed in column A. And my
reporting unit combo box with the values from column G.

Does this help?



Private Sub UserForm_Initialize()


Dim WeekList As String, icount As Integer, ReportList As String

icount = 2

While Worksheets("Variables").Range("A" & icount).Value <> ""

WeekList = Worksheets("Variables").Range("A" & icount).Value

cboWeek.AddItem WeekList

icount = icount + 1

Wend

icount = 2


While Worksheets("Variables").Range("G" & icount).Value <> ""

ReportList = Worksheets("Variables").Range("G" & icount).Value

cboReporting.AddItem ReportList

icount = icount + 1

Wend

End Sub