Run macro after enter a value in cell within Sub Worksheet_SelectionChange

[cgcamal]

Hi everyone,

I have the code below that runs Macro1 when I select cell within the range $B$7:$I$1000.

But I want to run Macro1 If I enter a value in selected cell, not only when is selected, because Macro1
is intended to copy the value I enter to other cell.

How can I do this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
[COLOR=Green]'target range to monitor[/COLOR]
If Not Intersect(Target, Range("$B$7:$I$1000")) Is Nothing Then

  On Error Resume Next

Application.EnableEvents = False

    Addr = Target.Address
    
    If IsEmpty(Range(Addr)) Then [COLOR=Green]'Verify if current cell selected is empty
       'Do some code[/COLOR]

       [B] [COLOR=Navy]Call [/COLOR]Macro1 [/B][COLOR=Green]'I want to run this macro if data is entered in Target cell[/COLOR]
    End If
  On Error GoTo 0

Application.EnableEvents = True

End If

End Sub

Many thanks in advance.

Best regards

 

[GTO]

Greetings,

Is this close?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("$B$7:$I$1000")) Is Nothing _
    And Target.Count = 1 Then
        If Not Target.Value = vbNullString Then
            Application.EnableEvents = False
            Call Macro1
            Application.EnableEvents = True
        End If
    End If
End Sub

Hope that helps,

Mark

 

[cgcamal]

Hi GTO,

Thanks for your reply. The option you gave me It works, but I think is needed some code to complete what I want.

With your modification the Macro1 runs when selected cell contains data, but I need to happen this:

1-) Select any cell (now Sub Worksheet_Change will run automatically)
2-) Enter value in selected cell (like put stand-by Sub Worksheet_Change)
3-) And once the values is entered, run macro Macro1.

I hope be clear enough.

Thanks in advance.

Regards

 

[GTO]

With your modification the Macro1 runs when selected cell contains data, but I need to happen this:

Please try and state what is happening, and/or what you want to happen with more clarity. Are you saying that macro1 is called when you change the value of a single cell in the appropriate range? That is what is supposed to happen.

1-) Select any cell (now Sub Worksheet_Change will run automatically)

No, worksheet_change is not called until you have changed the value(s) of a cell or cells. The Worksheet_SelectionChange event would occur.

2-) Enter value in selected cell (like put stand-by Sub Worksheet_Change)

I am sorry, but that is making no sense to me. What do you mean?

3-) And once the values is entered, run macro Macro1.

I believe we have that part already happening, right?

Mark

 

[cgcamal]

Hi Mark,

Sorry for my unprecise explanation.

What is happening now is:
1-) I select a cell (Worksheet_Change becomes active)
2-) If selected cell is not empty, Macro1 runs,
3-) But if cell is empty and I enter a value, when I hit "Enter" button, Macro1 doesn't run.

What I want is:
1-) I select a cell (Worksheet_Change becomes active)
2-) Enter a value in selected cell (independently if selected cell contains data or not)
3-) When I confirm the value with "Enter" button or with "Ctrl+Enter" o moving arrows I want the Macro1 runs.

For that reason I wrote like putting on hold the Worksheet_Change until I finish to enter the value in selected
cell and after this happens, run Macro1.

I hope to be more clear this time.

Many thanks so far.

Regards.

 

[GTO]

Please show us all the code in the ThisWorkbook module, as well as Macro1. Maybe I can understand from that.

Thank you,

Mark

 

[cgcamal]

Hi again Mark,

The are 2 books (you can download them from here CopyDataToSummary.zip):
1-) Tables.xlsm
2-) Summary_File.xlsx (I want to add in this book in next empty row in any of 2 sheets, the values entered in sheets of Tables.xlsm)

The expected behaviour is:

File Tables.xlsm contains the "Sub Worksheet_Change" and 2 more macros, "Sub Computers()" and "Sub Printers()",
each macro copy new entered values in sheets "Computers" and "Printers" of Summary_File.xlsx respectively.

I mean, when I write "Computers" in column B in sheet "Table1" of Tables.xlsm, the new data must be copied in
"Computers" sheet of Summary_File.xlsx and "Sub Computers()" should run. If I write "Printers" in column B in sheet "Table1"
of Tables.xlsm, the new data must be copied in "Printers" sheet of Summary_File.xlsx and "Sub Printers()" should run.

In Tables.xlsm, is needed to enter "Computers" or "Printers" first in column B to know in which sheet of "Summary_File.xlsx"
must be added the values enterered from columns C to G.

As you can see, if you try to add values following the rules above, the data actually are added in respective sheet of
"Summary_File.xlsx", but not in the precise moment, I mean, not when you enter a new value and hit enter.

