Updated June 12, 2023
PART- 15 – VBA (Visual Basic) ActiveX Control – modification
Transcript of the Video – VBA (Visual Basic) ActiveX Control – modification
Video on VBA (Visual Basic) ActiveX Control – modification – Ok so i have hopes you would have try this exercise and let the look at solution here and the here let’s look at what will be corresponding coding for banks so I’ll go to the Developers Tab and in order to access this code behind banks I must go into design mode that is first think that I must do in order access the properties or maybe you know changes the colors may be look code I’ll click here on bank and moment I do that you kown it says that this is called as private sub command button _ clicks so may be command button 1 _ click is just moment banks I’ll just kind of change the names to bank so that you know it is kind of more logical in nature so I’ll write this as bank and now I will again double click and now you can see there is a private sub banks _ click so though the original one still saved you know the new one has been created so I’ll delete the old one because that not required and no more exist so this is what we are kind of looking at so what do you want here in this case the movement I’ll click banks whatever was done here like it got sorted and it got out into the output sheet the same thing should happened in this worksheet so basically we are saying that whatever was the Macro which we had looked at in the filter dynamic worksheet that the same macro which we would like to kind of access for this code as well right so I have just gone into the edit filter and now this is the code which we are kind of taking about because this is same think which want to run here so I’ll just copy this code and CTRL C ok and the another think to note is that the sheet which have I was working on was sheet no 3 right so I need to click here and that’ were are private sub banks _ click is it is not in sheet 2 it is not in sheet 1 but it is in because that’s where I had created object so I must paste the same code here so let me do it again because may be this is the first time were you probably accessing the code within worksheet so what I have done is I am trying to see what was the code within filter underscore dynamic and I went into the edit button and saw that this is the code which was working fine for sheet 2 ok so this is whole code which I had copied now when look at where is my code associated with bank this is sheet called Data Filter sheet this is where I had double clicked and went into the sheet so you can see this kind highlighted this code is inside sheet no. 3 ok so I go into sub and paste the whole code now I think more or less this is what we wanted but we really need to always think that weather some level of customization is required or not so what this code was doing was essentially it was copying from sheet no 1 shorting it, filtering it and pasting it in sheet no 2 so here what do you want we want to paste it in data filter sheet right here is what we want to paste it so there would be slight modification in this code this would be sheets instead of sheet 2 we would like to paste it in data filter sheet so that’s what I will write instead of sheet 2, I’ll write data filter sheets so these are small modification that you will have to do when running Macro and using VBA codding to customize it ok so please note again we have not written the code we have just modifying some aspects and hooping that these codes actually run so I’ll stop here and I’ll try to run this course so what we get is run time error which is 1004 so let me see what error it is all about what happens here is that since we are in the data filter sheet and what is the first command see range A1.select right Range A1.select so we are talking about range A1 of data filter sheet or range A1 of sheet 2 or Range A1 or sheet 1 so obviously we want to select the range A1 for sheet 1 so I guess you must have kind of figure doubt we have missed something so we must select sheet 1 first like what we did for selecting data filter sheet I’ll copy the same function and instead of data filter sheet I’ll write this as sheet 1 ok this is where the first step is select sheet 1 go to its A1 select and then do the task ok so let me try this works now ok I guess we still are finding some issues now let’s look at this range here range A1. Select maybe, you know, and here again; we have the rage A1. Select see in most cases, you will find that this kind of error actually go when you use this word called active sheets because many year time VBA actually confuses and creates lot of confusion within the codes itself and runs and throngs error so what I am trying to say here is that once you are in sheet 1, this is basically you are active sheet so instead of just writing range A1. Select write this as active sheets. Range A1. Select so this is explicitly telling the code that this was now the active sheet and within the active sheet select this range A1 a probably that error is being through because we have range A1.select twice in this code and you know the code is unable to identify still that which one is active as off now so I have written active sheet here and I will probably to the same thing for the other one as well so I have done same think and now I am hoping that this code would run so let’s click on run and probably this as run in the bag round so since there is no error as such let me see that happened ohh yes I guess this is what the code is all about so I guess we got this code correct so let me click here on banks and see if this runs again so finally we got into the code and remember the moral of story we have used active sheets. Range any sheet which we want to select should go as this kind of format and majority of code has been borrowed from what we learn from Macros so that’s about let’s try to kind of you know copy and paste the same code for material software and utilities and see if this are also working fine ok so but before that let me go to the design mode and change the command buttons to materials software to just copied from here so that we are not confused about the name itself so I am just doing this cosmetic changes here utilities and now I will go inside individual buttons and kind of copy this whole code and which we got it correct right so this is code what do you think will be the change now so since we have the code only things which we have to do is just logically connect only criteria changes so here we will write this as materials ok let me go back to my worksheet and see if this material things is working fine ok so you know still get materials so that’s fine now let me go to design mode and also do the same think for software and utilities copy the code pasting it and I’ll just change criteria here software, Software right yaa so going back to the sheet going back to sheet clicking on utilities I’ll copy this code and change this to utilities ok perfect so I guess this code should actually work extremely fine so let me just come out on design mode and click on software’s look like it is not kind of working well with us let me see what is the problem here ok so probably I figure doubt what is problem here we have used software to look at the sector but in our code we had mentioned the criteria as software with an S so this is where we actually had missed so though code was fine then give anything let me again now the try same thing software and now it is working fine let me click on utilities and it is working fine let me click on banks again perfect let me click on material so though the code actually works perfectly fine we see that the original you know output still present so what happens here I mean just to go back and look at what has happened in this case I’ll just delete everything I’ll click on banks first so there were 7 banks 1,2,3,4,5,6,7 banks and now I’ll click on material and what I see is that obviously this output is correct but it has still was unable to delete the original you know banks which were also in access of 3 so bank 1,2,3 4 are still displayed here so may not be the code may not be still fully correct to an extent required it to be so we may have to kind of just manipulate this code a beat more so that we can kind of make this fully functional.