How To Automatically Set The Tab Order, Save & Load Data in Microsoft Excel
How To Automatically Set The Tab Order, Save & Load Data in Microsoft Excel
🔥รับเทมเพลตที่ดีที่สุด 250 แบบที่นี่► https://bit.ly/250WKBK-Desc
📢 รับคุณสมบัติของคุณเพิ่มใน PATREON ► https://bit.ly/PatreonDesc
👉ขายซอฟต์แวร์ EXCEL-Based ของคุณเอง► http://bit.ly/MentorProgram_YTDesc
👨💼เข้าร่วมคลาสมาสเตอร์แดชบอร์ดที่น่าทึ่งของฉันที่นี่► http://bit.ly/Dashboard_YTDesc
📣เรียนรู้ VBA จากพื้นฐาน ► https://bit.ly/VBACourse_Discount
ในการฝึกอบรมสัปดาห์นี้ ฉันจะแสดงวิธีตั้งค่าลำดับแท็บ บันทึก \u0026 โหลดข้อมูลโดยอัตโนมัติ
📣30-HOUR VBA COURSE► https://bit.ly/VBACourse_Discount
⬇ดาวน์โหลดเวิร์กบุ๊กสัปดาห์นี้ฟรี:
📩 การใช้อีเมลของคุณ: http://bit.ly/AutoTabSaveWb
💬 FACEBOOK MESSENGER : http://bit.ly/AutoTabLoad_FbDl
หลักสูตร EXCEL:
►DASHBOARD MASTERCLASS: http://bit.ly/Dashboard_YTDesc
►โปรแกรมการให้คำปรึกษา: http://bit.ly/MentorProgram_YTDesc
► หลักสูตร VBA สำหรับผู้เริ่มต้น: https://bit.ly/FreeVBACourse
ผลิตภัณฑ์ EXCEL:
►คู่มือทรัพยากร Excel ขั้นสูง: http://bit.ly/UERG_YTDesc
►250 สมุดงาน Excel: https://bit.ly/250WKBK-Desc
►Automate การเขียนโปรแกรม VBA ของคุณ: https://bit.ly/AutoMacroAddIn
►สร้างแดชบอร์ดและแผนภูมิในไม่กี่วินาที: https://bit.ly/Pine-Bi
►ปกป้องแอปพลิเคชัน Excel ของคุณจากการโจรกรรม: http://bit.ly/SecureExcel
🧐ค้นหาผลิตภัณฑ์และหลักสูตร Excel ที่ดีที่สุดได้ที่นี่: http://bit.ly/ExcelProducts
การประทับเวลา:
0:00 - บทนำ
0:31 - ภาพรวม
4:52 - กิจกรรมเปลี่ยนการเลือก
6:26 - การจัดรูปแบบตามเงื่อนไข
7:18 - โหลดลูกค้า
16:40 - กิจกรรมการเปลี่ยนแปลง
21:43 - ตั้งค่าคีย์
22:12 - รับคำสั่งแท็บ
23:34 - ปิดใช้งานเวิร์กชีต
27:11 - เปิดใช้งานชีต
เข้าร่วมชุมชนของเรา:
►กลุ่มเฟสบุ๊ค: http://bit.ly/groupexcel
►เฟสบุ๊คแฟนเพจ: https://bit.ly/EFF_FanPage
ติดตามฉันในบัญชีโซเชียลมีเดียของฉัน:
🐦ทวิตเตอร์: https://twitter.com/Excel4Freelance
🌏เว็บไซต์: https://www.ExcelForFreelancers.com
🔗LinkedIn: https://www.linkedin.com/in/excelforf…
👤เฟสบุ๊ค: https://www.facebook.com/ExcelForFree…
📸 อินสตาแกรม: https://www.instagram.com/excelforfre…
🎥 Rumble: https://rumble.com/c/ExcelForFreelancers
👤ชาวบ้าน: https://locals.com/member/ExcelFreela…
✉โทรเลข: https://t.me/ExcelForFreelancers
📲Gab: https://gab.com/ExcelForFreelancers
เกี่ยวกับเรา:
ฉันช่วยผู้ที่คลั่งไคล้ Microsoft Excel เปลี่ยนความหลงใหลในตัวเองให้เป็นผลกำไร เพื่อให้พวกเขาสามารถสร้างรายได้แบบพาสซีฟโดยใช้ทักษะของพวกเขาโดยไม่ต้องเสียเวลาแลกเงิน
ฉันชอบทำวิดีโอเหล่านี้ให้คุณทุกสัปดาห์ วิธีที่ยอดเยี่ยมในการสนับสนุน Excel For Freelancer เพื่อให้การฝึกอบรมเหล่านี้ฟรีทุกสัปดาห์คือการสนับสนุนเราผ่านหนึ่งในผลิตภัณฑ์ที่น่าทึ่งที่นำเสนอซึ่งจะช่วยให้คุณเพิ่มพูนทักษะ Excel และบรรลุความฝันของคุณ
รับการแจ้งเตือนทันทีด้วยวิดีโอการฝึกอบรมและสมุดงานใหม่ฟรีโดยสมัครรับข้อมูล
อัปโหลดวิดีโอการฝึกอบรมใหม่ทุกวันอังคาร
ขอบคุณมากสำหรับการสนับสนุนอย่างต่อเนื่องของคุณ,
Randy Austin
#exceltraining #taborder #saveandload
Content
0 -> hello and welcome this is Randy with Excel for
freelancers and today we have a very cool training
5.43 -> where we're going to show you three different
features one being the automated tab order in
10.8 -> which we can control the order of when a user
tabs the second is automatically loading data
16.71 -> into specific cells and the third is going to be
saving data into the cells so please stay tuned
24.48 -> it's going to be an awesome training and great
to have you here I think you're really going to
34.41 -> enjoy the training today I've put together three
small features into one super packed training
42 -> today and first let's go over as we like to do
why why would we want these features okay the
47.73 -> first feature is the tab feature and basically
that lets us control the order in which we want
54.72 -> to user to tab now why is this important well
often we have a specific tab order that we want
61.92 -> the user to go in or we may want him to focus only
on specific fields specific cells for him to go
69.03 -> through so it's really nice as with most software
applications there's a specific tab order and we
74.97 -> can set that tab border and we can control the
user movement and this can also save the user a
80.28 -> whole lot of time so even with cells protected
the Excel doesn't always automatically a lot of
95.79 -> time and a good user experience the next up is
a load feature and basically what the idea is is
101.58 -> that when we select information from a table below
it loads those details either into the table below
107.13 -> or into another table and one of the reasons we
want to do this is we also want to often verify
114.09 -> the information we may not want them to select
information within a specific table we may not
119.49 -> want them to change the information in a table
and we may want them to do it through the form
124.86 -> itself once it's done through the form we can
verify information that it's accurate we can
129.66 -> set specific details we can also make sure that it
gets in the right place so by loading this details
138.55 -> it automatically loads it up in that way the user
can focus exactly on the cells above and the next
145.27 -> is to save it and often we may not want the user
to save directly in a table it's like saving in
151.06 -> a directly in database but we want to use more
of a form based now Excel does have a form base
156.1 -> where it's a pop-up form but I like this feature
because there's no form that needs to be popped
160.36 -> up there's no placement of the form and we have
a lot more control when we use our forms within
166.24 -> the table itself as far as the look and feel so
we can do that simply by typing in and then the
171.64 -> notes appear down here so if we are to select
a different information and we change the email
178.36 -> to Frankenstein since it's Halloween and then I
think I spelled that wrong but it's alright don't
185.59 -> hold me to that and then we could just hit enter
and automatically the email has changed down here
191.23 -> in the right row so that is a great way to do
that and the reason we want to do that again is
197.14 -> to simplify a user experience so there's a lot
of reasons we do this I do that in a lot of my
202.45 -> applications in which I'll show you a little bit
later on so you can see what that might look like
207.4 -> you know in a full-blown automation application
and so let's get to it so how is this done all
213.67 -> right let's go ahead and go through one by one
the first we have here is a table selection now
218.56 -> we went over this into training how to highlight
the Select rope and I'll go over real quick again
223.54 -> and as with most applications I like to hide
the data in usually the first two columns let's
229.42 -> clinton unhide those and then you'll see it let's
go ahead and unhide those in two columns a and B
236.47 -> okay and basically what I've done is on selection
change in the code I've put the row right here
244.24 -> into B 1 so that means when we select any item
from this table it's going to put whatever row
251.53 -> that is and B 17 and we can do that from VBA
by going into the VBA model alt F 11 or in the
259.39 -> developers tab under visual basic and of course
if you don't have that visual if you don't have
264.97 -> that Developer tab you can view it in options and
then under the customize ribbon you can see the
271.15 -> developer right here so make sure that's checked
back into the developers tab and the Visual Basic
276.79 -> we can look at our code now we're focused on
customers okay that is the sheet so we're gonna
282.85 -> go into that sheet into the VBA model here okay
and we're woke focused right now on selection
289.93 -> change which is this one right here basically
selection change means that when we select all
296.26 -> we nearing is selecting when we select something
happens okay and so what is it that we want to
303.34 -> happen well when we select any item within this
table we want the row to go here that's all we're
308.59 -> doing that's all we're doing right now within the
VBA code so what we're saying is if not intersect
316.09 -> e13 that's the first cell in our table e13 okay
the last cell in our table j26 if you're creating
328.27 -> a table make sure this goes to the last row you
can leave this as a high number if you like okay
333.07 -> is nothing that means basically this is a double
negative if not is nothing when you have a double
339.07 -> negative basically what is saying in Excel is if
there is a selection double negative cancel each
344.35 -> other out so that means if there is a selection
that's how it translates then do something and
350.2 -> what do we want to doing it's simple here B 1 B
1 equals the target row okay this load customer
358.42 -> we're gonna focus on that a little bit later on
but for this target Rome okay so putting that in
364 -> the target row doesn't highlight the cells right
however when we add conditional formatting it
368.95 -> does and into the conditional formatting we go and
I'm going quickly because this is all covered in a
375.34 -> prior lesson but I just wanted to show people
that haven't seen that what's going on if you
379.21 -> want a more detailed please check out our excel
for freelancers where I go into a real detailed
383.95 -> on this under the manage rules and basically
here we have edit rule and we have saying if
390.19 -> b1 is the row I want it to color this color and
so I've given it a format so that is how we do
396.28 -> that this particular one simply allows us to color
alternating rows okay you see how each one's blue
402.67 -> white blue white and that's all that says when
you get the download you can check it out okay
408.25 -> so the idea that I want here is when I select a
row I want the information in that row to appear
414.49 -> in the upper six fields okay we have customer name
city home phone number address email and notes I
420.55 -> want all the information from what I select here
to appear here okay you know how how do I do that
426.43 -> so I've done that with another macro and as you
saw in the code there's something called load
430.42 -> macro now let's go into that okay so we're gonna
go into form load this module right here okay and
438.19 -> it's very very simple I've kept it simple there
are many ways to do this okay there's many ways
443.83 -> but here's I've chosen a way that we lesson the
code so there's just a lesson to code this stop
450.43 -> code I'm gonna pause this and I'm gonna pause this
reset code and I'll go over that issue that's not
455.02 -> necessary these are used to speed it up and okay
and we'll go over that and we'll but basically all
459.85 -> that's doing is stopping the calculations of the
workbook and then continuing them the main heart
466.45 -> of the code is right here so basically the idea
is when we select honest on a row we want to place
474.82 -> the first name here we want to place the address
right in i3 we want to place the city in f6 and
485.59 -> so on for all six of those fields so that's what
we want to do now we could now I could write code
491.56 -> that basically says that would says something like
now we've defined the customer oh we know what row
498.4 -> we're doing right we're working on row 17 so we
know the row so we know the road to dinner but
503.89 -> how do we get this data up here okay we can do
that with simple with some very simple code and
510.1 -> so here we've defined the row we've to mentioned
the row is long okay and then we've defined it
516.28 -> in B rows so we know the customer row but now
what we have to do is basically we have to tell
521.17 -> Excel okay for each of these columns that means
column right so for each of these columns we've
528.91 -> got a 1 2 3 6 we need to put each one of those
in the area here and how do we do that okay well
535.69 -> it's gonna excel's gonna have to go through all
six columns right it's got to go through all six
540.01 -> and now what columns are those okay in Excel right
Excel can use range e through J but if we want it
547.6 -> to automate in other words if we want to use like
a for next where it goes through column five six
551.95 -> seven eight we want to go through all the columns
as far as in numerical we need to we need to know
556.63 -> what columns those are so example e if we type in
column right what column is this this is calling
562.87 -> five right so basically I'm gonna drag this over
what I need to do is I need to tell Excel okay I
568.33 -> need you to go through call five through ten take
this each data and put it in here five through ten
575.62 -> right we know the row right so we can do that
if we know this is calling five six seven we
582.43 -> can go ahead and mark each row so let's go ahead
and do that okay so what I've told Excel is okay
590.02 -> now for the customer column also long right also
dimensions for the customer column five through
597.01 -> ten as you can see here five through ten okay so
we know the row and now we know the column okay
605.2 -> so let's go ahead now the cell now we need to know
okay now we know where each one is but where do we
611.08 -> place this let's go ahead and unhide and this is
called mapping data mapping okay I'm gonna unhide
616.27 -> row eleven okay and what I've done here is I've
mapped each one of these out okay so that means
624.01 -> when it gets to when Excel gets to column five
how do we know where the customer name is well
630.91 -> actually the customer name is in F three right
whereas the customer address is in a three right
639.58 -> so basically we're telling Excel when it gets to
this column where should we put the data where
645.31 -> should we put the data okay there's many ways
to do this is a great one because it's very very
649.51 -> light on code you can see it's just a very minimal
amount of code so basically I've taken I've put
655.57 -> the information in cells and put it in so okay so
that means okay when it gets to row 16 column 5
665.89 -> look here find out where they from where that dude
is supposed to go and put it here okay so that's
673 -> exactly what I've done in the code when you pull
up the code let's go ahead and pull that up again
678.37 -> cell that means the cells going to go is already
located in row 11 and the customer column we know
686.86 -> the customer columns gonna go from 5 to 10 okay
so what is the cell the cell is a string value the
693.88 -> cell is gonna tell us so when it goes remember
this is a fixed row this row doesn't change so
699.61 -> that means row 11 okay row 11 is fixed as it goes
through it's going to mark each one of these is
706.72 -> the cell because we need to pull that cell we need
to know what cell is gonna change so so first one
712.75 -> for row 11 column 5 is F 3 okay so we know the
cell where it needs to put is F 3 right so in this
722.26 -> case in the first case that cell is gonna be F 3
okay so then all we need to do is take that cell
730.12 -> right in this case in the first case it's going
to be F 3 that means range F 3 equals what what
738.4 -> is it equal well it's gonna equal now we know
the customer row and now we know the customer
743.47 -> column value see how simple that is we know the
row the row is always going to be here okay and
749.23 -> the column is going to change from 5 to 10 so the
first one is going to be Jimmy Dean the second one
754.42 -> is going to be you know park street and so on so
it's going to go through these and it's going to
759.52 -> loop through from 5 to 6 placing all the data in
the individual fields it's just that simple it's
766.06 -> very very very simple on code right and all I've
done is and let me go ahead and update that so
772.33 -> these simple two lines these two lines of code
is all we need to load that information ok and
779.62 -> it's a little bit slow if you look when you watch
it it's that's just pretty fast now but it can be
785.83 -> pretty slow I think we don't have calculation off
but what I've done is to make it even faster is
791.2 -> used something called a stop code and reset code
and what that is is to macros that I use a lot and
799.36 -> I've given you them today and they're called code
riesen you can find them here and basically under
804.55 -> stop code right all we've done is Sagan turn off
the calculation that means we don't want Excel to
809.23 -> calculate anything while we're loading it because
generally every change that that's made to a cell
814.87 -> Excel is going to calculate that workbook and in
this workbook it's very small we have you know
819.28 -> almost no formulas but in the workbook where
you got a lot of formulas and a lot of day it
823.36 -> could be very slow so turning off that calculation
temporarily is very important also screen updating
829.06 -> turning that off temporarily also speeds up Excel
and this is a huge time-saver okay so the only
834.7 -> important thing we want to do is turn it back
on once we're finished with running the macro
838.78 -> so we've used this code reset to turn it back on
okay back into our form load macro so let's go
846.13 -> ahead and activate those so we've got stop code
that's gonna so as we run through this code here
854.2 -> no calculation is going to be made and we don't
need any calculations and then once we're done
859.21 -> we're gonna reset that okay so now once we go back
into the Excel workbook you can see here and it
866.47 -> just loads real real quick all right now them all
so we have save okay now what I want to do is when
874.78 -> I want to make a change here I wanted to save it
so if I've type in here test notes for I want to
881.68 -> save right in that selected row okay there it's
done okay now how do we do that we've done that
887.95 -> also with mapping okay now here's our table here
right we know customer name is is column five we
896.08 -> know again that the address is column six right
we know that because we've got the columns right
902.23 -> here so how do we get so we use what's called
mapping what I've done is mapping and if you
908.23 -> look on a and B here I've done two things and
basically what I've said is yeah telly code if
913.57 -> there is a change here if there's any change here
I want you to take this column and this row and
920.11 -> change it remember this is the row it's gonna stay
fixed but the column we need to know if it's this
925.3 -> remember column five is a customer so this row and
this column and make that change okay so when we
933.98 -> change this to Samsonite boom it changes right
there right here so how do we know we know it
940.58 -> because we're making a change here in this column
and here's the cell okay here is the column and
948.41 -> here's the road now how do we do that through VBA
let's go ahead and go back into the VBA and again
952.88 -> it's very very simple code and let's go ahead
and into the customer sheet okay and we have
959.84 -> we're focused on here on two different columns of
data okay one is F right we have three fields in
968.27 -> column F the second is we have three fields in
column I so we treat them as separate okay now
975.02 -> I've chosen column B to map F to map excuse me to
map I okay and call them a right to map F okay so
985.43 -> we have different mapping so I written code says
if there is any change from F 3 to F 9 then do
994.52 -> something okay so let's go ahead and take a look
at that into the VBA model again if there's a
1000.13 -> change remember this one is work sheet change work
sheet is not selection change that means an actual
1005.53 -> change when they make it when there's a selection
to these fields nothing happens but when there's
1010.66 -> a change when they actually make a change to the
data we want something to happen and what do we
1015.25 -> want to happen well this range F 3 to F 9 we went
over that what we want to do is I want to say okay
1022.9 -> I want you to make a change and I'm gonna go with
cells okay and when you're doing cells right cells
1032.53 -> let's go ahead cells okay the first thing that
you need to do is the row what is the row and the
1039.85 -> second is the column okay first is the row second
is a column remember range when you're doing range
1045.34 -> it's different it's the opposite okay range is
gonna be generally the column first right and when
1052.42 -> you're using the we should we use ranges where
we're defining columns like ABC right but when we
1056.92 -> have two numbers and we have a number for the bro
and number four the column we need to use cells
1062.54 -> that's why we use sometimes range and sometimes
cells okay so cells is good for when columns and
1068.75 -> rows are both numbers all right so in this code
what I'm saying is cells now I need to get the row
1075.2 -> what is that row right remember in cells the row
comes first well we know the row is in b1 right
1081.35 -> the row of that customer is in b1 right okay so
that's first week now we've got to identify the
1088.64 -> column right what is the column okay well we're
saying our column and our column is in B right
1097.49 -> B and the target row the target Rose whatever row
was changed okay be okay or in this case I'm sorry
1106.73 -> in F 3 to F 9 it would be a write a and the target
rose so we know the target row the target row is
1114.11 -> 3 so what is in a3 a3 is this value right here v
right v tells us the column all right to put that
1124.25 -> in which is here okay I know it's a little bit
hard to follow but the code is simple but it takes
1129.26 -> a little bit and looks confusing but remember
all we're doing in cells is we need to find the
1133.73 -> row first and then the column okay that's it okay
so the row we know is b1 the column right we the
1141.74 -> column is here the column is in this b5 okay
the b3 excuse me a3 in this case would be five
1155.09 -> okay all right so here range B and the target row
that's our column B Targa roof in this gates it's
1164.36 -> five so in this case our customer row is 21 our
customer column we're changing is 5 21 5 so that
1175.13 -> means cells 21 row 21 column 5 we want to change
it what do we want we wanted to target value okay
1184.22 -> so that means when we make Fred Fred errs when we
make a change right that means all of a sudden we
1193.73 -> want row 21 column 5 make a change that's what
we're telling you so row 21 column 5 make a change
1202.22 -> boom row 21 right here : 5 it made the change so
that's we're doing that's why we've set it up so
1209 -> as we go through in this case it would be column
7 in this case it would be column 9 right column
1216.11 -> 9 is here okay let me go instead of these so it's
very clear to you which column is which and then
1221.45 -> we can delete those once we had that okay so calm
9 and then the second one is okay now if there's
1228.14 -> a change to I 3 to i 9 then use what's in column
beef okay same idea right if we make a change to
1236.6 -> the notes it's going to be in this case row 20
column 10 call them tennis notes right so test
1244.79 -> the friendly customer boom there it is changed so
it's changed of so you see how simple that can be
1253.22 -> once we map it out once we map it out we know what
we're doing okay and that's and that is basically
1259.49 -> how to save information that's that's really I
use that almost in every single application I
1264.5 -> develop I use it in in many different ways and all
and I'll show you a little bit later on so that
1270.77 -> gives you a real idea of how powerful that can be
and that's only good you see how little bit of a
1275.57 -> code is required and the last of the features
that I want to show you is that automated tab
1280.88 -> order and this is a great great feature I didn't
actually write the tab order but I've modified it
1286.52 -> to use it to my needs and let's go ahead and take
a look at that and basically all that is necessary
1292.49 -> and this is it's a pretty good good amount of
code but you can just copy and paste it it's
1296.48 -> not so important that you know how every single
feature works in this but basically it's saying
1303.5 -> when a user presses tab what do you want to do
right when a user presses enter what should we
1309.83 -> do when you press tab what should happen right and
basically we don't want this on right and so the
1317.69 -> next thing is we need to map out okay in the tab
order what is the tab order we need to be able to
1322.76 -> set that it's kind of a little bit cumbersome it
takes a little bit of time if you a lot of fields
1327.5 -> but basically when you go through the code here
right you're gonna find this function here get
1332.27 -> tab order okay and all we need to do is set the
array okay and you need to put the cells in here
1339.41 -> in which you want the tab order to happen okay
in our case I don't want anything to happen if
1346.43 -> they've selected this okay I don't want anything
to happen if we're down here I only want our tab
1351.8 -> order to work if we're up here so what I've done
is I've started it out with an if statement and
1356.96 -> I've basically said in that if statement that if
the active row is less than 12 less than 12 then
1366.92 -> get the table order otherwise you know don't do
any this is not so important here this just helps
1372.17 -> you I'll comment that out because it's not really
used in this case but it helps you if you want to
1377.57 -> use it for another function in which you don't
want to use tab borders for a certain level of
1382.76 -> rows over or columns over and so basically what
I've done is I've set f3 then I 3 then f6 I think
1390.29 -> so this is the order I choose and it's that simple
you put in that order and then it works just like
1395 -> that so if I change this if I change this to g3
right after okay let's go ahead and do that if I
1404.24 -> put in g3 here g3 okay and then a comma alright
and now we're in f3 it'll go right to g3 okay
1413.45 -> so you see when you use shift tab it goes right
back so it's really nice it's a great feature and
1418.94 -> I use that as well in many of my applications
so I think you're going to find that extremely
1425 -> useful and quite helpful now the other thing that
it is with this we basically have to turn this on
1431.57 -> and off and we can do that through here set on key
okay now we can do that throughout the application
1438.02 -> if it's true if this part is true then it's gonna
work if it's false it's not so for example when
1443.18 -> we go to the customer sheets you can see that
I've set the on key to true that means I want
1448.13 -> it to work but what if you go to another sheet
you don't really want the tab order look in this
1453.32 -> year we've got a tab where that's not really gonna
help me okay we only want a specific to one sheet
1458.6 -> or specific sheet so what we want to do is we want
to turn it off okay when we're leaving that sheet
1466.47 -> we can do that in VBA as well simply by on the
worksheet deactivate here we can set the on key
1476.34 -> to false okay and that's all we have to do okay
and now once it's set to false we go into into
1483.42 -> let's leave this go and shoot one and now we can
see we've got our standard Excel tab order and now
1488.73 -> if for something if there's an example you want
it to do a different thing like let's say I have
1493.98 -> multiple sheets and I want out of a specific tab
order on one sheet and I want another tab order
1498.36 -> on one another sheet there's a lot of things
all we need to do is test for a sheet name so
1503.16 -> we could do something like under tab macros we
can go down here we could do something like if
1509.67 -> she if active sheet name we could do active she
bought name equals customers just get my typing
1522.03 -> down equals customers then right you could do
that right and surrounded by an F that way that
1530.79 -> way it's also only going to work on a specific
customer and so you might want to have this tab
1535.89 -> order for this sheet and you might want to copy
and paste it and have a different tab order for
1540.09 -> another sheet so using this if active sheet name
equals that kind of sets it so it only works for
1546.15 -> specific sheet and that's really helpful so you
could do it if it only works for a specific sheet
1550.38 -> if it's if the active cell or active row is above
a certain level or below a certain level so you
1556.68 -> can put in specific if-then features that will
only make it work for certain sections certain
1563.82 -> sheets so it's really really helpful and another
one is um let's go ahead and open new workbook I
1569.34 -> want to show you one more let's go ahead and open
the new workbook and you see here we are in a new
1574.83 -> workbook and the tab is also we don't really want
that right not at a new workbook so we can we can
1581.07 -> change that on workbook deactivate okay when we
go into workbook okay and we deactivate this let's
1588.39 -> go ahead and select workbook right and that means
when the workbook is diac debated what do we want
1594.58 -> to happen I want to basically I want to set that
on key default set on key to false okay and so
1602.86 -> now when we deactivate it the tab waters gonna be
normal okay but look also the tab borders normal
1610.69 -> now again to we don't really want that we want
it back on we want it back the way it was right
1615.94 -> we want that tab order so again once again on
within the VBA model let's go ahead and set that
1625.06 -> workbook on sheet activate we can do something
like if active sheet dot name equals let's see
1637.3 -> customers then set on key the truth okay that way
let's see hopefully I got that right that way now
1649.12 -> it's normal right so let's go ahead go out normal
now when we go back in it's not okay so let's go
1657.19 -> through that and see what we did okay so actor
sheet name equals customers we can do active
1665.26 -> sheet name all right equals truth so we can also
let's just go ahead and get rid of that I'm not
1675.91 -> sure whether that's not working so now when we
go back in all right I just said it to deactivate
1682.18 -> let's make sure I have that right workbook sheet
activate let's see we don't want sheet activate
1687.73 -> we want activate there we go activate work but not
the sheet activate activate okay get rid of that
1696.46 -> workbook activate all right now we back in and
now we have the right order okay so it's workbook
1703.57 -> activate and then that would work so that'll
work we can also do we can also surround that
1709.27 -> by the in if statement to as well on this one
if active sheet dot name equals customers okay
1719.6 -> then alright set on key to true and then that work
so that way when we're activating it it'll work
1725.93 -> right and now for on sheet1 it's still good ok
so that's good so setting that on key ok setting
1734.36 -> that on key is really going to help us control the
user environment and control the the ways in which
1743.42 -> a user can navigate so it's really really helpful
alright so we've covered three things today we've
1750.68 -> covered automatic tab we've covered loading
information into a form into what I call a form
1756.83 -> and once we don't need these we can delete these
and you don't want to delete the maps but you can
1762.35 -> hide this ok and we've covered automatically
saving to a database alright and I'm going to
1768.26 -> include this workbook in the download I hope you
have enjoyed this halloween tuesday training and
1774.17 -> another one next week as always please like
and share as much as you can if you have not
1779.33 -> already join over 1,500 members in our experts
sub group and thanks so much have a good one
Source: https://www.youtube.com/watch?v=40aYorpxPiU