VBA User Form Recognition?

[MFish]

Hi there,

I'm very new to the VBA side of Excel. I understand Excel Spreadsheets but not much on the VBA side. I had a question about excel understanding what is selected in a User Form while you start marking things off. Here's my goal, may get confusing...

I have locations in my company that are indicated by a 3 letter acronym. Example: Visalia = VIS, Fresno = FAT, Sacramento = SMF, Etc...
I have made a match/index/if formula in the cells (With data sheets) that understand if someone marks VIS in A1 and FAT in A2, it will calculate "x" amount of time to travel on the road in A3 and other information in other cells.
The issue I run into, is that my spreadsheet is completely static. It is stuck with 10 trucks/runs and it looks bad, visually, when only 9 trucks run and a run is missing on several rows (I protect my sheets as my employees around me delete crap they're not supposed to).
I'd like to make a command button that shows a form where they can plug information in and will add onto the next available line underneath, neatly. If only 9 runs are run, then it only record 9 runs. But, my main goal is, if they plug in VIS to FAT, will the user form understand the same principle of time/distance between the two? Is it capable of showing you on the spot, before hitting confirm, what that distance/time may be? How do you write a code that best describes this?


SCH = Scheduled
Original formula:
=IFERROR(IF(OR(D3="28"),INDEX(Data!$CF$4:$DO$39,MATCH(E3,Data!$CF$3:$DO$3,0),MATCH(F3,Data!$CE$4:$CE$39,0)),
IF(OR(D3="28T",D3="53"),INDEX(Data!$AR$4:$CA$39,MATCH(E3,Data!$AR$3:$CA$3,0),MATCH(F3,Data!$AQ$4:$AQ$39,0)),
IF(OR(D3="24",D3="26"),INDEX(Data!$DU$4:$FD$39,MATCH(E3,Data!$DU$3:$FD$3,0),MATCH(F3,Data!$DT$4:$DT$39,0)),
IF(D3="","",""))))+J3,"")


Hopefully this wasn't that confusing or people are having similar questions too...

 

[Kamolga]

Yes, everything can be done wit userforms, the most complicated being adding lines (I would use an "add" button but that is not easy coding I believe)

On the other hand, you can use tables on the sheet, they extend automatically when you add a line, so they are kind of nice.

For the UF, you can chose to work with 3 letters acronyms or get the Acronyms to be replaced by their full name when getting out (I would rather lists).
To show time or km, my way is to insert a label with no text and when two locations are entered, I assign the right value to it.
It is possible to do the same with a textbox if you want the user to be able to overwrite the value