trigger macro or vba code when a specific cell is changed...

shanzek

New Member
Joined
Feb 23, 2004
Messages
19
This should be easy, and maybe I'm just missing something, but I want to run a macro or vba code when the user enters something into a specific cell and then moves to the next cell.

What is happening is that the user pastes a block of text into a cell, and it seems to really screw up the cell format. So when they move on to the next cell, I want to run a macro to reformat the cell I just left. In reality I have 4 specific cells they can enter data into, the rest are locked. The macro I have reformats all 4 cells, which is fine. I just can't figure out how to trigger it other than using a button...

THanks
Steve
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Re: trigger macro or vba code when a specific cell is change

Take a look @ Worksheet_SelectionChange() Event.
Occurs when the selection changes on a worksheet.

Syntax

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Target The new selected range.
Hope that helps,

Smitty
 

shanzek

New Member
Joined
Feb 23, 2004
Messages
19
Re: trigger macro or vba code when a specific cell is change

Do I need to do anything special to make this work?
Here's what I added:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Select Case Target.Address
    Case "$b$4"
        Stop
    Case "$b$6"
        Stop
End Select
MsgBox ("hit it")


End Sub
but it doesn't trigger...
I go to cell b4 and enter some info, then move to cell b6, and nothing happens.

Does it matter if other cells are locked, or if the sheet is protected?

Thanks
Steve
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
Re: trigger macro or vba code when a specific cell is change

It needs to be in the correct Worksheet module, not a standard module where you probably place most macros.

The easiest way to get to the worksheet module is go to the worksheet in Excel, right click on tab name, and select "View Code". The code needs to be pasted into this resulting window to be triggered automatically.
 

shanzek

New Member
Joined
Feb 23, 2004
Messages
19

ADVERTISEMENT

Re: trigger macro or vba code when a specific cell is change

Excellent! I knew I had to be missing something simple. I was putting it into module 1 with the other macros...
Thanks everyone.
Steve
 

jhoverton

New Member
Joined
Jul 19, 2006
Messages
4
Trying to follow the directions...

Sorry to be so dense...

I've tried to follow along here but what I'm trying to do may not be answered by this solution.

My task: I want to rename the worksheet when a cell changes, and I would like it to trigger automatically. I see the part about using Worksheet_SelectionChange but I can't seem to get anything to work.

The cell to read from is on Sheet1. The sheet name to change is Sheet3. I know no VBA. What am I missing, other than the obvious need for VBA education?

I appreciate any help in this solution.

Cheers,
Jay
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Welcome to the Board!

How's this for a start:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("A1")
             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Only look at that range</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> OhCrap
            Sheets(3).Name = rng.Value
            
OhCrap: <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It assumes that your sheet name is in A1.

Hope that helps,

Smitty
 

jhoverton

New Member
Joined
Jul 19, 2006
Messages
4
Thank you for the help...!

It's working... to a degree. I'm working with text, however, not numbers. Will the "Target.Count >1" element of this solution still work as designed?

Is there any way to have the change take place without having cell A1 opened and exited? Right now that's the only way to get the macro to activate. (A1 is linked to another cell on sheet1. Could I have this rename sheet3 when the cell on sheet1 is changed? What would the sheet.cell reference look like?)

I do appreciate your help.
Jay

How's this for a start:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Range("A1")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
On Error GoTo OhCrap
Sheets(3).Name = rng.Value

OhCrap: Exit Sub
End Sub

It assumes that your sheet name is in A1.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
The Target.Count part just prohibits the code form working if multiple cells are changed, i.e. copying a multiple cell range onto A1.

As for doing this as a result of cell calculation, that'll be easy, you need to use a Sheet Calculate event:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> AwwCrap
        Sheets(3).Name = Range("A1").Value

AwwCrap:     <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top