VBA User Form Recognition?

MFish

Board Regular
Joined
May 9, 2019
Messages
76
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...:eek:
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top