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

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" wrote in message
...
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

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" skrev i meddelandet
...
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" wrote in message
...
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

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


"Marie J-son" wrote in message
...
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" skrev i meddelandet
...
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" wrote in message
...
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

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