Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 -- 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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password for excel sheet | Excel Discussion (Misc queries) | |||
Starting MS Excel with a worksheet other than SHEET 1 | Excel Worksheet Functions | |||
Creating GIFs in Excel 2003 for use in Dreamweaver | Charts and Charting in Excel | |||
How do I duplicate a sheet 20 times in an excel spreadsheet | Excel Worksheet Functions | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel |