Correct Placement of MsgBox for Item which does not exits in ComboBox

[NimishK]

Hello Just require correct placement of MSGBOX in the following code if item does not exists in combobox

Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
       If Not IsArrow Then
       .List = Worksheets("Sheet1").Range("A2").CurrentRegion.Offset(1).Value
       
        If .ListIndex = -1 And Len(.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
             Next i
            .DropDown
        End If


    End If
    End With
End Sub

Regards
NimishK

 

[pike]

Hello,
You could use listcount

Option Explicit
Private IsArrow As Boolean
Private Sub ComboBox1_Change()
    Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Worksheets("Sheet1").Range("A2").CurrentRegion.Offset(,1).Value
        If .ListIndex = -1 And Len(.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
            Next i
            .DropDown
        End If
        If Me.ComboBox1.ListCount = 0 Then MsgBox "listcount is zero"
        End With
    End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
    If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Worksheets("Sheet1").Range("A2").CurrentRegion.Offset(,1).Value
End Sub

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Worksheets("Sheet1").Range("A2").CurrentRegion.Offset(,1).Value
End Sub

 

[NimishK]

Hi Pike
Thank you so much for your immediate solution.

My last question on this How would One Get Simulataneous Result by ComboBox_Click() Event and Combobox_Change Event()
As observed Change event does not allow Click event to Fire. No reuslt.

Private Sub ComboBox1_Click()
Dim idx As Long
idx = ComboBox1.ListIndex
       curRow = ComboBox1.ListIndex + StartRow
       If curRow < StartRow Then curRow = StartRow

 If idx <> -1 Then
        TextBox1.Value = Worksheets("Sheet1").Range("B" & curRow).Value
    End If
End Sub

for Click Event I need to fully REM the code of Combbox_Change Event as to fucntion smoothly with Combobox_Click

Any ideas How Could i acheive this working with both the events?

Regards
NimishK

 

[pike]

Hi NimishK,
It will fire and they will work together but not in the way I think you want.
The list index will never match with the filtered combobox index to allow you to find the current row, specially if there are duplicates.
If there are no duplicates you could use find

 

[NimishK]

Pike I really understand what you are saying. My problem is to have user friendly Combobox. Have been struggling for this for quite a long time. just on simple combobox
.

It will fire and they will work together but not in the way I think you want.

I am intreseted in which ever way you can come up with the logic.

The list index will never match with the filtered combobox index to allow you to find the current row, specially if there are duplicates.
If there are no duplicates you could use find

Dear My sheet will have datas of Duplicates, Triplicates , Qudraplicates and so on. How can anyone miss on this. To Diffrentiate will add a Sr No column before the range of column which has duplicate, triplicalte and qudraplicate data text

 

[pike]

Hello,
Maybe do not combine the two combobox events for the one control but have a separate search textbox to find the result

 

[NimishK]

Maybe do not combine the two combobox events for the one control but have a separate search textbox to find the result

What is the use of then having a combobox if Events can't function for the results we seek. Though in VBE you get the two events
Its a surprise that MS also cant think on this though they upgrade their versions

A textbox which needs to be hidden(may be) which i think will do the calling and dereive the result.

 

[NimishK]

Private Sub cmbProdCode_Change()

listStdOptAcc.Enabled = True

txtCopyofProdCode.Text = vbNullString
txtCopyofProdCode.Text = cmbProdCode.Text

Dim IdWs As Worksheet
Set IdWs = Sheets("PL1")

Dim myRanges As Range
Set myRanges = Sheets("PL1").Range("a2:p251")   


Dim idx As Long
      idx = cmbProdCode.ListIndex


          If idx <> -1 Then
              cmbErpCode.Text = Worksheets("PL1").Range("A" & idx + 2).Value
              cmbProdCode.Text = Worksheets("PL1").Range("B" & idx + 2).Value
              txtLongDescp.Text = Worksheets("PL1").Range("C" & idx + 2).Value
              txtRate.Text = Worksheets("PL1").Range("I" & idx + 2).Value
              
           
          End If


Call txtCopyofProdCode_Change   '[B] May be call the text box change routine here[/B] 
End Sub

Are you mentioning something similar to above. The above code is extremely different than in post 3.
Should not mind if combo change and click event give the same result

 

[pike]

hard to say with limit code snippets and understanding of the complete project

 

[NimishK]

For me its begining as asked the question

How would One Get Simulataneous Result by ComboBox_Click() Event and Combobox_Change Event()
As observed Change event does not allow Click event to Fire. No reuslt.

If you could show something on similar grounds with your simplest example as per your post 2 will appreciate.
As i have not succeeded in this at all and have been banging my head since the problem of ComboClick and Combo change event thinking it was impossible.