Run Code To Fill Cells On Drop-down Change

Mentor · Oct 15th 2007

Hi:

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!!)

Code

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

Display More

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.

Kind regards,
Mentor:)

p45cal · Oct 15th 2007

Re: "auto-completion' Of Data Cells

By "the auto-fill function without" I'll assume you mean your code as opposed to Excel's autofill method.

What kind of combo-box is it? One from the Forms toolbar, or one from the Control Toolbox toolbar? Neither of which appears to trigger the worksheet change event when changed and linked to a cell on the sheet.

Won't Data|Validation with a list not do? This does trigger the Worksheet change event.

Otherwise, if it's a combobox from the Control Toolbox toolbar then it has its own change event into which you should place your code.

p45cal

Mentor · Oct 16th 2007

Re: Run Code To Fill Cells On Drop-down Change

Hi P45cal:

Thank you for your reply.

I didn't think of a standard validation list - I'll try it thank you. I'll also try out the combobox change and see how that works, although I have to confess that I am not a great fan on the combobox at this point because the only way to deactivate it or move to the next cell or whatever in a worksheet is via mouse-click. Is that true or can you change that too?

Have a great day!

Kind regards,

Mentor :smile:

Run Code To Fill Cells On Drop-down Change

Participate now!

Share