Excel VBA Forms Part 17 - Editing Data

[@evangelinecheng3171]

Your videos allowed me to discover my passion for programming and possibly reoriented my future career. Cheers and keep the brilliant videos up!

[@divyal5852]

Sucha classy explanation! Thank you Andrew! You're the best tutor! :) Cheers!!!

[@juan140395]

Excellent and very useful video as all of them. Thank for your big effort to achieve this videos Andrew!

[@philipptroger6907]

Thank You! Some stoppers refering to the structure would be helpful.

[@donbogdala5428]

Excellent! Can't wait for the next lesson!

[@donbogdala5428]

Thanks, I am looking forward to them

[@FelixDsouzaJoseph]

Hi Andrew, how to use countif function in userform

[@FelixDsouzaJoseph]

Hi Andrew, How do i apply filter in a userform and then get resulting data in a label under the add to list button.

[@ntsakomculu371]

Hi, Thanks for all the videos. i've learned a lot over a shot space of time. Would you mind doing a series on how to dynamically add events to dynamically added controls and getting them to work like those controls added at design time? Thank you

[@WiseOwlTutorials]

You're very welcome, I'm happy to hear that you found the videos useful! I can add that topic to my list although I think it will be a while before I'll have time for this one!

[@johnhackwood1568]

too good, wonderful series thank you!

[@WiseOwlTutorials]

Thanks John!

[@exceltipsandtricks5763]

Hi, about that procedure which splits the genres string and select corresponding values in the list box... Wouldn´t that be sort of easier to loop through that box clearing all (possibly) seleted items first and then do the second loop the other way around to yours where the nested one would be the one which loops through the items in the list box and when the match is found it gets selected and exit for is used to carry on with other possible genre which split might have returned? That way you wouldn´t have to use any boolean checks nor would you have to care about any previously selected items which may not match the current genres. But overall very good video! Gave me some ideas how to go about keeping track of those previuosly handled records....till now I always used very hidden config sheet for this stuff.

[@thomsondesignbuild]

Great tutorial, unfortunately I got to 38:50 and my data does not populate the form. It works when I used the previous step using 'controlsource' but when using the automated version the box remains blank. Does anybody have any idea what might be going on?

[@MrvladivostokMr]

you had blank cells in the sheet, all values must be in...

[@chrisfekete6101]

Hi Andrew: Thank you for the awesome videos. They are so helpful and fun! I have a quick question. I noticed that you used Property Let and Property Get to create properties for the forms without creating an actual Class. Can one create Property Let and Property Get properties for existing Excel/VBA object? In essence creating additional properties for existing objects? Thank you!

[@krn14242]

Thanks Andrew. Saving this one for tomorrow.

[@krn14242]

+WiseOwlTutorials Not sure if it is just me, but your Topic slides (segways) in between your instruction are blurry.

[@jicbs]

+WiseOwlTutorials Things happen Andrew, no worries! Great Video!!!

[@krn14242]

+WiseOwlTutorials Just completed the video. Excellent instruction.. easy to follow. The rendering gets better in the last few minutes of the video. Weird. Thanks so much.

[@michaelhuis]

Hello Andrew, How can i display the "Private Sub ReadGenres (GenreList as string) in my locals? (in the movie at 1 hour and 2 minutes) To see the steps when you press with F8. I can follow what you do but to understand it.. Its quit difficult. Exactly with the g as variable in the routine.. Because you didnt set a value for it in the first place. > If filmgenre.list(i) = g > but wich value does g have at that moment? Everytime the yellowline goes to every place except that one it seems. I tried to go with the cursor in the subroutine but still nothing. When i press the edit button in my wsheet there is no error. thanks a lot for all the answers on my previous questions! it was a great help

[@hariy5997]

hi sir some one edited my data so i want to know who r edited i need there information throw vba code can u please help me on it

[@jonathanvaughn2255]

Thanks for the amazing videos, I've had my subscription for over 1 year now... Awesome JOB!!! Do you have any tutorials for Google Sheets (Google Scripts Google's version of VBA) ??

