creating a combobox dynamically on an excel sheet
 

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

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

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

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

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

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

Typical! :cool:

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


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

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


--
gupt
------------------------------------------------------------------------
gupt's Profile: http://www.excelforum.com/member.php...o&userid=24067
View this thread: http://www.excelforum.com/showthread...hreadid=376700

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

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