Creation of a data entry form on Excel

[AlexSH09]

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:

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

 

[Hui]

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

 

[AlexSH09]

Hi!

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

Thanks

 

[Marc L]

Hi !

As a starter :​

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