The code below list all the sheets name of active workbook in drop
down list. Because of the code line ".CLEAR" I am not able to get
combobox value. I want that when I select sheet name in drop down
list that name should appear as combobox text or value. and also how
can i stop some one to write in combobox as i want if some one write
in combobox the msgbox should appear saying "Select from drop down
list". Please can any friend solve my these queries about combobox.
Thanks

Private Sub ComboBox1_DropButtonClick()
Dim Sh As Worksheet
With ComboBox1
.CLEAR
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "" Then
.AddItem Sh.Name

End If

Next
End With

End Sub

To block direct entry into the combo box just change its Style to
fmStyleDropDownList.

RE holding its value you might see how this works:

Dim ItemIndex As Integer

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = 0 Then
ItemIndex = ComboBox1.ListIndex
End If
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim Sh As Worksheet
With ComboBox1
.Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "" Then
.AddItem Sh.Name
End If
Next
.ListIndex = ItemIndex
End With
End Sub


--
Jim
"K" wrote in message
...
| The code below list all the sheets name of active workbook in drop
| down list. Because of the code line ".CLEAR" I am not able to get
| combobox value. I want that when I select sheet name in drop down
| list that name should appear as combobox text or value. and also how
| can i stop some one to write in combobox as i want if some one write
| in combobox the msgbox should appear saying "Select from drop down
| list". Please can any friend solve my these queries about combobox.
| Thanks
|
| Private Sub ComboBox1_DropButtonClick()
| Dim Sh As Worksheet
| With ComboBox1
| .CLEAR
| For Each Sh In ActiveWorkbook.Sheets
| If Sh.Name < "" Then
| .AddItem Sh.Name
|
| End If
|
| Next
| End With
|
| End Sub

On Sep 18, 1:03*pm, "Jim Rech" wrote:
To block direct entry into the combo box just change its Style to
fmStyleDropDownList.

RE holding its value you might see how this works:

Dim ItemIndex As Integer

Private Sub ComboBox1_Change()
* * If ComboBox1.ListIndex = 0 Then
* * * * ItemIndex = ComboBox1.ListIndex
* * End If
End Sub

Private Sub ComboBox1_DropButtonClick()
* * Dim Sh As Worksheet
* * With ComboBox1
* * * * .Clear
* * * * * For Each Sh In ActiveWorkbook.Sheets
* * * * * * * If Sh.Name < "" Then
* * * * * * * * .AddItem Sh.Name
* * * * * * * End If
* * * * * Next
* * * * * .ListIndex = ItemIndex
* * End With
End Sub

--
Jim"K" wrote in message

...
| The code below list all the sheets name of active workbook in drop
| down list. *Because of the code line ".CLEAR" I am not able to get
| combobox value. *I want that when I select sheet name in drop down
| list that name should appear as combobox text or value. *and also how
| can i stop some one to write in combobox as i want if some one write
| in combobox the msgbox should appear saying "Select from drop down
| list". *Please can any friend solve my these queries about combobox.
| Thanks
|
| Private Sub ComboBox1_DropButtonClick()
| Dim Sh As Worksheet
| With ComboBox1
| * *.CLEAR
| * * *For Each Sh In ActiveWorkbook.Sheets
| * * * * *If Sh.Name < "" Then
| * * * * * *.AddItem Sh.Name
|
| * * * * *End If
|
| * * *Next
| End With
|
| End Sub

Thanks lot Jim