MsgBox appearing thrice in VBA?

[mehidy1437]

Hi Guys,

I'm working on below code, end result is okay, but I'm getting the MsgBox thrice.
After editing the cell when I press enter its giving the msg & have to press ok/enter another two times to deactivate the msg.

This code is running on "Worksheet" with change event.
Need your help to solve it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim addActiveCell As String, cellValue As String, count As Integer, offaddActiveCell As String
    Dim cwcyiwiy As String, lengthda As String, widthda As String, weightda As String, addida As String, fabprice As String, cmaccsgraothrs As String
    Dim lengthplus As String, widthplus As String, weightplus As String, addiplus As String, cmaccsgraothrsplus As String
      
            
 On Error GoTo Last
                                              
  'vba run or not confirmation
  Dim Msg As String, Ans As Variant
  Msg = "If you run this code, it will convert data into fabrics consumption into ACTIVECELL." _
   & vbNewLine & "Data sequence should be as/or->  cw, 65 22 10, 58 4, 250, 0.15" _
   & vbNewLine & "Data sequence should be as/or->  cy, 65 22 10, 58 4, 58, 0.5" _
   & vbNewLine & "Data sequence should be as/or->  iw, 38 10 4, 27 2, 250, 0.15" _
   & vbNewLine & "Data sequence should be as    ->  iy, 38 10 4, 27 2, 58, 0.5"

  Ans = MsgBox(Msg, vbYesNo)

  Select Case Ans

      Case vbYes
'vba run or not confirmation

    addActiveCell = ActiveCell.Address(0, 0)
    'cellValue = Trim(ActiveCell.Value)
    offaddActiveCell = ActiveCell.Offset(-1, 0).Address(0, 0)
    cellValue = Trim(ActiveCell.Offset(-1, 0).Value)
        'count =Len(string)-Len(Replace(string,"/",""))
        count = Len(cellValue) - Len(Replace(cellValue, ",", ""))
            
            Set Target = Range(offaddActiveCell)
            'Set Target = Range("c13")
                 'If target.Value = count Then
                        
      If count = 4 Then

                        'FabricsConsumptions
                        '========================
                    cwcyiwiy = Trim(Split(Range(offaddActiveCell).Value, ",")(0))
                    lengthda = Trim(Split(Range(offaddActiveCell).Value, ",")(1))
                    widthda = Trim(Split(Range(offaddActiveCell).Value, ",")(2))
                    weightda = Trim(Split(Range(offaddActiveCell).Value, ",")(3))
                    addida = Trim(Split(Range(offaddActiveCell).Value, ",")(4))
                    
                    lengthplus = Replace(lengthda, " ", "+")
                    'Range("i10") = lengthplus
                    widthplus = Replace(widthda, " ", "+")
                    'weightplus = Replace(widthda, " ", "+")
                        If cwcyiwiy = "cw" Then
                        
                            Range(offaddActiveCell).Formula = "=((((" & lengthplus & ")*(" & widthplus & ")*2*12*" & weightda & ")/10000000+" & addida & ")*105%)"
                            
                            ElseIf cwcyiwiy = "cy" Then
                            
                            Range(offaddActiveCell).Formula = "=(((((" & lengthplus & ")*(" & widthplus & ")*2*12)/36/2.54/2.54/" & weightda & ")+" & addida & ")*105%)"
                            
                            ElseIf cwcyiwiy = "iw" Then
                            
                            Range(offaddActiveCell).Formula = "=((((" & lengthplus & ")*(" & widthplus & ")*2*12*" & weightda & ")/1550000+" & addida & ")*105%)"
                            
                            ElseIf cwcyiwiy = "iy" Then
                            
                            Range(offaddActiveCell).Formula = "=(((((" & lengthplus & ")*(" & widthplus & ")*2*12)/36/" & weightda & ")+" & addida & ")*105%)"
                            
                            Else
                            Range(offaddActiveCell).Formula = "=((((" & lengthplus & ")*(" & widthplus & ")*2*12*" & weightda & ")/10000000+" & addida & ")*105%)"

                        End If
 End If
 
 
 If count = 6 Then
                        
                        'total fob price
                        '========================
                    cwcyiwiy = Trim(Split(Range(offaddActiveCell).Value, ",")(0))
                    lengthda = Trim(Split(Range(offaddActiveCell).Value, ",")(1))
                    widthda = Trim(Split(Range(offaddActiveCell).Value, ",")(2))
                    weightda = Trim(Split(Range(offaddActiveCell).Value, ",")(3))
                    addida = Trim(Split(Range(offaddActiveCell).Value, ",")(4))
                    fabprice = Trim(Split(Range(offaddActiveCell).Value, ",")(5))
                    cmaccsgraothrs = Trim(Split(Range(offaddActiveCell).Value, ",")(6))

                    lengthplus = Replace(lengthda, " ", "+")
                    'Range("i10") = lengthplus
                    widthplus = Replace(widthda, " ", "+")
                    cmaccsgraothrsplus = Replace(cmaccsgraothrs, " ", "+")
                        If cwcyiwiy = "cw" Then
                        
                            Range(offaddActiveCell).Formula = "=(((((" & lengthplus & ")*(" & widthplus & ")*2*12*" & weightda & ")/10000000+" & addida & ")*105%)*" & fabprice & "+" & cmaccsgraothrsplus & ")/12*110%"
                            
                            ElseIf cwcyiwiy = "cy" Then
                            
                            Range(offaddActiveCell).Formula = "=(((((" & lengthplus & ")*(" & widthplus & ")*2*12)/36/2.54/2.54/" & weightda & ")+" & addida & ")*105%)*" & fabprice & "+" & cmaccsgraothrsplus & ")/12*110%"
                            
                            ElseIf cwcyiwiy = "iw" Then
                            
                            Range(offaddActiveCell).Formula = "=((((" & lengthplus & ")*(" & widthplus & ")*2*12*" & weightda & ")/1550000+" & addida & ")*105%)*" & fabprice & "+" & cmaccsgraothrsplus & ")/12*110%"
                            
                            ElseIf cwcyiwiy = "iy" Then
                            
                            Range(offaddActiveCell).Formula = "=(((((" & lengthplus & ")*(" & widthplus & ")*2*12)/36/" & weightda & ")+" & addida & ")*105%)*" & fabprice & "+" & cmaccsgraothrsplus & ")/12*110%"
                            
                            Else
                            Range(offaddActiveCell).Formula = "=((((" & lengthplus & ")*(" & widthplus & ")*2*12*" & weightda & ")/10000000+" & addida & ")*105%)*" & fabprice & "+" & cmaccsgraothrsplus & ")/12*110%"

                        End If
                                                      
  End If

