i want to add a combobox in a cell, and when i drag it down, want that it
applies to the next cell as well, like what normally happens when we drag
down a formula...
I saw in someone's excel sheet that this combobox appeared only when we
click that particular cell, otherwise , the cell appears to be without any
combobox. and when we drag it down, the combobox and its contents appear in
the next cell as well.

how can i implement that? please reply...

Use Data/Datavalidation/List
Set a list by typing vlaues seperated by coma or give a range of cells where
you can pur no of values that will be diplayed in the list.
the cell will not accept values other than in the combolist


"Alok" wrote in message
...
i want to add a combobox in a cell, and when i drag it down, want that it
applies to the next cell as well, like what normally happens when we drag
down a formula...
I saw in someone's excel sheet that this combobox appeared only when we
click that particular cell, otherwise , the cell appears to be without any
combobox. and when we drag it down, the combobox and its contents appear
in
the next cell as well.

how can i implement that? please reply...

Hi Alok,

You could perhaps use the Data Validation feature with the list option.

If you are not familiar with Data validation, see Debra Dalgleish's tutorial
at:

http://www.contextures.com/xlDataVal01.html

If this is not appropriate to your requirements, and you want a combobox,
try something like:

'================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCombo As OLEObject
Dim rng As Range
Const sListAddress As String = "D1:D26" '<<==== CHANGE

If Target.Cells.Count 1 Then Exit Sub

Set rng = Range("A1:A10") '<<==== CHANGE

On Error Resume Next
Set MyCombo = Me.OLEObjects("MyCombo")
On Error GoTo 0

If Not Intersect(rng, Target) Is Nothing Then
If MyCombo Is Nothing Then
Set MyCombo = Me.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, _
displayAsIcon:=False)
MyCombo.Name = "MyCombo"
End If

With MyCombo
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = sListAddress
.LinkedCell = Target.Address
End With
Else
If Not MyCombo Is Nothing Then MyCombo.Visible = False
End If
End Sub
'<<================

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

Change the value of sListAddress to accord with the address of the combobox
list.

Change the address of the rng variable to accord with the desired range for
the combobox. The combox will appear in whichever cell is selected in the
range assigned to the rng variable.


---
Regards,
Norman



"Alok" wrote in message
...
i want to add a combobox in a cell, and when i drag it down, want that it
applies to the next cell as well, like what normally happens when we drag
down a formula...
I saw in someone's excel sheet that this combobox appeared only when we
click that particular cell, otherwise , the cell appears to be without any
combobox. and when we drag it down, the combobox and its contents appear
in
the next cell as well.

how can i implement that? please reply...