I am busy with a project using VBA to populate a spreadsheet based upon the data entered into a particular cell. The problem that I am experiencing is that I populate the initial cell via a combo-box which I cannot exit unless I click elsewhere with my mouse.
The code that I am using to populate the other cells is as follows (please don't laugh!!)
Private Sub Worksheet_Change(ByVal target As Range) Application.ScreenUpdating = False On Error GoTo dump Dim r As Range, MyRowNum As Long, ws1 As Worksheet, ws2 As Worksheet, strPriCode As Variant, _ strNAPPIE As Long, strSInCost As Currency, strSInDesc As String Set ws1 = Worksheets("SINVOICE") Set ws2 = Worksheets("STOCKIMP") Set r = ws1.Range("B11:B35") If Intersect(target, r) Is Nothing Then Exit Sub If target > 0 Then strPriCode = target.Value Sheets("STOCKIMP").Select With ws2.Range("STOCK") MyRowNum = .Cells.Find(What:=strPriCode, after:=.Cells(1, 1), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row ws2.Rows(MyRowNum).EntireRow.Select strNAPPIE = ws2.Cells(MyRowNum, 2).Value strSInDesc = ws2.Cells(MyRowNum, 3).Value strSInCost = ws2.Cells(MyRowNum, 6).Value End With Sheets("SINVOICE").Select With target(1, 8) If Not target(1, 8) = strSInCost Then .Value = strSInCost End If End With With target(1, 2) .Value = strNAPPIE End With With target(1, 3) .Value = strSInDesc End With End If dump: Application.ScreenUpdating = True End Sub
I would really be grateful if someone could let me know how to either trigger the auto-fill function without needing to press Enter or perhaps let me know how to change the properties of the Combo-Box so that it is possible to Tab or Enter out of it. Or perhaps I should build something else and not use a combo-box at all?
Please could you let me know, thank you.