For loop with msgbox one time output vba

atif_ar

New Member
Joined
May 31, 2014
Messages
31
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:
Dear,
Really appreciate your efforts. But still msgbox is appearing 2 times even A1 is empty or not
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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
Back
Top