Solved: Increase Listindex value of comboobx after saving

[sujittalukde]

I am using the following code to save data from a user form. It contains a combo box which contains the name of the employees.
On form activation, I have set the listindex value at 0.
Now I want that after saving an entry, the listindex should increase by "i" value ie if the present Listindex value is 1 then after the save buuton is clicked, the list index should automatically increment to Listindex 2 and so on.
This is the code I am using:

Private Sub CommandButton1_Click()
Dim Found As Boolean
Checkname = ComboBox1.Value
    Found = False
    i = 2
           Do
           EmployeeName = Trim(ThisWorkbook.Sheets("Sheet1").Range("A" & i).Value)
            If EmployeeName = Checkname Then
                ThisWorkbook.Sheets("Sheet1").Range("A" & i).Select
                Found = True
                ActiveCell.Offset(0, 1).Value = TextBox1.Value
                ActiveCell.Offset(0, 2).Value = TextBox2.Value
                ComboBox1.ListIndex (0 + i - 2)
                TextBox1.SetFocus
              End If
            i = i + 1
        Loop Until ((EmployeeName = "") Or (Found = True))
'ComboBox1.SetFocus
End Sub

But it is showing werror here at this line:
ComboBox1.ListIndex (0 + i - 2)
How can I fix this?

[tstav]

To increment the ListIndex value use:
ComboBox.ListIndex = ComboBox.ListIndex +1

Is that what you're asking for?

[Bob Phillips]

Why are you incrementging ListIndex? You up it and immediately process another so up it again, there seems no rationale for it.

[sujittalukde]

I am attaching a sample workbook. The combobox source is the name range NEmp.
When the save button will be clicked I want that the combobox value should auto increase to next name so the user can start data entry to text boxes.
NB: Please note that the save button doest not save data for the first name. In case you select 2nd or other name then save button saves the data to sheet. If posisble plese fix this problem also.

[Bob Phillips]

[vba]

Private Sub ComboBox1_Change()
Dim Pos As Long

With ThisWorkbook.Sheets("Sheet1")
Pos = Application.Match(ComboBox1.Value, .Columns(1), 0)
TextBox1.Value = .Cells(Pos, 2).Value
TextBox2.Value = .Cells(Pos, 3).Value
End With
End Sub

Private Sub CommandButton1_Click()
Dim Found As Boolean
Checkname = ComboBox1.Value
Found = False
i = 1
Do
EmployeeName = Trim(ThisWorkbook.Sheets("Sheet1").Range("A" & i).Value)
If EmployeeName = Checkname Then
ThisWorkbook.Sheets("Sheet1").Range("A" & i).Select
Found = True
ActiveCell.Offset(0, 1).Value = TextBox1.Value
ActiveCell.Offset(0, 2).Value = TextBox2.Value
ComboBox1.ListIndex = i - 1
End If
i = i + 1
Loop Until ((EmployeeName = "") Or (Found = True))
'ComboBox1.SetFocus

End Sub

Private Sub UserForm_Activate()
Label1.Caption = ThisWorkbook.Sheets(1).Range("A1").Value
Label2.Caption = ThisWorkbook.Sheets(1).Range("B1").Value
Label3.Caption = ThisWorkbook.Sheets(1).Range("C1").Value
ComboBox1.ListIndex = 0
ComboBox1.SetFocus
End Sub
[/vba]

You have identified the problem with item 1 yourself, when you trim the worksheet value. It has a trailing space, so the form combobox is loaded with that spoace but uou trim it when comparing. Either remove it from the worksheet, or don't Trim in the code.

[sujittalukde]

Thanks xld, its working well. Also after removing the Trim word, it is working well.

[tstav]

You are assigning a fixed range A2:A446 to the ComboBox. The DropDown list will contain empty rows if this range is not filled with Names.
In case you want the ComboBox list to contain only the names you have entered in Column(1), you may use the following:

