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:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,743
Office Version
  1. 2010
Platform
  1. Windows
The message box will appear everytime you call celchange, this is because you set the boolean b=TRUE and never set it to false. so this line:
VBA Code:
If b Then MsgBox "Mandatory Cells Empty"
always triggers. When do you want the message box to appear?? Did you intend to only set the message box if this statement is triggered?
VBA Code:
If cel2.Value = "" Then
if that is the case try this modification:
VBA Code:
b=False   
For Each cel2 In Range("A1:A24")
If cel2.Value = "" Then
    cel2.Offset(0, 3).Value = ""
    b = True
    End If
 

atif_ar

New Member
Joined
May 31, 2014
Messages
30
Dear,
Thanks for the reply but dear still by making b=false still Msgbox is appearing whether A1:A24 is empty or not. Kindly advice
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
Trial moving the enable events code out of the sub and into the worksheet change event. HTH. Dave
 

atif_ar

New Member
Joined
May 31, 2014
Messages
30

ADVERTISEMENT

Trial moving the enable events code out of the sub and into the worksheet change event. HTH. Dave
Dears,

Thanks for the reply and efforts but still doing this only 1 time code works second time msgbox completely not appears.

Kindly you are people are expert help me out on this

Regards

Atif
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
That is some confusing code. It seems like if some value in "D1:D24" changes and causes an update/change to the value of G6, C4 or C9 then you want the sub to run which removes the row value from "D" if the "A" same row value is blank as well as initially blanking values from the other range if null values are found... these could also include the row values from "D" if "O" values are blank. Anyways, if that's what U want this seems to work. HTH. Dave
Code:
 Sub celchange()
Dim cel As Range, cel2 As Range, b As Boolean
For Each cel In Range("o1:eek24")
    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 = ""
    b = True
    End If
Next
If b Then MsgBox "Mandatory Cells Empty"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D1:D24")) Is Nothing Then
Select Case Target
    Case Range("G6")
        Call celchange
    Case Range("C4")
        Call celchange
    Case Range("C9")
        Call celchange
    Case Else
        'Do nothing
End Select
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
ps. If there is a "D" removal due to a blank in "A" then the msgbox only shows once.
 

atif_ar

New Member
Joined
May 31, 2014
Messages
30

ADVERTISEMENT

Thanks for the reply dear. I tried dear but still message is appearing two time even column A is empty or no
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
Code was tested before posted so I have no idea why it is not working for you? Dave
 

atif_ar

New Member
Joined
May 31, 2014
Messages
30
Dear,
I think a simple code will help me. I need a code which looks in column O1:O24, A1:A24. If i make selection in D1:D24 any text or value then if each cell in O1:O24 is < 1 then D1:D24 will be empty, if each cell in A1:A24 is empty then again D1:D24 empty and with message box mandatory cell empty.
Note: This is all in corresponding/parallel cells for example if O1<1 OR A1 is Empty the D1 is Empty and so on.
This only code i think will help
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
I explained what your code was doing. What your asking for is different. Again code tested and works. Dave
Code:
Sub celchange()
Dim cel As Range, cel2 As Range, b As Boolean
For Each cel In Range("o1:o24")
    If cel.Value < 1 Then
    cel.Offset(0, -11).Value = vbNullString
    End If
Next
For Each cel2 In Range("A1:A24")
    If cel2.Value < 1 Then
    cel2.Offset(0, 3).Value = vbNullString
    b = True
    End If
Next
If b Then MsgBox "Mandatory Cells Empty"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D1:D24")) Is Nothing Then
Call celchange
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,998
Messages
5,628,043
Members
416,289
Latest member
Jbelisari

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