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.
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 !!!
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!
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 🤩😇
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
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.
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
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
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.
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.
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.
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.
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.
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 !
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?
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.
Hello, I'm learning VBA and you are an excellent teacher, I've a question:- If Target.Comment is Nothing Then Code Else Code So when you click on a blank cell that hadn't contained any comment why it is not auto commented before typing hello. Even whenever I'm clicking on any cell the comment has been added without writing anything in the cell.
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...
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
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?
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.
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
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, 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...
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!
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 :(
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!
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
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.
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
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
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!
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
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.
With your effort in these videos, I really felt that I became a professional, thank you very much
Thanks Aamir, happy to hear that the videos have helped you, thanks for watching!
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.
Thank you Sayyad, I appreciate the comments and thank you for watching!
I simply love this series. Truly remarkable with a tinge of English humor so that the learning experience never gets boring. Thanks a lot!
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 !!!
Hi Naima, welcome back!
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!
As always - you're awesome.
I love how you go through every single problem every time and come up with solutions.
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!
Thank you sir that was great
You're very welcome Manoj, thank you for watching!
I have learnt a lot from your videos ...Thanks WiseOwl and Andre Gould....
Thank you, Karna!
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 🤩😇
Happy to hear that you're enjoying the videos! Sadly I don't think that VBA can help Arsenal now :/
The best vba tutorial👍👍👍👍🌹🌹🌹🌹
Wonderful tutorials Andrew! I've come so far with VBA. All thanks to you.
wow my mind was blown away from the ctrl+enter shortcut
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
Thank you very VERY much! *****
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.
My master you are the best of the best of best master, thanks you for share it with us.
Thanks Alexis! But stop, you'll make us blush! 😀
Thank you Andrew!!! Another marvelous video!
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
Always very good. Trying to adapt to visio application
Thanks and good luck!
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
Thank you so much for this series of videos. They are very instructive.
Just great, the best vba on net, Cheers
Thanks Janez, I appreciate the comments!
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.
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.
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.
Hi, thanks for these. Well done. What would be your recommendation as next steps or intermediate level learning after this?
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.
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.
Hope we ca get an answer on this...
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 !
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?
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.
Hello, I'm learning VBA and you are an excellent teacher, I've a question:-
If Target.Comment is Nothing Then
Code
Else
Code
So when you click on a blank cell that hadn't contained any comment why it is not auto commented before typing hello.
Even whenever I'm clicking on any cell the comment has been added without writing anything in the cell.
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...
Another amazing video.. :) Just one query, how is it that your 1st IF statement did not require and END IF Statement?
Great! Thank you for the clarification :)
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
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?
nice video. one question:16:36 how do you indent several lines?
Note to self...do not press CTRL + A when you have the loop for the target cells enabled....
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.
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
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.
That's great Andrew, thanks. I'll follow up on the links you've posted as well, much appreciated.
yah i enjoyed all of these training Video
Thank you Sir !! Your videos are very good..
Super information sir, thanks for your effort, God Bless You.
Thanks indeed for this brilliant video. 🌟 🌟 🌟 🌟 🌟
very good video, very informative
Your style more wonderful
Great video...
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...
Thanks so much...
Is it possible to have Event procedures call regular subroutine variables or functions in modules in the same workbook?
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!
Thank you for an interesting video!
Okay how can I use the Event to allow multi user at once
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 :(
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!
Awesome gúy
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
Can someone let me know how he has the three buttons at top-right corner for the worksheet minimizer, maximizer/retorer, closer shown?
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.
08:47 I love this Joke, May be you can share Top 10 Joke of Excel Skill as a new Vedio😂
"There are also other things that can bizarrely happen in the life of the button" :D
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!
you are great
Thank you!
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!!!!!!!!!
At around 32:00 is anyone else finding when a cell is selected a blank comment is immediately created before they start typing?
That really does help! Many thanks!
thank you
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
@@WiseOwlTutorials yes done
Many thanks teacher
awsome!
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
Boss, Awesome
Hi
Any worksheet specific code wont seem to work for me ( the comment and the color trick) any help?
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!
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.
Another question for you my friend, is there any way to extract texts from the comment boxes of a massive number of cells at once?
+WiseOwlTutorials thank you very much my friend. I adjusted a little for my needs here and it is working fine. Awesome tip.
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
Minecraft in excel
Thank you