creating a combobox dynamically on an excel sheet

[gupt]

hi,

i'm totally new to excel and writing macros and code for it. i need to
create a file which will allow the users to click on the 11th column on
any row and select a value from the combobox that appears in that cell
on clicking.

the combobox will only appear if it is the 11th column. i plan to
create one combobox and make it invisible when the file is loaded. it
will become visible in row the user clicks (only if the col = 11). i
want to use VBA for this. but i have no clue how to! any help will be
greatly appreciated!

thanks!!

gupt

 

[Guest]

Hi,

Add a Combobox to your sheet and add a following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.column = 11 Then
ActiveSheet.Shapes("Combobox1").Left = Target.Left
ActiveSheet.Shapes("Combobox1").Top = Target.Top
End If
End Sub

Hope this helps.
Peter

 

[MartinShort]

I've made a slight enhancement to Peter's code which will hide the comb
unless you've selected a cell in the column11
Code
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 11 Then
ComboBox1.Visible = True
ActiveSheet.Shapes("Combobox1").Left = Target.Left
ActiveSheet.Shapes("Combobox1").Top = Target.Top
Else
ComboBox1.Visible = False
End If
End Su

 

[Guest]

Hi,

Another enhancement would be to hide the combobox when the workbook opens

 

[MartinShort]

That's true, but I was assuming it was hidden when the workbook was
initially saved

I did like your TARGET functions though; it's not something I've used
in terms of object co-ords and Excel Help is strangely quiet on the
issue...

 

[Guest]

Ok; we assume combobox is hidden . But here's another neat enhancement (I
don't know how I haven't rembered it before) - combobox width and height are
set to the cell's width and height:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.column = 11 Then
ActiveSheet.Shapes("Combobox1").Visible = True
ActiveSheet.Shapes("Combobox1").Left = Target.Left
ActiveSheet.Shapes("Combobox1").Top = Target.Top
ActiveSheet.Shapes("Combobox1").Width = Target.Width
ActiveSheet.Shapes("combobox1").Height = Target.Height
Else
ActiveSheet.Shapes("Combobox1").Visible = False
End If
End Sub

 

[MartinShort]

Typical!

I was discussing this with a colleague from work just before I left and
with a bit of experimentation we "invented" the same solution! One
thing I found though is that it looks better if the DropButtonStyle is
altered to a different setting - the down arrow looks a little cramped
on a dynamically altered box size.

Thanks again - it's a great tool.

M

 

[gupt]

hey,

thanks a lot guys! it works! you solved my problem!

i heard from someone that its possible to do the same thing withou
using VBA. by using the Validate option in the Data menu and selectin
List in the options thr. i tried tht too. but i cant put the items
want to put in the list in the same sheet and it only allows the sourc
of the list to be on the same sheet. so thats a problem. is it so or i'
making a mistake somewhr? which one is a better solution?

thanks again!!
gup

 

[Debra Dalgleish]

You can use a list on a different sheet, if you name the list. There are
instructions here:

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