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

[shanzek]

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

 

[Smitty]

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]

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:

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]

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]

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]

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]

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]

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]

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