1 00:00:00,00 --> 00:00:04,200 Hey, This is Puneet back again and this video is going to be 2 00:00:04,300 --> 00:00:09,100 interesting in so many ways. So we are gonna learn how we can use a VBA 3 00:00:09,200 --> 00:00:14,000 code to hide or unhide the status bar, or how we can show 4 00:00:14,100 --> 00:00:18,900 a message on the status bar. So, first of all, I'm gonna open the Developer Tab and I'm gonna 5 00:00:19,000 --> 00:00:23,800 Click on the Visual Basic Editor to open it and then I'm gonna insert a new model 6 00:00:23,900 --> 00:00:28,700 to use and from here I'm gonna start the code 7 00:00:28,800 --> 00:00:33,600 [typing code] 8 00:00:33,700 --> 00:00:38,500 So the first thing is to use the 9 00:00:38,600 --> 00:00:43,400 application object and as well as you press 10 00:00:43,500 --> 00:00:48,300 a dot.It gives you a list to select the properties so you need to select 11 00:00:48,400 --> 00:00:53,200 Display Status Bar and here you 12 00:00:53,300 --> 00:00:58,100 can use two different values False and True. So I'm gonna use False 13 00:00:58,200 --> 00:01:03,000 here. Now this line of code tells VBA to 14 00:01:03,100 --> 00:01:07,900 Hide the Status Bar. So application displays status bar equals 15 00:01:08,000 --> 00:01:12,800 False and now if I run this code it simply hides 16 00:01:12,900 --> 00:01:17,700 the Status Bar and now if I want status bar back I'm gonna use 17 00:01:17,800 --> 00:01:22,600 True here. Application displays status bar equals True 18 00:01:22,700 --> 00:01:27,500 that means to show the Status Bar and I fell on this code 19 00:01:27,600 --> 00:01:32,400 it simply gives me Status Bar back. Alright! 20 00:01:32,500 --> 00:01:37,300 So from here I'm gonna change the property 21 00:01:37,400 --> 00:01:42,200 .Status Bar. Now I can you this property to 22 00:01:42,300 --> 00:01:47,100 show a message (a custom message) on the Status Bar so I'm gonna use 23 00:01:47,200 --> 00:01:52,000 equals again and then a message that I want to show 24 00:01:52,100 --> 00:01:56,900 [typing code] 25 00:01:57,000 --> 00:02:01,800 Alright! so I'm gonna run this code 26 00:02:01,900 --> 00:02:06,700 as soon as I run this code it simply shows that message 27 00:02:06,800 --> 00:02:11,600 which has been specified in the code on the Status Bar. Now, here is one thing that 28 00:02:11,700 --> 00:02:16,500 you need to take care that when you show a message on the Status Bar using a VBA code it 29 00:02:16,600 --> 00:02:21,400 will stay there untill or unless you clear that message so 30 00:02:21,500 --> 00:02:26,300 to clear that message we need to run this code again but with the Blank Value. 31 00:02:26,400 --> 00:02:31,200 So, Application.StatusBar equals a Blank Value 32 00:02:31,300 --> 00:02:36,100 and when I run this code it simply gives me 33 00:02:36,200 --> 00:02:41,000 the default status bar back 34 00:02:41,100 --> 00:02:45,900 or you can also use False to come back to the default Status Bar if you already have 35 00:02:46,000 --> 00:02:50,800 a message on that you can simply use False. Just like that. 36 00:02:50,900 --> 00:02:55,700 Now, let's see an example where we can show a progress message on the 37 00:02:55,800 --> 00:03:00,600 Status Bar. A progress message of a loop so for this I'm gonna 38 00:03:00,700 --> 00:03:05,500 use a code. Now, in this code, we have a loop that will run from 39 00:03:05,600 --> 00:03:10,400 1 to 10,000 times and show a progress message on the Status Bar. So, let's 40 00:03:10,500 --> 00:03:15,300 understand this code line by line so in the first line here I'm using a 41 00:03:15,400 --> 00:03:20,200 variable to store the count of the loop and 42 00:03:20,300 --> 00:03:25,100 in the second line I'm using, actually, I'm starting a loop that will 43 00:03:25,200 --> 00:03:30,000 loop from count 1 to 10,000 say in this line I'm using again 44 00:03:30,100 --> 00:03:34,900 that Application.StatusBar and here we have a message that will show 45 00:03:35,000 --> 00:03:39,800 the progress so it says x of 10,000 so basically x is the counter of the 46 00:03:39,900 --> 00:03:44,700 loop and the format x/10,000 percent so this 47 00:03:44,800 --> 00:03:49,600 the part will show the percentage of the progress and then a 48 00:03:49,700 --> 00:03:54,500 closing loop and here I'm using those events to not to hang the 49 00:03:54,600 --> 00:03:59,400 excel application so basically when you run a loop excel simply a freeze 50 00:03:59,500 --> 00:04:04,300 everything untill that loop stop and here I'm using 51 00:04:04,400 --> 00:04:09,200 the Application.StatusBar = False to clear that message in the end. 52 00:04:09,300 --> 00:04:14,100 So make sure to have a look on the Status Bar because this progress 53 00:04:14,200 --> 00:04:19,000 message will be there for a few seconds. 54 00:04:19,100 --> 00:04:23,900 Now as you can see that progress that loop is running. 55 00:04:24,000 --> 00:04:28,800 Typing 56 00:04:28,900 --> 00:04:33,700 It will take quite a few seconds 57 00:04:33,800 --> 00:04:38,600 Alright! wait 75% 58 00:04:38,700 --> 00:04:43,500 85%, 95% and 100. 59 00:04:43,600 --> 00:04:48,400 Now, here you need to note down the two most important points 60 00:04:48,500 --> 00:04:53,300 that you always need to use Application object before using 61 00:04:53,400 --> 00:04:58,200 Status Bar or display Status Bar property and you always need to 62 00:04:58,300 --> 00:05:03,100 clear the message from the Status Bar using a code that means you 63 00:05:03,200 --> 00:05:08,000 need to use Status Bar = False or a Blank Value to 64 00:05:08,100 --> 00:05:12,900 clear the message. Hey, thanks for watching 65 00:05:13,000 --> 00:05:17,800 this video and I hope you found this VBA trick useful and 66 00:05:17,900 --> 00:05:22,700 make sure to subscribe to this channel because I will be coming out with lot of cool 67 00:05:22,800 --> 00:05:27,600 and helpful VBA Tutorials in the coming weeks. So, make sure to hit 68 00:05:27,700 --> 00:05:32,500 Subscribe Button and Like this Video and I will see you next week 69 00:05:32,600 --> 00:05:34,900 :)