For loop with msgbox one time output vba

atif_ar

New Member
Joined
May 31, 2014
Messages
30
Hi All,

I am using For loop in macro which called in worksheet change event but issue is my msgbox is appearing 2 times means one on Mandatory cell empty and again even on filling the cell. Kindly help

Macro

VBA Code:
Sub celchange()

Dim cel2 As Range, b As Boolean

Application.EnableEvents = False
For Each cel In Range("o1:eek:24")
    If cel.Value < 1 Then
    cel.Offset(0, -11).Value = ""
    End If
Next
For Each cel2 In Range("A1:A24")
    If cel2.Value = "" Then
    cel2.Offset(0, 3).Value = ""
    End If
    b = True
   
Next

If b Then MsgBox "Mandatory Cells Empty"
'
    Application.EnableEvents = True

End Sub


Worksheet Change

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("D1:D24")) Is Nothing Then
        Select Case Target
            Case "G6"
                Call celchange
            Case "C4"
                Call celchange
            Case "C9"
                Call celchange
            Case Else
                'Do nothing
        End Select
    End If
   
End Sub

Waiting for your valuable help
 
Last edited by a moderator:

atif_ar

New Member
Joined
May 31, 2014
Messages
30
Dear,
Really appreciate your efforts. But still msgbox is appearing 2 times even A1 is empty or not
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
You must have further code or formulas that are causing unknown recursion. If U want to trial my code, start a blank wb fill A1:A24 and o1:eek:24 with numbers larger than 1. Place any values in and D1:D24. Then copy and paste the code in the workbook. Trial making any number in o1:eek:24 or A1:A24 less than 1 and then make a change to any cell in the range D1:D24 It works for me so I really don't understand what's going on for U? Dave
edit: :eek: spooky it's supposed to be o1 : o24
 

Watch MrExcel Video

Forum statistics

Threads
1,128,063
Messages
5,628,379
Members
416,314
Latest member
Dan99321

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