'vba run or not confirmation
 Case vbNo
 GoTo Quit:
 End Select
Quit:
'vba run or not confirmation
Last:  Exit Sub

End Sub

 

[Oaktree]

That's happening because you're changing cells within a worksheet_change event... which then causes the worksheet_change event to be called again.

To prevent that recursiveness, put

 Application.EnableEvents = False

at the beginning of your Worksheet_Change Event, and put

 Application.EnableEvents = True

at the end of your Worksheet_Change Event.

 

[mehidy1437]

That's happening because you're changing cells within a worksheet_change event... which then causes the worksheet_change event to be called again.

To prevent that recursiveness, put

 Application.EnableEvents = False

at the beginning of your Worksheet_Change Event, and put

 Application.EnableEvents = True

at the end of your Worksheet_Change Event.

After adding these , the code run for only one cell in the sheet.
When I do the changes for others cell, it doesn't run the code.

 

[Oaktree]

Sounds like your events aren't being enabled after the first cell is changed.

Your Application.EnableEvents needs to go above your "Last:" line. Otherwise, you'll exit the sub before that line is executed.

Note that, if your events are disabled, you can use the immediate window in VBA to run Application.EnableEvents = True to reset it.

 

[mehidy1437]

Sounds like your events aren't being enabled after the first cell is changed.

Your Application.EnableEvents needs to go above your "Last:" line. Otherwise, you'll exit the sub before that line is executed.

Note that, if your events are disabled, you can use the immediate window in VBA to run Application.EnableEvents = True to reset it.

Many thanks, Its working now.

Just one more point. I'm trying to add this MsgBox under below sections, but couldn't getting any idea, how can I do this?

Here

 If count = 4 Then

MsgBox with some msg!!

And Here

If count = 6 Then

MsgBox with some others msg!!

And then I want to press ok/cancel to run the code or not?

 

[Paul Ked]

That's a different question, start a new thread

 

[mehidy1437]

Thanks, I have got the idea & working on my code.