[vba]Private Sub UserForm_Activate()
Dim R As Range 'The Range of the Names
Dim LastCell As Range 'The cell of the last Name in column 1
Label1.Caption = ThisWorkbook.Sheets(1).Range("A1").Value
Label2.Caption = ThisWorkbook.Sheets(1).Range("B1").Value
Label3.Caption = ThisWorkbook.Sheets(1).Range("C1").Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Find the Range of Names and assign it to the ComboBox List
'This way your ComboBox will show only the names
'without the extra blanks of your range A2:A446
'Go to the ComboBox Properties window and delete the RowSource =nemp
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Sheet1
Set LastCell = Columns(1).Find( _
What:="*", _
After:=[A1], _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Not LastCell Is Nothing Then
LastRow = LastCell.Row
Else
MsgBox "No names found", , "Message"
Exit Sub
End If
Set R = .Range(.Cells(2, 1), .Cells(LastRow, 1))
End With
'Give a name to the Range of names
R.Name = "Names"
'Assign the Range to the ComboBox
Me.ComboBox1.RowSource = "Sheet1!Names"
ComboBox1.ListIndex = 0
ComboBox1.SetFocus
End Sub
[/vba]

[sujittalukde]

Thanks tstav for the alternative and good solution.

[sujittalukde]

Dear xld/tstav, While working on the code I have found one error. If the combo box contains the last name and then CommandButton1 is pressed, the data are saved to cells and then an error comes in at this point:
ComboBox1.ListIndex = i - 1
How the same can be fixed?

[Bob Phillips]

I am not sure what the problem is here, but my guess is that it will be linked to all of those cells that are not empty, but have spaces in them.

[sujittalukde]

The error is:
Run time error 380, Couldnot set the Listindex property. Invalid Property value.

[Bob Phillips]

No not the error, but what you are doing to cause it. You said ... If the combo box contains the last name and then CommandButton1 is pressed ... and I have no idea what that mean s.

[sujittalukde]

Sorry if I couldnot clarify the matter. For simplification, I am uploading a sample filw wherein the same problem is coming.
After dounloading, if you click "Show Me" button a user form will pop up and then start pressing the commandbutton1.
When the combobox will reach to last name as in column A, then the error will come.
Hope this could give some clarification.

[tstav]

Hope this could give some clarification.

Hi sujittalukde,
I saw your file and your code. I hope this does what you need (I think it would be better if you showed in the combo box the same name that is selected on the sheet and not the one next to it).
[VBA]Private Sub UserForm_Activate()
Dim R As Range 'The Range of the Names
Dim LastCell As Range 'The cell of the last Name in column 1
Label1.Caption = ThisWorkbook.Sheets(1).Range("A1").Value
Label2.Caption = ThisWorkbook.Sheets(1).Range("B1").Value
Label3.Caption = ThisWorkbook.Sheets(1).Range("C1").Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Find the Range of Names and assign it to the ComboBox List
'This way your ComboBox will show only the names
'without the extra blanks of your range A2:A446
'Go to the ComboBox Properties window and delete the RowSource =nemp
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Sheet1
Set LastCell = Columns(1).Find( _
What:="*", _
After:=[A1], _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Not LastCell Is Nothing Then
LastRow = LastCell.Row
Else
MsgBox "No names found", , "Message"
Exit Sub
End If
Set R = .Range(.Cells(2, 1), .Cells(LastRow, 1))
End With
'Give a name to the Range of names
R.Name = "Names"
'Assign the Range to the ComboBox
Me.ComboBox1.RowSource = "Names"
ComboBox1.ListIndex = 0
Sheet1.Range("A2").Select
ComboBox1.SetFocus
End Sub
[/VBA]
[VBA]Private Sub ComboBox1_Change()
Dim Pos As Long
With ThisWorkbook.Sheets("zz cc")
Pos = Application.Match(ComboBox1.Value, .Columns(1), 0)
.Cells(Pos, 1).Select
TextBox1.Value = .Cells(Pos, 2).Value
TextBox2.Value = .Cells(Pos, 3).Value
End With
End Sub[/VBA]
[VBA]Private Sub CommandButton1_Click()
With ComboBox1
If .ListIndex < .ListCount Then
ActiveCell.Offset(0, 1).Value = TextBox1.Value
ActiveCell.Offset(0, 2).Value = TextBox2.Value
If .ListIndex <> .ListCount - 1 Then
.ListIndex = .ListIndex + 1
End If
End If
End With
End Sub[/VBA]

[sujittalukde]

Thanks tstav for the solution. In the mean time , yesterday night, after repeated trials I finally solved the error, just added this lines:

If Range("A" & i + 1) = "" Then
ComboBox1.ListIndex = 0
Else
ComboBox1.ListIndex = i - 1
End If

for this code:

Private Sub CommandButton1_Click() 
Dim Found As Boolean 
Checkname = ComboBox1.Value 
Found = False 
i = 1 
Do 
EmployeeName = Trim(ThisWorkbook.Sheets("Sheet1").Range("A" & i).Value) 
If EmployeeName = Checkname Then 
ThisWorkbook.Sheets("Sheet1").Range("A" & i).Select 
Found = True 
ActiveCell.Offset(0, 1).Value = TextBox1.Value 
ActiveCell.Offset(0, 2).Value = TextBox2.Value 
ComboBox1.ListIndex = i - 1 
End If 
i = i + 1 
Loop Until ((EmployeeName = "") Or (Found = True)) 
'ComboBox1.SetFocus
 
End Sub