Example for Table1, C7 in Tables.xlsm (assuming B7="Computers"):
The new value in C7 is added in next empty row in "Computers" sheet of "Summary_File.xlsx" only when:
1-) You enter a value in C7 (let say you type Dell in C7)
2-) Hit "Enter or Intro" button (active cell now is C8)
3-) Select again C7 (now is not empty, C7=Dell)
4-) Dell has been copied in next empty row in column C in "Computers" sheet of "Summary_File.xlsx".

What I want is that the data entered in C7 be copied just in step 2, just when you finish to hit "Enter" or
"Ctrl+Enter" or "Move arrows", without the need to select again C7.

I hope you understand better with this explanation and having the 2 files to try.

Much appreciated for your help and time.

Best regards

 

[GTO]

Hi Cesar,

Fortunately I was able to get the download, most personal storage sites are restricted on our system. I am still not sure, but maybe this is close. I changed a bit of the code; if it works but you have any issues understanding it, post back and I'll try to comment the code (time permitting) tomorrow.

Hope this works...

Option Explicit
    
Dim bolWBMissing As Boolean
Dim CL As String, wbk As Workbook, Ad As String
Dim NxEmptyRow As Long, ShName As String
    
Private Function GetSourceWB(ByVal WBPath As String, ByVal WBName As String, ByRef WB As Workbook) As Boolean
    
    On Error Resume Next
    Set WB = Workbooks(WBName)
    On Error GoTo 0
    
    If WB Is Nothing Then
        If Not Right(WBPath, 1) = Application.PathSeparator Then
            WBPath = WBPath & Application.PathSeparator
        End If
        Set WB = Workbooks.Open(WBPath & WBName)
    End If
    
    If Not WB Is Nothing Then
        Set wbk = WB
        GetSourceWB = True
    End If
End Function
    
Private Sub Worksheet_Activate()
    
    If wbk Is Nothing Then
        If Not GetSourceWB(ThisWorkbook.Path & "\", "Summary_File.xls", wbk) Then
            
            MsgBox "I was unable to return the source workbook, so I" & vbCrLf & _
                   "won't update when a cell is changed...", _
                   vbInformation Or vbOKOnly, vbNullString
            bolWBMissing = True
        End If
    End If
End Sub
    
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet, rng As Range
    
    If wbk Is Nothing Then Call Worksheet_Activate
    
    If Not bolWBMissing _
    And Not Intersect(Target, Range("B7:I1000")) Is Nothing _
    And Target.Count = 1 Then
        
        On Error Resume Next
        Set wks = wbk.Worksheets(Cells(Target.Row, 2).Value)
        On Error GoTo 0
        
        If wks Is Nothing Then
            MsgBox """" & Cells(Target.Row, 2).Value & """ does not exist!", vbCritical, vbNullString
            GoTo BailOut
        End If
        
        wks.Cells(wks.Cells(wks.Rows.Count, "A").End(xlUp).Offset(1).Row, Target.Column).Value = Target.Value
    End If
BailOut:
End Sub

Mark

 

[GTO]

ACK! PS. The workbooks need to be in the same folder for test...

 

[cgcamal]

Mark,

You're more than close. I think you've done, and the main key to get what I wanted is change "Worksheet_Selection_Change" by "Worksheet_Change" how you've done.

I was wrong using Selection_Change event, because of that, the code only it was working when I was selecting the cell after enter a value

From your code I'm learnig several things, a way to do it with a small code, using functions and including error handlings, nice.

Besides that, I've learned more than one thing from the line that copies the target.value. I didn't know cells command could be used it with syntax Cells(Row number,"Column Letter") and I've learned how to use offset in the same sentence when looks for last row (or next empty row in this case)

I've only added an IF statement to avoid copy Items name (Computers or Printers) because they work as trigger only to know in which sheet data must be copied.

If Target.Column <> 2 Then
    wks.Cells(wks.Cells(wks.Rows.Count, "A").End(xlUp).Offset(1).Row, Target.Column).Value = Target.Value
   End If

At the beginning I was trying to update Summary_File.xlsx having it closed, but even when I got succesfull tests doing simple change of cells in a closed workbook with a macro I found that combines VBA and SQL commands, but I was'nt able to update Summary_File.xlsx with all conditions needed.

Well, one more question:

In real file I have more than 50 "Table" sheets, do you know how to execute this code in every sheet without copy it into every sheet module?

or if it is needed to insert the same code in every sheet module, do you know a macro to copy the same sheet module into the other 49 sheets modules?

Many thanks, really, I've learned so much with your solution.

Best regards