ExcelBanter - ComboBox problems !!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - ComboBox problems !! (https://www.excelbanter.com/excel-programming/301880-combobox-problems.html)

ComboBox problems !!

I have a named range in column C from C1.

I have a ComboBox which has the RowSource set a
QUALITYREASONSFORFAILURE

and a textbox where new entries can be added via a commandbutton.

This is the code which is allocated to the command button:

Dim QUALITYREASONSFORFAILURE
QUALITYREASONSFORFAILURE = NewReasonTextBox.Value

If QUALITYREASONSFORFAILURE < "" Then
Application.ScreenUpdating = False
Sheets("DATA").Activate
With Range(Cells(1, "C"), Cells(Rows.Count, _
"C").End(xlUp))
Set X = .Find(QUALITYREASONSFORFAILURE, , xlValues, xlWhole)
End With
If X Is Nothing Then
Cells(Rows.Count, "C").End(xlUp). _
Offset(1, 0).Select
ActiveCell = QUALITYREASONSFORFAILURE
Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="QUALITYREASONSFORFAILURE", _
RefersTo:=Selection
Application.ScreenUpdating = False
Sheets("DATA").Activate
Range("C1").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Else
Application.ScreenUpdating = True
MsgBox QUALITYREASONSFORFAILURE & " ALREADY EXISTS IN DATABASE"
Application.ScreenUpdating = False
End If
End If
ComboBox2.Value = NewReasonTextBox.Value
NewReasonTextBox.Value = ""
'ThisWorkbook.Names("QUALITYREASONSFORFAILURE").De lete
Call UpdateNamedRanges

With Worksheets("DATA")
Range("C1").End(xlDown).Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "QUALITYREASONSFORFAILURE"
End With

End If

'
---------------------------------------------------------------------------------

End Sub

Adding new items works fine and dandy.

The problem i'm having is that the named range is not being recognise
when i go back and activate the CombBox.

For some reason the last item is never displayed in the ComboBox.
The selection in column C is selected ok (every item is selected) bu
is always missing the last item in the selection.

Please could someone suggest how i could fix this problem.

It is doing my head in trying to solve the problem.

Many thank

--
Message posted from http://www.ExcelForum.com

ComboBox problems !!

reassign the listfillrange

Application.ScreenUpdating = True
me.Combobox1.ListFillRange = Range( _
QUALITYREASONSFORFAILURE). _
Address(1,1,xlA1,True)
Else

Assumes the combobox is on a worksheet. If on a userform, change
ListFillRange to RowSource.

--
Regards,
Tom Ogilvy

"Jako " wrote in message
...
I have a named range in column C from C1.

I have a ComboBox which has the RowSource set as
QUALITYREASONSFORFAILURE

and a textbox where new entries can be added via a commandbutton.

This is the code which is allocated to the command button:

Dim QUALITYREASONSFORFAILURE
QUALITYREASONSFORFAILURE = NewReasonTextBox.Value

If QUALITYREASONSFORFAILURE < "" Then
Application.ScreenUpdating = False
Sheets("DATA").Activate
With Range(Cells(1, "C"), Cells(Rows.Count, _
"C").End(xlUp))
Set X = .Find(QUALITYREASONSFORFAILURE, , xlValues, xlWhole)
End With
If X Is Nothing Then
Cells(Rows.Count, "C").End(xlUp). _
Offset(1, 0).Select
ActiveCell = QUALITYREASONSFORFAILURE
Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="QUALITYREASONSFORFAILURE", _
RefersTo:=Selection
Application.ScreenUpdating = False
Sheets("DATA").Activate
Range("C1").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Else
Application.ScreenUpdating = True
MsgBox QUALITYREASONSFORFAILURE & " ALREADY EXISTS IN DATABASE"
Application.ScreenUpdating = False
End If
End If
ComboBox2.Value = NewReasonTextBox.Value
NewReasonTextBox.Value = ""
'ThisWorkbook.Names("QUALITYREASONSFORFAILURE").De lete
Call UpdateNamedRanges

With Worksheets("DATA")
Range("C1").End(xlDown).Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "QUALITYREASONSFORFAILURE"
End With

End If

'
--------------------------------------------------------------------------
-------

End Sub

Adding new items works fine and dandy.

The problem i'm having is that the named range is not being recognised
when i go back and activate the CombBox.

For some reason the last item is never displayed in the ComboBox.
The selection in column C is selected ok (every item is selected) but
is always missing the last item in the selection.

Please could someone suggest how i could fix this problem.

It is doing my head in trying to solve the problem.

Many thanks

---
Message posted from http://www.ExcelForum.com/

ComboBox problems !!

Many, many thanks again Tom

--
Message posted from http://www.ExcelForum.com

ComboBox problems !!

I tried the code Tom gave me but now i get the following error.

Runtime Error 1004.

Method "Range of object_global failed".

Please note this the code i am using:

Private Sub CommandButton1_Click()
Dim QUALITYREASONSFORFAILURE
QUALITYREASONSFORFAILURE = NewReasonTextBox.Value

If QUALITYREASONSFORFAILURE < "" Then
Application.ScreenUpdating = False
Sheets("DATA").Activate
With Range(Cells(1, "C"), Cells(Rows.Count, _
"C").End(xlUp))
Set X = .Find(QUALITYREASONSFORFAILURE, , xlValues, xlWhole)
End With
If X Is Nothing Then
Cells(Rows.Count, "C").End(xlUp). _
Offset(1, 0).Select
ActiveCell = QUALITYREASONSFORFAILURE
Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="QUALITYREASONSFORFAILURE", _
RefersTo:=Selection
Application.ScreenUpdating = False
Sheets("DATA").Activate
Range("C1").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Me.ComboBox1.RowSource = Range( _
QUALITYREASONSFORFAILURE). _
Address(1, 1, xlA1, True)
Else
Application.ScreenUpdating = True
MsgBox QUALITYREASONSFORFAILURE & " ALREADY EXISTS IN DATABASE"
Application.ScreenUpdating = False
End If
End If
ComboBox1.Value = NewReasonTextBox.Value
NewReasonTextBox.Value = ""

End sub

Thank

--
Message posted from http://www.ExcelForum.com

ComboBox problems !!

Me.ComboBox1.RowSource = Range( _
QUALITYREASONSFORFAILURE). _
Address(1, 1, xlA1, True)

should be

Me.ComboBox1.RowSource = Range( _
"QUALITYREASONSFORFAILURE"). _
Address(1, 1, xlA1, True)

my typo.

--
Regards,
Tom Ogilvy

"Jako " wrote in message
...
I tried the code Tom gave me but now i get the following error.

Runtime Error 1004.

Method "Range of object_global failed".

Please note this the code i am using:

Private Sub CommandButton1_Click()
Dim QUALITYREASONSFORFAILURE
QUALITYREASONSFORFAILURE = NewReasonTextBox.Value

If QUALITYREASONSFORFAILURE < "" Then
Application.ScreenUpdating = False
Sheets("DATA").Activate
With Range(Cells(1, "C"), Cells(Rows.Count, _
"C").End(xlUp))
Set X = .Find(QUALITYREASONSFORFAILURE, , xlValues, xlWhole)
End With
If X Is Nothing Then
Cells(Rows.Count, "C").End(xlUp). _
Offset(1, 0).Select
ActiveCell = QUALITYREASONSFORFAILURE
Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="QUALITYREASONSFORFAILURE", _
RefersTo:=Selection
Application.ScreenUpdating = False
Sheets("DATA").Activate
Range("C1").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Me.ComboBox1.RowSource = Range( _
QUALITYREASONSFORFAILURE). _
Address(1, 1, xlA1, True)
Else
Application.ScreenUpdating = True
MsgBox QUALITYREASONSFORFAILURE & " ALREADY EXISTS IN DATABASE"
Application.ScreenUpdating = False
End If
End If
ComboBox1.Value = NewReasonTextBox.Value
NewReasonTextBox.Value = ""

End sub

Thanks

---
Message posted from http://www.ExcelForum.com/