combobox filling problem

[honoluluzulu]

hi !

i have a problem with the filling of a comobobox.
first of all a code sniplet:


Private Sub Worksheet_Activate()

ComboBox1.Clear

ComboBox1.AddItem "Fichte/Lärche"
ComboBox1.AddItem "Mischwald"
ComboBox1.AddItem "Laubwald"
ComboBox1.AddItem "Kiefer/Auwald"

ComboBox1.Text = ComboBox1.List(0)

End Sub

the problem:

if i open the excel file the combobox is empty or one entry is inside
but the others are missing.

i can't imagine where the problem is.
the code is very simple and clear...

do anybody have an idea ?

if there are any questions plz take a look at the attached file


+-------------------------------------------------------------------+
|Filename: Testfall_A1_Bodenwert.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4359 |
+-------------------------------------------------------------------+

 

[Guest]

Works OK for me (XL2003). And yours worked if you ativate the sheet by
sitching to sheet2 and then sheet1.

 

[honoluluzulu]

this is true, but is there a possibility to fill the combobox by opening
the file ?!
so i have to switch to sheet 2 and return to sheet 1 - this is not a
good solution....

 

[Guest]

put code in "Thisworkbook" so combobox is filled when workbook is opened



Sub workbook_open()
With Worksheets("sheet1")
..ComboBox1.Clear

..ComboBox1.AddItem "Fichte/Lärche"
..ComboBox1.AddItem "Mischwald"
..ComboBox1.AddItem "Laubwald"
..ComboBox1.AddItem "Kiefer/Auwald"
End With
End Sub

 

[Guest]

See my second reply.

this is true, but is there a possibility to fill the combobox by opening
the file ?!
so i have to switch to sheet 2 and return to sheet 1 - this is not a
good solution....

 

[honoluluzulu]

i already had this idea but if you try Combobox1 is unknown at this
point
i get an error "subscript out of range" by compiling this code at
workbook open action

Private Sub Workbook_Open()


With Worksheets("Sheet1")
ComboBox1.Clear

ComboBox1.AddItem "Fichte/Lärche"
ComboBox1.AddItem "Mischwald"
ComboBox1.AddItem "Laubwald"
ComboBox1.AddItem "Kiefer/Auwald"

End With

End Sub

 

[honoluluzulu]

sorry my fault ;-)
i changed the source like this

Private Sub Workbook_Open()



Sheet1.ComboBox1.Clear

Sheet1.ComboBox1.AddItem "Fichte/Lärche"
Sheet1.ComboBox1.AddItem "Mischwald"
Sheet1.ComboBox1.AddItem "Laubwald"
Sheet1.ComboBox1.AddItem "Kiefer/Auwald"



End Sub

and here we go.. it works

give thanks for support !