Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox problems !!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Having problems with adding input from combobox | Excel Programming | |||
Combobox Visibility Problems | Excel Programming | |||
ComboBox Problems | Excel Programming | |||
Combobox control problems in a grouped cell | Excel Programming |