hi sir - this is a great help I was able to create a pop up text box already and made it visible to a specific cell. This time, I also want to display whatever value or text i entered on the cell to the pop up textbox whenever i click on it. so that whatever I typed on the pop textbox, it will display the text or value on the cell at the same time, whatever has entered on the cell will still visible on the textbox once i clicked it.
hi alan, it is always a pleasure to watch your videos, and learn some great excel... regards colin, berlin - i have not forgotten the coffees i owe you :)
Hi, really well explained, it was a great help, but if I entry data by doing copy and paste in multiple cells in a column, example: my Data validation is in column "K", Pop-up notification arrives and given data puts in column "L", there is no problem in it..... but when I'm doing entry in column "C" , suppose there is written "Apple" in cell no C2, I copied the value of C2 and pasted from C3 to C12, error 13 message pops up, how can I fix it?
Thank you for the video, If we change the value from delivery to collection, then how to change the address value as blank from some value. So, i mean every time when i update the column B value then column c value is not updating based on selection for column B
Hi Alan.. great demonstration. Learned from your VBA course, I would use: If Not Intersect(Target, Range("B:B")) Is Nothing Then.. on the Worksheet_Change event.. but totally understand you were keeping it simple and basic for this example. Just glad I've learned enough to know the difference.. haha!! Thanks again for all of your wisdom and insights.. Computergaga is great! Thumbs up!!
Thanks for your reply.. I mean to say amount. In your example if it is delivery then pop up will come and will ask to put address but in my case if it is misstatement then I have to put the misstatement amount but currently I am putting manually. I want this formula oriented. If I do it formula oriented it would be helpful for me
@@biswadeepsarkar5724 you can concatenate a formula or reference to a range in the message of a user form. The different techniques for this go beyond the scope of this comment. You will use the target cell to know which cell was changed so that you can reference the cell beside it or wherever the amount would be.
Good day sir, thanks very much for this video. Please I have a little issue; after entering a desire value target cell, the pop up window comes up. However when I enter an address and clicks OK button nothing is registered in the cell as expected. I'm some what confused where could I have gone wrong? Please help. Once again thanks for your understanding.
You can protect the worksheet with Activesheet.Protect. This would also mean that you may need to unprotect a sheet before you write the address into column C. If you want particular cells protected and others not. You could prepare this on the sheet first, or even record yourself doing it to generate code quickly and easily.
Hi. This is great macro, I am just having a problem where if i insert a row above it gives me a runtime error 13 (mistype match). Th debugger is highlighting the "If Target.Column = 10 And Target.Value = 2 Then" Please help if possible. Thanks
It is difficult to help without seeing what is going on, but a mis-match error indications a conflict with data types. I would check your variable data type for the 2 part. Maybe it is a text value on the worksheet and not a number. Or maybe you are using a string variable.
It depends on specifics, but typically a data type is different to one specified in the variable. My VBA course (link in video description) will take you through all this.
This would need to be checked on the Workbook close or save event. Just to check if a cell was left blank. Macros are the best way to do this. Data Validation can help, but is not perfect. You have to be careful at times forcing this type of behaviour but it can be done.
Hi Alan . This is not working when I make a selection from a combobox instead of a drop down list . I want the message box to appear on basis of certain selection I make from my comboxbox list
No worries. Is this a combo box form control on a sheet? You can right click the combo box and Assign Macro. From here you can create a new drop down change macros to check the selected value (combo box form controls use a cell link) and then show the appropriate message box.
it was helpful but we actually need sending auto sms to our phone number after every data entered or every time we sale anything and type it in MS excel.
Thank you . Hi i need same concept of choose list selecting specific"text" need to show user form popup, then that form having some two kind of choose list once selected that we need to give ok button that results are storing each and every row values at the same that sheet have data's paste or deleting runtime error 13 never shows i need that kind of coding
I am facing the same problem. When using the copy or paste option in the cells that are offset of our target column the runtime error 13 pops up. Any work around for that?
I have a list of 30 finished products which each has deferent amount of some of 40 ingredients and I am trying to create a chick list that result of showing the amount of the required ingredients for each of the checked in the list... Any advice on how to do it :)?
Sure, but it is a lot to explain here. As long as you have that list of how much of each ingredient for each product, there is no reason why we cannot count them for all checked items.
Lltllk look lo lo que it lot of loo oi OLLI my account number ollllll of l lo llllolllllllll ok ollllll ok lloollllllloooolllol op llllolllllllll you o lo loloool of look lolllol our ofl of OOO l oi looll lu o ooru ollllo look lo opportunity l lo llllolllllllll I lolll lo lo lo pot ooo ok loop ollllll l of lloo lo lllllllo OOO ok oi it looo lul ouroksn ownol our ll op lo looking at lll it ooo lol I o or a loloo little lolmyolllln my new et opportunity y opp bag lo oi IP op l loo on my own personal World in order has not received a letter o y
The cell that was changed can only be one cell. We can test if it is either of this columns with OR logic. If Target.Column = 2 OR Target.Column = 5 etc.
Sounds like the issue is with the 2, because you are receiving the error for a mis-matched data type. If the 2 is a text value, you will need the quotes "" in the code.
hi sir - this is a great help
I was able to create a pop up text box already and made it visible to a specific cell.
This time, I also want to display whatever value or text i entered on the cell to the pop up textbox whenever i click on it.
so that whatever I typed on the pop textbox, it will display the text or value on the cell at the same time, whatever has entered on the cell will still visible on the textbox once i clicked it.
Hi Alan... I stanched with your lovely way explaining us how to do what ... Thank you...
You're welcome. Thank you Talal.
Very cool. Thank you
You're welcome. Thank you.
This video is very useful for me. Thank you so much....😊😊😊😊
hi alan, it is always a pleasure to watch your videos, and learn some great excel... regards colin, berlin - i have not forgotten the coffees i owe you :)
No problem Colin. We shall do it again some time.
te mereces como mil quienentos likes
Great video! Thank you. That's gold!!!
Thank you, Iván
Hi, really well explained, it was a great help, but if I entry data by doing copy and paste in multiple cells in a column, example: my Data validation is in column "K", Pop-up notification arrives and given data puts in column "L", there is no problem in it..... but when I'm doing entry in column "C" , suppose there is written "Apple" in cell no C2, I copied the value of C2 and pasted from C3 to C12, error 13 message pops up, how can I fix it?
Very good tips Alan. As usual. Thank you
Thank you Angelo.
Thank you for the video, If we change the value from delivery to collection, then how to change the address value as blank from some value. So, i mean every time when i update the column B value then column c value is not updating based on selection for column B
Hi Alan.. great demonstration. Learned from your VBA course, I would use: If Not Intersect(Target, Range("B:B")) Is Nothing Then.. on the Worksheet_Change event.. but totally understand you were keeping it simple and basic for this example. Just glad I've learned enough to know the difference.. haha!! Thanks again for all of your wisdom and insights.. Computergaga is great! Thumbs up!!
Thank you Wayne.
Exactly what I needed, explained so well! Thank you!
You're welcome Natalia.
Hi. What if I need a particular cell instead of a column to be monitored for user input? Thank you. :)
Hello sir, if possible for you then make a video where Automatically macro runs when cell formula result changes ...👍
Thanks for the video Sir,....Really Useful.
You're very welcome, Swapnil. Thank you.
How can i make like this idea in Google sheet??
Difficult but interesting and useful thank you
You're welcome Afzal. Thank you.
Hi. How do you leave the address cell blank, if suddenly the user select collection after inserting the address location?
Thanks very informative video and very helpful but i need value instead of text how can i do that and how to change msg box title?
You're welcome, Biswadeep. Not sure what you mean by the value. For the title, select the userform and change the 'Caption' in the Properties window.
Thanks for your reply.. I mean to say amount. In your example if it is delivery then pop up will come and will ask to put address but in my case if it is misstatement then I have to put the misstatement amount but currently I am putting manually. I want this formula oriented. If I do it formula oriented it would be helpful for me
@@biswadeepsarkar5724 you can concatenate a formula or reference to a range in the message of a user form. The different techniques for this go beyond the scope of this comment.
You will use the target cell to know which cell was changed so that you can reference the cell beside it or wherever the amount would be.
Good day sir, thanks very much for this video. Please I have a little issue; after entering a desire value target cell, the pop up window comes up. However when I enter an address and clicks OK button nothing is registered in the cell as expected. I'm some what confused where could I have gone wrong?
Please help. Once again thanks for your understanding.
Thank you. How can you protect the particular cell after value is entered in the userform?
You can protect the worksheet with Activesheet.Protect. This would also mean that you may need to unprotect a sheet before you write the address into column C.
If you want particular cells protected and others not. You could prepare this on the sheet first, or even record yourself doing it to generate code quickly and easily.
Can we use both options like ( collection & delivery)
Sure. This is OR logic so use the word Or between the conditions instead of And.
I don't have the Form object available to insert. I can only insert a Module or Class Module
ok, is this a Mac maybe.
Can i also add a new value to the cell that was changed, in this example it would put the text i want into the delivery cell
If you put it back in the same cell, it would trigger the Change event again.
Hi. This is great macro, I am just having a problem where if i insert a row above it gives me a runtime error 13 (mistype match).
Th debugger is highlighting the "If Target.Column = 10 And Target.Value = 2 Then"
Please help if possible.
Thanks
It is difficult to help without seeing what is going on, but a mis-match error indications a conflict with data types. I would check your variable data type for the 2 part. Maybe it is a text value on the worksheet and not a number. Or maybe you are using a string variable.
For some reason, i received run-time error ‘13’ type mismatch. Do i need to use “Dim” .... sorry i am very new to VBA. Hope you can help!
It depends on specifics, but typically a data type is different to one specified in the variable.
My VBA course (link in video description) will take you through all this.
is it possible to force the user to fill a cell x and not leave it empty, if cell y has a value (not like optional comment or information)
This would need to be checked on the Workbook close or save event. Just to check if a cell was left blank.
Macros are the best way to do this. Data Validation can help, but is not perfect. You have to be careful at times forcing this type of behaviour but it can be done.
Hi Alan . This is not working when I make a selection from a combobox instead of a drop down list . I want the message box to appear on basis of certain selection I make from my comboxbox list
No worries. Is this a combo box form control on a sheet? You can right click the combo box and Assign Macro. From here you can create a new drop down change macros to check the selected value (combo box form controls use a cell link) and then show the appropriate message box.
it was helpful but we actually need sending auto sms to our phone number after every data entered or every time we sale anything and type it in MS excel.
I am using sheet 3 in my workbook and at the end when i click ok button it shows error 424 object required plz help ASAP
Thank you . Hi i need same concept of choose list selecting specific"text" need to show user form popup, then that form having some two kind of choose list once selected that we need to give ok button that results are storing each and every row values at the same that sheet have data's paste or deleting runtime error 13 never shows i need that kind of coding
I am facing the same problem. When using the copy or paste option in the cells that are offset of our target column the runtime error 13 pops up. Any work around for that?
I have a list of 30 finished products which each has deferent amount of some of 40 ingredients and I am trying to create a chick list that result of showing the amount of the required ingredients for each of the checked in the list... Any advice on how to do it :)?
Sure, but it is a lot to explain here. As long as you have that list of how much of each ingredient for each product, there is no reason why we cannot count them for all checked items.
Lltllk look lo lo que it lot of loo oi OLLI my account number ollllll of l lo llllolllllllll ok ollllll ok lloollllllloooolllol op llllolllllllll you o lo loloool of look lolllol our ofl of OOO l oi looll lu o ooru ollllo look lo opportunity l lo llllolllllllll I lolll lo lo lo pot ooo ok loop ollllll l of lloo lo lllllllo OOO ok oi it looo lul ouroksn ownol our ll op lo looking at lll it ooo lol I o or a loloo little lolmyolllln my new et opportunity y opp bag lo oi IP op l loo on my own personal World in order has not received a letter o y
What if target range is column B, column E and Column H. How do I define the Target Range? Multiple and non adjacent columns.
The cell that was changed can only be one cell. We can test if it is either of this columns with OR logic. If Target.Column = 2 OR Target.Column = 5 etc.
@@Computergaga thank you Sir 🙏
You're welcome Akash.
Sorry my column is the 10th and the condition is "2"
Sounds like the issue is with the 2, because you are receiving the error for a mis-matched data type. If the 2 is a text value, you will need the quotes "" in the code.
boring
Ouch!