The secret to this code is the Intersect method. Because you don't want to save the worksheet when any old cell changes, you use the Intersect method to determine if the target cell (the cell that changed) intersects with the range specified as the trigger range (C5:C16 in this case). The Intersect method returns one of two. How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change. That will allow us to run some VBA code whenever the Cell G7 contains the word. Excel VBA – Macro Runs When Worksheet Changed. Change Macro. By using Event code in Excel VBA. Of lines of code, so it only runs when cell B5 is. Hi, As the Excel Guru at work, I am often asked to do odd things. (Excel related, all other odd things are usually meant as a joke). So when someone phones me out of the blue and asks questions I am unsurprised, for I am recommended as a go to guy. ![]() ![]() I mention all this so that you know I am not entirely stupid. I need an action to occur when a cells in a range are changed. I have this as script on the the Worksheet. Private Sub Worksheet_Change( ByVal Target As Range) If Intersect(Target, Me.Range( ' C3:C5')) Is Nothing Then Pork End Sub And the Macro (Called 'Pork', don't ask.) Sub Pork() MsgBox ' Change' Sheets. Add After:=Sheets(Sheets.Count) Sheets( 1).Select End Sub This is held in the Modules section. So as far as I can see it should work. What I require is that when cells in the range are changed, a new tab opens with the name of the text in the relevent cell. (I know the example shown doesn't do this, I am working on the creation of the tab before I get into the nitty gritty of the macro). Am I being dim? I hate that this doesn't allow for proper line spacing and makes the code less readable, so I've inserted comment markers to break it up (this is my first ever reply, so I know of no other way to do it). Personally, I'd set aside a specific cell to enter the sheet name, then do it on a button event to have more control over what happens (what if they make a typo?) My functions are public as I'd usually have 'utility' functions in a global module to keep the code tidy and made available to the whole project. I like to control absolutely everything, so I didn't even know about using 'Intersect', so I would've written it like this: public const topRangeRow = 3 public const bottomRangeRow = 5 public const rangeColNum = 3 ' target.column doesn't work with letters ' ' determines if the sheet already exists - good error checking!
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
April 2018
Categories |