1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creation of a data entry form on Excel

Discussion in 'VBA Macros' started by AlexSH09, Mar 13, 2018.

  1. AlexSH09

    AlexSH09 New Member

    Messages:
    4
    Dear all,

    I have created a form on Excel which should make possible for some users to mass-upload data in another system.
    I looked for few days for a solution to get multiple searchable drop lists in the same file and eventually succeed to find a solution.
    The 3 columns I put these searchable drop lists are E, F, G in the "InputView" tab.
    These columns are connected to 3 databases in the tab called "List" (one database for each column, even if it is same information i didn't fin any other solution)

    The problem is that my file is quite slow because each time a user click on the cells the system needs to calculate again.
    Indeed, the whole trick of this file is in 3 cells in "InputView" tab: K1, L1, M1
    Their values change depending on the active cell I am clicking on as defined in this VBA code:

    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 5 And (Target.Row > 9 And Target.Row < 101) Then
        Sheet2.[K1] = ActiveCell.Row
        End If
        If Target.Column = 6 And (Target.Row > 9 And Target.Row < 101) Then
        Sheet2.[L1] = ActiveCell.Row
        End If
        If Target.Column = 7 And (Target.Row > 9 And Target.Row < 101) Then
        Sheet2.[M1] = ActiveCell.Row
        End If
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Column = 5 And (Target.Row > 9 And Target.Row < 101) Then
        Sheet2.[K1] = ActiveCell.Row
        End If
        If Target.Column = 6 And (Target.Row > 9 And Target.Row < 101) Then
        Sheet2.[L1] = ActiveCell.Row
        End If
        If Target.Column = 7 And (Target.Row > 9 And Target.Row < 101) Then
        Sheet2.[M1] = ActiveCell.Row
        End If
    End Sub
    So I don't know how to simplify a bit the file to make it faster to use.

    In addition, I have been looking for some solutions to link together the DTpickers (columns A & B). Indeed I would like the "to" of each line to be linked with the "from' of the previous line in order to make the date selection easier.
    With this, instead of showing the date of today as first option, the system would show the last date selected on the previous line which would make the file more user-friendly.

    Do you have any idea on how to do this?

    Thanks a lot

    Attached Files:

    Last edited: Mar 13, 2018
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,391
    What version of Windows & Excel are you using?

    Why, because you are using a control which isn't available in newer versions of Excel.

    So I can't help you
  3. AlexSH09

    AlexSH09 New Member

    Messages:
    4
    Hi!

    Thanks for your answer, indeed I work on Excel 2010...
    Anyone else can help me?

    Thanks
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,892
    Hi !

    As a starter :​
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Row > 9 Then If Target.Row < 101 Then If Target.Column > 4 Then _
        If Target.Column < 8 Then Sheet2.Cells(Target.Column + 6).Value = ActiveCell.Row
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    AlexSH09 likes this.

Share This Page