[@jalalkasmani2575]

Can we view access database in excel userform in grid view format?

[@janezklun]

As always great video, thank you

[@joshuakarow151]

I created a userform that allows me to filter a data table and now I'm wondering how to set the currentidcell equal to the first cell beneath the table header that is not hidden by data. Currently with what you've shown in the video my code still sets my currentidcell equal to "B3" even though it's hidden by the filter. Any help would be appreciated. Thanks!!

[@joshuakarow151]

It worked like a charm, I substituted "CurrentRecordId" in place of the rowindex number so I could cycle through the list with my navigation buttons. Thanks!!

[@erikainks8979]

I need help with my userform I need to know how to add a datepicker that can run when I edit my data. For some reason, it errors out when I try and run in edit mode. How can I get a datetimepicker to work in edit mode???

[@erikainks8979]

I am trying to also copy and paste the data in a new worksheet and then delete... can you please assist.

[@andrewwest5344]

I find your knowledge amazing. I have used VBA for many years but unfortunately I have developed a lot of bad practices since I am self taught. The following issue may be related to the code, but I cannot see anything particularly wrong.... I have a serious issue with one critical development whereby the user forms will sometimes partly load (not fully displayed) or even freeze while I am editing data in the form when they had successfully opened. I have tried to resolve this for a year or so and did manage to reduce the frequency of this happening by a re-write of much of the code. I realise I am not giving you enough information to help me properly, but wondered if you had ever come across an intermittent issue like this with user forms. This development is quite involved and has a lot of dynamic data being loaded up to the form, and I wonder if the technique I am using is a bad method . The most odd thing about this issue is the way I get the frozen form to release. Believe it or not I repeatedly click the windows 'notification' icon at the bottom right of the screen, to release the form. Sometimes it takes numerous toggles of this button and eventually the form will close normally. Then ones I have managed to close the form, I must close the file down completely and re-open it before I attempt to open the form again, or it will 100% of the time, freeze again. The re-opened file will normally be fine for the remainder of the time I am using it. As an experiment I have stripped the code down to bare minimum to see if it is the loading of the data into the form, but even then the problem still happens, but perhaps less often. Oddly it seems to affect other open spreadsheets when this issue strikes, whereby these other spreadsheet forms will also freeze, so maybe related to windows registry. It issue happens on EXCEL 2016 up to 2019, and on different PC's. I have given up trying to resolve it, and the development is far to big to even consider a complete re-write. I just live with the issue, but is driving me nuts, since this application is something I need to do my job on a daily basis.

[@WiseOwlTutorials]

Hi Andrew, that's certainly a frustrating issue to have to live with. I'm afraid that I don't have any insight into why it might be happening though, sorry!

[@andrewwest5344]

@@WiseOwlTutorials Thanks for answering, you must get many thousands of people asking you questions, so I am very appreciative!

[@WiseOwlTutorials]

@@andrewwest5344 No problem, I hope you find a solution!

[@FulvioGaggioli]

Msgbox "Thank you WiseOwl!"

[@SantoshBudakoti]

great video! I have create a formula in excel for counting a quiz ans but it not work properly, in first column ans key second response and third use formula =if(a2=b2, "2",if(a2b2,"-1",if(isblank(b2)=" ", "0", 0))) I want to if ans is right show 2 if ans is wrong show - 1 and if ans is blank show 0

[@SantoshBudakoti]

+WiseOwlTutorials thanks for your valuable help, I need if response is bank value is 0. So I can calculate total no. like if ans is right calculate 2 points if ans is wrong calculate -1 if ans not given by participant calculate 0 the will calculate total points.

[@SantoshBudakoti]

+WiseOwlTutorials It's work perfectly, great, Lot of thanks, I am waiting form 2 day's for this. Once again thanks

[@sasonzade3004]

Hi! The subjects that shown between the movies is bot visible. ,for me it doesn't matter but it may disturb others.. Thanks!

[@grigoriefimovic1547]

Hi there Andrew...based on this video, i realize that the Property Get statement is just like a regular function....I've used it in place of functions, after viewing this video. How is it different though? Thanks

