creating a combobox dynamically on an excel sheet

**gupt** · 06-06-2005, 03:34 AM

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

Peter Jausovec · 06-06-2005, 07:05 AM

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
--
http://blog.jausovec.net

"gupt" je napisal:

>
> 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
>
>
> --
> gupt
> ------------------------------------------------------------------------
> gupt's Profile: http://www.excelforum.com/member.php...o&userid=24067
> View this thread: http://www.excelforum.com/showthread...hreadid=376700
>
>

**MartinShort** · 06-06-2005, 07:37 AM

I've made a slight enhancement to Peter's code which will hide the combo unless you've selected a cell in the column11.

Please Login or Register  to view this content.

Peter Jausovec · 06-06-2005, 09:05 AM

Hi,

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

--
http://blog.jausovec.net

"MartinShort" wrote:

>
> I've made a slight enhancement to Peter's code which will hide the combo
> 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 Sub
> --------------------
>
>
> --
> MartinShort
>
> Software Tester
> ------------------------------------------------------------------------
> MartinShort's Profile: http://www.excelforum.com/member.php...o&userid=22034
> View this thread: http://www.excelforum.com/showthread...hreadid=376700
>
>

**MartinShort** · 06-06-2005, 11:16 AM

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...

Peter Jausovec · 06-06-2005, 01:05 PM

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

--
http://blog.jausovec.net

"MartinShort" wrote:

>
> 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...
>
>
> --
> MartinShort
>
> Software Tester
> ------------------------------------------------------------------------
> MartinShort's Profile: http://www.excelforum.com/member.php...o&userid=22034
> View this thread: http://www.excelforum.com/showthread...hreadid=376700
>
>

**MartinShort** · 06-06-2005, 02:22 PM

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** · 06-06-2005, 09:28 PM

hey,

thanks a lot guys! it works!

you solved my problem!

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

thanks again!!
gupt

Debra Dalgleish · 06-07-2005, 12:05 AM

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

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

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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html