Loading sheet names in Combobox
 

Hello There,

I created a active X -ComboBox (from Control Toolbox) and would like to
load it with the sheetnames in my workbook.

This code I have as worksheet code: Credits to Tom O.

Private Sub ComboBox1_Click()
Dim Sh As Worksheet
Dim sVal As String
With ComboBox1
sVal = .Text
Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "Inputs" Then
AddItem Sh.Name
End If
Next
.Value = sVal
End With
End Sub

1. =Knowing Tom... I do not dare to say that the Combo does not get
loaded ???? !
2. If we can manage to load the Combobox, I would like to write the
selection away to cell: eg. B5.

Any Help really appreciated,

Sige

Loading sheet names in Combobox
 

Your taking the answer out of context. Every piece of code I write isn't a
general solution - nor is the code necessarily original - such as in this
case, where I was helping someone with code they had written. If you want
to load a combobox with the names of the sheets, I wouldn't use the click
event of the control itself. I can't say what event to use, because I don't
know the circumstances of the requirement, but in general


Dim Sh As Worksheet
With ActiveSheet.ComboBox1
For Each Sh In ActiveWorkbook.WorkSheets
.AddItem Sh.Name
Next
End With


will put the names of the sheets in combobox1 on the activesheet. loading a
combobox and making a selection in the same event is inconsistent. to write
the value, you can use the click event

Private Sub Combobox1_click()
me.Range("B5").Value = me.Combobox1.Text
End Sub

or you could just link the combobox to the cell.


--
Regards,
Tom Ogilvy


"Sige" wrote in message
oups.com...
Hello There,

I created a active X -ComboBox (from Control Toolbox) and would like to
load it with the sheetnames in my workbook.

This code I have as worksheet code: Credits to Tom O.

Private Sub ComboBox1_Click()
Dim Sh As Worksheet
Dim sVal As String
With ComboBox1
sVal = .Text
Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "Inputs" Then
AddItem Sh.Name
End If
Next
.Value = sVal
End With
End Sub

1. =Knowing Tom... I do not dare to say that the Combo does not get
loaded ???? !
2. If we can manage to load the Combobox, I would like to write the
selection away to cell: eg. B5.

Any Help really appreciated,

Sige

Loading sheet names in Combobox
 

"Sige" wrote in message
oups.com...
Hello There,

I created a active X -ComboBox (from Control Toolbox) and would like to
load it with the sheetnames in my workbook.

This code I have as worksheet code: Credits to Tom O.

Private Sub ComboBox1_Click()
Dim Sh As Worksheet
Dim sVal As String
With ComboBox1
sVal = .Text
Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "Inputs" Then
AddItem Sh.Name
End If
Next
.Value = sVal
End With
End Sub

1. =Knowing Tom... I do not dare to say that the Combo does not get
loaded ???? !

Try this, it loads by clicking the dropdown arrow

Private Sub ComboBox1_DropButtonClick()
Dim Sh As Worksheet
Dim sVal As String
With ComboBox1
sVal = .Text
.Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "Inputs" Then
.AddItem Sh.Name
End If
Next
.Value = sVal
End With
End Sub

2. If we can manage to load the Combobox, I would like to write the
selection away to cell: eg. B5.

Just set the Combo's LinkedCell property to B5.

Loading sheet names in Combobox
 

Thank you Tom,

It was exactly what I needed!

:o) Sige

"NOSPAM" to be removed for direct mailing...

*** Sent via Developersdex http://www.developersdex.com ***

Loading sheet names in Combobox
 

Thx a lot BOB,

Like this the loading behaviour is updated every time!
Sige

Loading sheet names in Combobox
 

It is, which caters for new sheets being added very simply.

--
HTH

Bob Phillips

"Sige" wrote in message
ups.com...
Thx a lot BOB,

Like this the loading behaviour is updated every time!
Sige