Simply the best vba teacher on the internet! Even who doesn't Know nothing about vba can learn. Only the great teachers can do this! Thank you very much.
you are one of the best teacher ...love the way you describe the code from simple to complex with every time solving the problems which may occur..hats off to you Andrew.
Sometimes I overlook what I perceive as the most simplest of code. However, your tutorials prove again how wrong my logic is! very informative tutorial. I must commend you on all that I have learned from Wise Owl. I have implemented much of what I have learned in my current job. I've used a lot of different Excel/VBA study guides through out the years, but your approach to teaching is the only course that has flicked a switch in me. I work for a German Company here in the US and they have brought in different companies in the past to teach Microsoft Office applications. The instructors never seem to convey the course material as you do. So again I must say, Thank You!
I get up early in the morning and often return late in the evening, but there's always at least half an hour a day, at any time, to watch one of your videos! I am never tired to learn from you. You are simply the best! Thank you!
Hi Andy; it's been a while, I had a lot of work and couldn't unfortunately take some time to watch your turorials and continue my VBA learning journey that I started thanks to your informative videos. But I am back now and fully prepared to pick up from where I left. thank you again for the amazing efforts !!!
I am watching the series even on Saturdays and Sundays - that is how engrossing it is. Thank you WiseOwl. 😃 P.S would be nice to write a Simple code for this season premier league: which prompts Arsenal to win it 🤩😇
Andrew, This is Awesome code, thank you for sharing! I keep many workbooks that I am incorporating this into as I have always struggled with the "Comment" functions. I've made a slight change on the Clear Comments sub to; Selection.ClearContents this will suit my needs as I track many changes. This will make my life a whole lot easier! Thanks again, Jimmy
This is the FIRST video I watch that shows me stuff that actually works! Great, concise, right-to-the-point! One thing though, I work with (almost exclusively) MS Word, and I'm using MS Word 2016. The declarations in the VBA editor are very few, I don't know why. Also, when I write the procedure for the BeforeClose/BeforePrint, for example (which do not exist in the drop-down menu,) event, they don't execute. +WiseOwlTutorials
Wow... What a nice class. Congrats once again and thanks to the tip in the handling errors video. I have no access to the Internet in my office that I simply forget that Google exist sometimes. Hahahahahahahhahaahahahah
Hi Andrew, I should say you are a great teacher. Learning VBA seems to be possible only because of your tutorials. Thank you so much for doing this great work. I need help in creating Mail Merge through VBA - that is after sorting a list of addressees, I would like to send them all email alerts. Your help will be much appreciated.
I can't Thank You Enough Rly I Can't, I've downloaded all ur videos just in case I got caught without internet.I wich that u can add more to this playlist something like designing a full program , anything but something
Many many useful examples, thanks. In one thought, what might be the best way to prevent the user from m adding a (new) table to my sheet with already one of my key tables. I'm thinking, as a chance check, if listobject count is greater than one, then don't allow LO creation
I think this should mitigate the problem: Dim ToColorCells As Range Dim CorrectedTarget As Range Dim SingleCell As Range Set ToColorCells = Range("A1:E10") Set CorrectedTarget = Intersect(Target, ToColorCells) If Not CorrectedTarget Is Nothing Then For Each SingleCell In CorrectedTarget SingleCell.Interior.Color = vbYellow Next SingleCell End If Of course we can improve this piece of code.
Again, amazing video... I am using mac excel 2011, most of the code is working fine. However, the code environ("username") is not working. Is there an alternative solution for mac? Also, the Add comment did not work, it does nothing when i change a cell's value. Please suggest.
First of all i wanna thank you so much for this video and i have a question. What is the difference Active X control with Form Control?At the end of video you added a command button from Active X Control,so if we wanted to add from Form Control,What would be the difference? Thanks.
if you're trying to add just 1 worksheet (by putting 1 inside the InputBox), then you get RunTimeError '1004'. It should be because it subtracts 1 after having already created 1 sheet... right?
Awesome video! I like how you covered errors that we might encounter. Question, I need to have a macro activate whenever a filter is added or changed. Any Ideas?
These are simply the best VBA Intro tutorials! Thanks for providing them. On the Event of adding mutiple sheets I tried asking for only 1 sheet and got an error. (HowMany -1 = 0) . But now even if I delete all the added code (save,close, and add the code again) and try it again - the code is just ignored when I click the add sheet button???? It just adds a sheet and doesn't display the Input Box??
***** Thanks for responding but oops - I think I was editing the wrong macro (Personal Module) because when I closed everything and did it a third time - all was well. Sorry to bother you but good to know that you actually keep up with your massive about of training videos! Thanks again.
Thank you for posting such an awesome video. One quick query, is there any video you posted which explains how to search specific "String" in one Workbook then pickup the data which is related to this String to another Workbook?... something similar to this?
Andrew, this is a terrific video, but I found myself getting hung up on your use of "SingleCell as range". I couldn't find any definition of that at all (msdn etc). Which means you've created it as a range variable, what bugs me is how is SingleCell defined to be just a single cell? You've just defined it to be a range, how does the "Single" part come into it? Sorry if I'm a bit late in replying, I must be the last person on the internet to have discovered these fantastic videos. The code I'm referring to is about 35:53 it is a Worksheet_Change event.
Hi Andrew, this video is excellent and it provided some fantastic solutions to several problems I have been trying to address in my model. I have a question (pls forgive if it a stupid one) I have macro that is in a module and it is designed to assign text color to cells containing formulas, cell references and hard coded inputs respectively. However, I have to select the range and then run the code for the cell contents to be formatted. Is there a way to have this done automatically when a given cell is updated/amended? Thank you very much.
Thx so much for all your tutorials Andrew ! I have a question reg this one (at around 16.30): I can't figure out why we need to add the following: "If TypeOf Sh Is Worksheet Then", because my understanding is that this event procedure would only be triggered by an additional worksheet, and nothing else. I might be missing something :) If you can clarify for me that would be great ! Thx again !
Thanks for such a good video. One question - has the "BeforeClose" event changed in Excel 2016? I set Cancel = True, got the "You're not leaving" msg, ok-ed once, tried to close it one more time workbook closed without "You're not leaving" prompted.
I am currently watching the Event Procedures video. I love these videos BTW! I was wondering on the msgbox part where you state you are not leaving is there another way you can get out of the file without using time? For example, I work at a manufacturing plant and there are times when my operators accidently closes a file this is perfect to use for that. however there are times when the power goes out and they bring up the AutoRecover file (which I do not want them to do but it happens anyways) and so they would have to exit that file to get back to the original and if I used that code they would not be able to get out of the file.
Your videos are very informative. Thanks a lot. I'm looking for VBA code for drop down list in excel which when run will select the values from drop down list one by one. Pls assist...
Hi Andrew, I have a question about the comments code. Can this be made an "Addin" or utilized from my Personal.xlsb workbook in my xlstartup folder? I would like to be able to toggle it on and off from the Quick Access toolbar. so far I haven't any success doing either one.
Hello Andrew. Thank you so much for this brilliant course. I'd like to make an observation. I just noticed that when I clear a cell from its content, vba treats this action as a change and hence adds a blank comment of it "only username and date". The same thing happened with you at minute 34:30 while you're pointing at cell A3; there are 2 comments, while that cell had only the value of "Yes". How can we work around this? Thank you!
for the followin code , if we want to add only one sheet by putting 1 in the dialogue (imput boxe) we will get an error, any comments please. Private Sub Workbook_NewSheet(ByVal Sh As Object) Dim N As Integer N = InputBox("How many worksheets do you want to add") Application.EnableEvents = False Worksheets.Add Count:=N - 1 Application.EnableEvents = True End Sub
Hi Mahdjoub, you can test the value the user has entered with an if statement and only add the extra worksheets if the value of N is greater than 1: If N > 1 Then Application.EnableEvents = False Worksheets.Add Count:=N - 1 Application.EnableEvents = True End If
any tips on how to stop worksheet_calculate from going on an infinite loop? I've set up a macro that is triggered after the worksheet recalculates but it keeps going in a loop and crashing... tried a few ways to stop it from doing that but nothing seems to work :(
Hi Oscar! The version of Excel used in the video is Excel 2010. Modern versions of Excel have only one set of minimize, maximize, restore buttons as far as I know.
Thanks for this :) Slightly easier way to append a new comment would be Target.Comment.Text Target.Comment.Text & vbNewLine & "new comment" - are there any hidden problems with doing it recursively like that?
Sorry, only meant for that line to replace the Start parameter of the Comment.Text method; the code in the video uses Start:=Len(Target.Comment.Text) + 1. Thanks for the quick reply on a years old video by the way!
I'm trying to stop the deletion of worksheets in a workbook. I used the BeforeSheetDelete event. And assigned a message box to display when the user tries to delete the worksheet, but I have struggling to write a line that stops/cancels the events when the user presses vbOk. I used the “cancel = true” but not working. Please can you come to my rescue 🙏
Hi! Yes, the BeforeSheetDelete event doesn't have a Cancel parameter so you can't do it that way unfortunately. One option would be to protect the workbook structure which prevents worksheets from being deleted (or moved, renamed, etc.). You might find this thread useful stackoverflow.com/questions/23645870/prevent-user-from-deleting-a-particular-sheet I hope it helps!
Hi Andrew, can you help me with some VBA? I am trying to add a msgbox to say "No Record Found" of the item_in_review serial.text doesn't appear in my worksheet. Private Sub SERIALSEARCH_CLICK() Do DoEvents Row_Number = Row_Number + 1 item_in_review = Sheets ("Name").Range("a" & Row_Number) If item_in_review = serial.text then And then I have a bunch of fields it will populate with data about a serial #. But I need it to also say if that serial # doesn't exist. THANK YOU IN ADVANCE!!!!!!!!!
Hi I've done additional method (or procedure) to your code related to worksheet selection change , and thats to resolve a problem happened to me when i sellected by fault the entire worksheet , the worksheet become not responding because of the looping process, so i resolved that by exiting the sub along with the condition "If the cell.row and cell.column limmited to 10 and 5 for example , and here's my modification for your comments please: Option Explicit Private Sub Worksheet_selectionChange(ByVal target As Range) Dim Rng As Range For Each Rng In target If Rng.Row < 10 And Rng.Column < 5 Then Rng.Interior.Color = vbYellow Rng.Value = "Selected" ElseIf Rng.Row >= 10 And Rng.Column >= 5 Then Exit Sub End If Next Rng End Sub
Very good - you can also test the CountLarge property of the Target object to check how many cells you have selected: If Target.Cells.CountLarge > 100 Then MsgBox "Too many cells selected" Exit Sub End If
Hi, I'm not sure why that's not working for you, feel free to download this example drive.google.com/open?id=1t5cplL7-XbO1zv9D9Pa7J96sL8_x4-H6 and check if works (you can click any cell on Sheet1 to change its colour and insert a new worksheet to have a value written into it automatically). Let me know if it works!
Hi, I have been following you and learning a lot. I'm really thankful to you. I do have a question: I have an excel with data validation lists in c11 and c19, based on code selected in c11, the drop down list in c19 changes to ones that are linked to that specific value in c11. I want to add Worksheet_SelectionChange so that when each time the value in c11 is changed, c19 value is cleared automatically. Can you plz help...
I have a question about a change event maybe someone can help me with... (I'm sorry in advance if this is obvious, this is my first attempt at VBA) I have a list of numbers (1-10) that users will select from and I could like to copy and paste a range of cells based upon their selection. I wrote If Range("E6") = 2 then range("G7","Y9").Copy Range("G11").PasteSpecial When 2 is selected from the list, the cells are copied and pasted in the correct location. However, an error message then appears that reads "there is already data here would you like to replace it." and then repeats itself over and over. After I select no, the error appears "PasteSpecial method of range class failed." If I put an Endif underneath, it will not run the code to begin with. Any help helps, thanks.
Sub test() Range("e8", "f8").ClearContents Dim thevalue As Integer thevalue = Application.InputBox("Please select or type the value ", " Selected value ", " Select or enter a value ", Type:=1) Select Case thevalue Case 2 Range("g11", "g12").Value = Range("g7", "y9").Value End Select End Sub
Do you know something your amazing explanation style same like the "Game of Thrones" writer style, you show us something and we said "oh that's Great this the one that we need " and then you show us another better one (Same like Game of Thrones they show you one guy he became the leader and the hero but then something will happen to this guy and the writer show you another one become the king and so and so :)
+WiseOwlTutorials No No I am sorry I didn't mean that... I mean the explanation is very exciting and pushing you to keep watching until you finish it Also showing you don't be happy because once you will learn a nice way after you will discover another excited one.:)) I really enjoyed this and last videos. Thanks a lot for everything.
message to self* do not click in excel and select all especially in a for each cell loop :):):). if you ever need someone to break excel, just give me a holler. I have become very good at that.mmm right after re typing my code and watching the video, you did pretty much say D O N O T S E L E C T A L L. LOL
