Call Sub, if there is a Value in a cell

[M_Gross]

Hi everyone,

I want to call a Sub, if there is change in a certain cell. With the following code, this works as lang as there is something in the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("A1") Or Target = Range("A2") Then Call "Name of Sub"
End Sub
[CODE]

However, if there is nothing in the cell "A1" or "A2", I get a Problem. Anyone an idea, how to solve that?

 

[My Aswer Is This]

Try something like this:
See line two of code says if cell is empty exit sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Call Meee
End If
End Sub

 

[dmt32]

Hi,

try following update to your code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
        If Len(Target.Value) > 0 Then Call NameofSub
    End If
End Sub

note: Code assumes that cell changes are made directly by user or code. Changes by Formula do not trigger this event.

Dave

 

[My Aswer Is This]

Not sure what you will be entering into the cells.

But you can run numerous scripts just by entering their names into a cell
Or Easier yet have a Data validation list in lets say "A1"
Have the sub names in the Data Validation list
When you choose the Sub name from the list that sub will run

Try something like this if that is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 5/28/18 10:30 AM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim ans As String
ans = Target.Value
Application.Run ans
End If
Exit Sub
M:
MsgBox "There is no sub named  " & ans
End Sub

 

[M_Gross]

Thank you very much for your fast answer!
I have still problems, maybe because sometimes there is a text and sometimes a numer in the cell.

 

[My Aswer Is This]

What are you entering into the cell?
You are using a cell change event but have never said what your entering in the cell.

So what must be entered into the cell to get your macro to run?

 

[M_Gross]

The makro just changes the headline, but the porblem is that I can not say if it is a number or a text that will be entered it the cell. There are Project IDs entered, which are different. Sometimes it is a serial number and sometimes a string.

 

[M_Gross]

I solved it in the way:

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Not Intersect(Target, Range("A1")) Is Nothing) Or (Not Intersect(Target, Range("A2")) Is Nothing) Then
        Call "Name of macro"
    End If
End Sub

 

[My Aswer Is This]

Glad to see you have things working for you.
I guess you want to always run the same Macro every time.