Don't add item in combobox ...

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> skrev i meddelandet
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> skrev i meddelandet
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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