|
Combobox to range
Is it possible to write a name in a combobox which is populated from a named
range and get the name added to the range? |
Combobox to range
hi,
Not automatically, but you could have a small command button which would add the new entry when clicked (if it does not already exists). ' Assumes: named range is in continuous 1-column range ' & combobox is on the sheet (else use RowSource instead of ListFillRange in code) ' & named range is Sheet level name, not book level name '---------------------------------------------------------------------------------- Private Const RG_LIST As String = "MyList" 'Named range Private Sub CmdAdd_Click() Dim rg As Range, wsh As Worksheet, cbx As MSForms.ComboBox Dim addr As String, txt As String, found As Boolean '-------- CHANGE here ------------ Set wsh = ActiveSheet Set cbx = ComboBox1 '------------------------------------- Set rg = wsh.Range(RG_LIST) txt = cbx.Text 'Does it already exist found = False On Error Resume Next found = (Application.WorksheetFunction.Match(txt, rg, 0) 0) On Error GoTo 0 'add it If Not found Then 'case to add name to list Set rg = rg.Resize(rg.Rows.Count + 1, 1) 'expand rg 1 row rg.Cells(rg.Cells.Count) = txt 'assign new value to last cell addr = "'" & rg.Parent.Name & "'!" & rg.Address(True, True) rg.Parent.Names.Add Name:="'" & rg.Parent.Name & "'!" & RG_LIST, RefersTo:="=" & addr cbx.ListFillRange = RG_LIST 'force to re-link named range End If End Sub '----------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Is it possible to write a name in a combobox which is populated from a named range and get the name added to the range? |
Combobox to range
Ups. Well, I do have a CmButton click_sub, which use's the name selected, so
I'll try to add you code to that. Not quite sure how to use the "RowSource", but I'll try to work on it. "Thanks a'lot "sebastienm" skrev: hi, Not automatically, but you could have a small command button which would add the new entry when clicked (if it does not already exists). ' Assumes: named range is in continuous 1-column range ' & combobox is on the sheet (else use RowSource instead of ListFillRange in code) ' & named range is Sheet level name, not book level name. '---------------------------------------------------------------------------------- Private Const RG_LIST As String = "MyList" 'Named range Private Sub CmdAdd_Click() Dim rg As Range, wsh As Worksheet, cbx As MSForms.ComboBox Dim addr As String, txt As String, found As Boolean '-------- CHANGE here ------------ Set wsh = ActiveSheet Set cbx = ComboBox1 '------------------------------------- Set rg = wsh.Range(RG_LIST) txt = cbx.Text 'Does it already exist found = False On Error Resume Next found = (Application.WorksheetFunction.Match(txt, rg, 0) 0) On Error GoTo 0 'add it If Not found Then 'case to add name to list Set rg = rg.Resize(rg.Rows.Count + 1, 1) 'expand rg 1 row rg.Cells(rg.Cells.Count) = txt 'assign new value to last cell addr = "'" & rg.Parent.Name & "'!" & rg.Address(True, True) rg.Parent.Names.Add Name:="'" & rg.Parent.Name & "'!" & RG_LIST, RefersTo:="=" & addr cbx.ListFillRange = RG_LIST 'force to re-link named range End If End Sub '----------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Is it possible to write a name in a combobox which is populated from a named range and get the name added to the range? |
Combobox to range
The RowSource works as the ListFillRange, you have to use one or the other
depending on the location of a COmbobox: -on a Userform use: cbx.RowSource -on a sheet: use cbx.ListFillRange that's the only difference i believe. -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Ups. Well, I do have a CmButton click_sub, which use's the name selected, so I'll try to add you code to that. Not quite sure how to use the "RowSource", but I'll try to work on it. "Thanks a'lot "sebastienm" skrev: hi, Not automatically, but you could have a small command button which would add the new entry when clicked (if it does not already exists). ' Assumes: named range is in continuous 1-column range ' & combobox is on the sheet (else use RowSource instead of ListFillRange in code) ' & named range is Sheet level name, not book level name. '---------------------------------------------------------------------------------- Private Const RG_LIST As String = "MyList" 'Named range Private Sub CmdAdd_Click() Dim rg As Range, wsh As Worksheet, cbx As MSForms.ComboBox Dim addr As String, txt As String, found As Boolean '-------- CHANGE here ------------ Set wsh = ActiveSheet Set cbx = ComboBox1 '------------------------------------- Set rg = wsh.Range(RG_LIST) txt = cbx.Text 'Does it already exist found = False On Error Resume Next found = (Application.WorksheetFunction.Match(txt, rg, 0) 0) On Error GoTo 0 'add it If Not found Then 'case to add name to list Set rg = rg.Resize(rg.Rows.Count + 1, 1) 'expand rg 1 row rg.Cells(rg.Cells.Count) = txt 'assign new value to last cell addr = "'" & rg.Parent.Name & "'!" & rg.Address(True, True) rg.Parent.Names.Add Name:="'" & rg.Parent.Name & "'!" & RG_LIST, RefersTo:="=" & addr cbx.ListFillRange = RG_LIST 'force to re-link named range End If End Sub '----------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Is it possible to write a name in a combobox which is populated from a named range and get the name added to the range? |
Combobox to range
OK. Thanks again
"sebastienm" skrev: The RowSource works as the ListFillRange, you have to use one or the other depending on the location of a COmbobox: -on a Userform use: cbx.RowSource -on a sheet: use cbx.ListFillRange that's the only difference i believe. -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Ups. Well, I do have a CmButton click_sub, which use's the name selected, so I'll try to add you code to that. Not quite sure how to use the "RowSource", but I'll try to work on it. "Thanks a'lot "sebastienm" skrev: hi, Not automatically, but you could have a small command button which would add the new entry when clicked (if it does not already exists). ' Assumes: named range is in continuous 1-column range ' & combobox is on the sheet (else use RowSource instead of ListFillRange in code) ' & named range is Sheet level name, not book level name. '---------------------------------------------------------------------------------- Private Const RG_LIST As String = "MyList" 'Named range Private Sub CmdAdd_Click() Dim rg As Range, wsh As Worksheet, cbx As MSForms.ComboBox Dim addr As String, txt As String, found As Boolean '-------- CHANGE here ------------ Set wsh = ActiveSheet Set cbx = ComboBox1 '------------------------------------- Set rg = wsh.Range(RG_LIST) txt = cbx.Text 'Does it already exist found = False On Error Resume Next found = (Application.WorksheetFunction.Match(txt, rg, 0) 0) On Error GoTo 0 'add it If Not found Then 'case to add name to list Set rg = rg.Resize(rg.Rows.Count + 1, 1) 'expand rg 1 row rg.Cells(rg.Cells.Count) = txt 'assign new value to last cell addr = "'" & rg.Parent.Name & "'!" & rg.Address(True, True) rg.Parent.Names.Add Name:="'" & rg.Parent.Name & "'!" & RG_LIST, RefersTo:="=" & addr cbx.ListFillRange = RG_LIST 'force to re-link named range End If End Sub '----------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Is it possible to write a name in a combobox which is populated from a named range and get the name added to the range? |
Combobox to range
I have only used rowsource in properties, never in code
"Kjeldc" skrev: OK. Thanks again "sebastienm" skrev: The RowSource works as the ListFillRange, you have to use one or the other depending on the location of a COmbobox: -on a Userform use: cbx.RowSource -on a sheet: use cbx.ListFillRange that's the only difference i believe. -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Ups. Well, I do have a CmButton click_sub, which use's the name selected, so I'll try to add you code to that. Not quite sure how to use the "RowSource", but I'll try to work on it. "Thanks a'lot "sebastienm" skrev: hi, Not automatically, but you could have a small command button which would add the new entry when clicked (if it does not already exists). ' Assumes: named range is in continuous 1-column range ' & combobox is on the sheet (else use RowSource instead of ListFillRange in code) ' & named range is Sheet level name, not book level name. '---------------------------------------------------------------------------------- Private Const RG_LIST As String = "MyList" 'Named range Private Sub CmdAdd_Click() Dim rg As Range, wsh As Worksheet, cbx As MSForms.ComboBox Dim addr As String, txt As String, found As Boolean '-------- CHANGE here ------------ Set wsh = ActiveSheet Set cbx = ComboBox1 '------------------------------------- Set rg = wsh.Range(RG_LIST) txt = cbx.Text 'Does it already exist found = False On Error Resume Next found = (Application.WorksheetFunction.Match(txt, rg, 0) 0) On Error GoTo 0 'add it If Not found Then 'case to add name to list Set rg = rg.Resize(rg.Rows.Count + 1, 1) 'expand rg 1 row rg.Cells(rg.Cells.Count) = txt 'assign new value to last cell addr = "'" & rg.Parent.Name & "'!" & rg.Address(True, True) rg.Parent.Names.Add Name:="'" & rg.Parent.Name & "'!" & RG_LIST, RefersTo:="=" & addr cbx.ListFillRange = RG_LIST 'force to re-link named range End If End Sub '----------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Kjeldc" wrote: Is it possible to write a name in a combobox which is populated from a named range and get the name added to the range? |
| All times are GMT +1. The time now is 12:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com