Run IfEmpty Macro Automatically

[rfernandes]

Hello,

I'm trying to get the following macro to run automatically when the cell of interest (A12) is not empty:

Sub DoStuffIfNotEmpty()
    If Not IsEmpty(Range("A12")) Then
        Sheets("CT2").Visible = True
        Else
        Sheets("CT2").Visible = False
    End If
End Sub

Right now, I have another macro that is successfully automatically enabled using: Application.EnableEvents = True

I'm wondering, does the former code not count as an "event" per se, and if not, is there any way I can still auto-enable it?

Thanks

[HaHoBe]

Hi, rfernandes,

what kind of code do you have or use so far?

Maybe just use the event behind the sheet with Range("A12") as a caller for the event (if the value is inserted manually which you didn´t mention):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A12")) Is Nothing Then
  Sheets("CT2").Visible = Len(Range("A12")) > 0
End If
End Sub

Ciao,
Holger

[rfernandes]

Hi Holger,

Sorry for the delayed response getting back; thanks for the help!

I have another worksheet_change event so initially, I ran into compile errors. With a little googling, I was able to get these items working by turning them into Event procedures and my code looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    EventProc1 Target
    EventProc2 Target
     
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Private Sub EventProc1(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A1:R60"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub EventProc2(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A12")) Is Nothing Then
  Sheets("CT2").Visible = Len(Range("A12")) > 0
End If
End Sub

This works, however, it requires that after I manually insert a value into "A12", I must select the A12 cell in order for the macro to run and sheet "CT2" to become visible. Is there any way for excel to notice I have entered in a value without having to select the cell post data entry?

[HaHoBe]

Hi, rfernandes,

Is there any way for excel to notice I have entered in a value without having to select the cell post data entry?

That´s what the Worksheet_Change-event does. And I wonder why you post a lot of code that doesn´t do what you want it to do on it´s own but not what you have in your Change-event. You can check for any range by intersect in your second procedure but I would start it on thre event that to my knowledge was created for the purpose.

Please, use Code-Tags as per Foum Rule #3 to display the code segments.

Ciao,
Holger

[arlu1201]

rfernandes,

I have added code tags to your posts 1 & 3. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

[rfernandes]

Thank you both for the further direction. Will use those code tags from now on..

Also I figured out where I was going wrong with regard to my last inquiry. My problem was that

after I manually insert a value into "A12", I must select the A12 cell in order for the macro to run and sheet "CT2" to become visible

My problem occured because I was using a Worksheet_SelectionChange event instead of your recommendation, a Worksheet_Change event.

Thank you for all your help on this problem, as well as the guidance on forum rules. Here is the successful code; this thread is SOLVED!

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    EventProc1 Target
    EventProc2 Target
     
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Private Sub EventProc1(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A1:R60"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub EventProc2(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A12")) Is Nothing Then
  Sheets("CT2").Visible = Len(Range("A12")) > 0
End If
End Sub