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?

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?

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?

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?

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?

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?