[@grigoriefimovic1547]

Much thanks Andrew...

[@erickdanielpaz95]

Does anyone know how to fix this issue: When I add the " Call ReaDataFenix" that will be equals to "Call ReadFilmDetails" it stop working. Public Property Let FormDataMode(Value As FormDataModes) pFormDataMode = Value If Value = AddMode Then Addcmd.Enabled = True ElseIf Value = EditMode Then Addcmd.Enabled = False CurrentRecordId = 1 End If End Property Please help

[@MrvladivostokMr]

I'am getting runtime error on this line : FilmDateScroll.Value = CLng(CDate(CurrentIDCell.Offset(0, 3).Value))

[@MrvladivostokMr]

this is happening in Private Sub ReadFilmDetails()...any idea ?

[@WiseOwlTutorials]

Hi there, my best guess is that the value you're referring to isn't being interpreted as a date. Which runtime error and message are you receiving?

[@nuhzakir8896]

Thank you.

[@nuhzakir8896]

+WiseOwlTutorials Your accent is different so I have trouble understanding you but thanks again.

[@nuhzakir8896]

***** No!I have been subscribing your channel for long time and love your videos.Problem is on me! :) English is not my mother language and I think so you are a British.I can understand easly american accent but the British Accent a little bit different.Anyway thank you for all your videos and tutorials.I'm following you.Best regards.

[@tumzarelaxing]

ANDROID PHONE FILE SYSTEM ACCESS IN VBA ????? Please how can i access the Android Phone "MTP" files and folders using VBA, i want to copy my Phone Files in a Particular folder and tryed every where in Google Stackoverflow.com know ones know how to do this. This is due to fact that android does not use Drive later instead they use Media Tranfere Protocol. Please please how can i accomplish this, i head that others are using some complecated ways of C#, then convert it. but i dont even know C#, let alone VBA just learned through your courses on Fso file handling tutorial. but this is a problem for me.(i'm using galaxy note3)

[@caribeiro77]

First of all, awesome tutorial as always... Know something completly of the topic... My properties window is not docked bellow the project explorer window anymore, i try to dock it but i'm not having success... Can somebody tell me how to dock it again, bellow the project explorer window? Thank you all... PS: Sorry for the total and completly of topic question...

[@caribeiro77]

+WiseOwlTutorials thanks a lot.....it's in place now....Finally... :-) Thank you.. Love your channel...

[@wattjock2405]

I realize that I am way behind on the posting of this video but, lazily, do not want to scroll through all of the comments to see... if no one has brought it to your attention yet, It appears to me that the Edit Films code breaks @ Private Sub ReadFilmDetails() FilmCertificate.ListIndex = WorksheetFunction.Match(CStr(CurrentIdCell.Offset(0, 4).Value), wsLists.Range("A2:A8"), 0) - 1 if a Film has the Certificate set to an MPAA Rating.

[@WiseOwlTutorials]

Wow, you must be the first person to get that far through the video! And yep, it looks like I neglected to store which rating system was used. You can do something like this in the ReadDetails procedure: If Not wsLists.Range("A2:A8").Find(CStr(CurrentIdCell.Offset(0, 4).Value)) Is Nothing Then UseUkRatings.Value = True FilmCertificate.ListIndex = _ WorksheetFunction.Match(CStr(CurrentIdCell.Offset(0, 4).Value), _ wsLists.Range("A2:A8"), 0) - 1 Else UseUSRatings = True FilmCertificate.ListIndex = _ WorksheetFunction.Match(CStr(CurrentIdCell.Offset(0, 4).Value), _ wsLists.Range("D2:D6"), 0) - 1 End If I hope it helps!

[@wattjock2405]

@@WiseOwlTutorials ~ thanks for the reply... I will try this out! Apologies for the late response, I've had a few things keeping me from my education this week. Should be back at it next week though!

[@WiseOwlTutorials]

@@wattjock2405 No apology necessary, we've all had weeks like that!

[@yeuduongtamhuong]

Public CurrentRecordID as long