Excel 2007 VBA - On Change Event

Question

4.50/5 (2 votes)

See more:

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...

VB

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...)

MSIL

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?

Posted 9-Apr-11 3:56am

Dalek Dave

Updated 9-Apr-11 3:57am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-04-09T04:10:00

Solution 1

So you mean you need to get the data from the modified cell? If that's correct, could you check that the range is just a single cell (target.count = 1). When that's true, take the Target.Text and use that for the new sheet name. Something like

Set newSheet = Sheets.Add(...)
newSheet.Name = textFromRange;

Or did I completely misunderstand the question :) ?

Posted 9-Apr-11 4:10am

Wendelius

Comments

Dalek Dave 9-Apr-11 10:39am

No, I just need to run the macro on a change of cell content event.
Within the macro I can do all I need, I just can't get the macro to run.

Wendelius 9-Apr-11 10:47am

Ok, sorry. Is the Worksheet_Change sub under correct worksheet? Tested your code and it's working fine, If I modify a cell outside C3:C5 a new sheet is added.

Wendelius 9-Apr-11 11:08am

And another thing, the macros are enabled in the workbook?

Dalek Dave 9-Apr-11 11:36am

Oh God! I just realised what I have done wrong... Thanks, Hmmm, I really don't believe I did that!

Wendelius 9-Apr-11 11:40am

Glad you got it solved :)

Dalek Dave 9-Apr-11 12:06pm

Normally I would be doing this on my work machine, which is set up for this. As I am at home, it didn't occur that the damn thing wasn't set correctly! - That'll teach me to pay attention.

elbatman · Answer 2 · 2011-04-14T10:01:00

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!
Public Function sheetExists(ByVal sheetName As String) As Boolean
On Error Resume Next
'
Dim findSheet As Worksheet
'
   Set findSheet = Worksheets(sheetName)
'
   sheetExists = Not (findSheet Is Nothing)
'
End Function
'
' combines the error checking into a single function that tells you if it has been successful
Public Function createSheet(ByVal sheetName As String) As Boolean
On Error Resume Next
'
Dim newSheet As Worksheet
'
   If (Not sheetExists(sheetName)) Then
'
      Set newSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'
      If (newSheet Is Nothing) Then
'
         createSheet = False
'
      Else
'
         newSheet.Name = sheetName
'
         createSheet = True
'
      End If
'
   Else
'
      createSheet = True
'
   End If
'
End Function
'
Private Sub Worksheet_Change(ByVal Target As Range)
'
' determine if the edited cell is within the range you want
'
   if ((target.row >= topRangeRow) and (target.row <= bottomRangeRow) and (target.column = rangeColNum)) then
'
      If (not createSheet(mySheet.cells(target.row,target.column))) Then MsgBox "Could not create sheet"
'
   end if
'
End Sub