ComboBox problems !!

[Jako]

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").Delete
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

 

[Tom Ogilvy]

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.

 

[Jako]

Thanks for the help Tom but i'm not sure where you mean to put the code
you mentioned.

I'm assuming you mean replace:

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

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

With:

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


Is that what you mean?

All controls are on a Userform.

Would it be better to Call the code from the CommandButton or have the
Code set as the Private code for the CommandButton, or doesn't it
matter.

Many thanks again.

 

[Jako]

Ooops!!

Sorry Tom i meant

Replace with:

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

as the controls are on a Userform

 

[Tom Ogilvy]

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.ListFillRange = Range( _
QUALITYREASONSFORFAILURE). _
Address(1,1,xlA1,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").Delete
Call UpdateNamedRanges

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

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


End If


Is what I mean.

 

[Jako]

Many, many thanks again Tom

 

[Jako]

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

 

[Tom Ogilvy]

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

should be

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

my typo.