MsgBox appears multiple times after data is pulled from a sheet

Question

I'd like to get some help with the following code. I am very new to this but I think it's an easy solution I'm just unable to retrofit suggestions from other searches into my code.

The msgboxes are working fine on the first pass to check if the text box values are correct but when I am checking to see if a formula result from a sheet is correct I'm getting 5 message boxes popping up.

Hope this makes sense, let me know if you have any suggestions!

`Private Sub SpeedCommand_Click()
 Dim ctl As Control

 If TextBox1AM180.Value > 12000 And TextBox1AM180.Value <> "" Then
   MsgBox "Rate Value is out of range for this boom. Ensure rate value is less than 12,000 lbs./acre", vbExclamation, "Main Bin Application Rate"
    Me.TextBox1AM180.SetFocus
   Exit Sub
 End If

 If (TextBox2AM180.Value > 120 Or TextBox2AM180.Value < 20) And TextBox2AM180.Value <> "" Then
 MsgBox "Density Value is out of range. Ensure density value is between 20 and 120 lbs./cu ft.", vbExclamation, "Main Bin Density"
  Me.TextBox2AM180.SetFocus
  Exit Sub
 End If

 If TextBox3AM180.Value > 12000 And TextBox3AM180.Value <> "" Then
  MsgBox "Rate Value is out of range for this boom. Ensure rate value is less than 12,000 lbs./acre", vbExclamation, "Granular Bin Application Rate"
  Me.TextBox3AM180.SetFocus
  Exit Sub
 End If

 If (TextBox4AM180.Value > 120 Or TextBox4AM180.Value < 20) And       TextBox4AM180.Value <> "" Then
  MsgBox "Density Value is out of range. Ensure density value is between 20 and 120 lbs./cu ft.", vbExclamation, "Granular Bin Density"
  Me.TextBox4AM180.SetFocus
  Exit Sub
 End If

 ' Write data to worksheet

 With Range("B4")
   .Offset(0, 0).Value = Me.TextBox1AM180.Value
   .Offset(1, 0).Value = Me.TextBox2AM180.Value
   .Offset(5, 0).Value = Me.TextBox3AM180.Value
   .Offset(6, 0).Value = Me.TextBox4AM180.Value
 End With

 If Range("MaxSpeed1").Value > 30 Then
     MsgBox "Based upon rate and density, speed is restricted by machine top end application speed."
  Exit Sub
 End If

 If Range("MaxSpeed2").Value > 30 Then
    MsgBox "Based upon rate and density, speed is restricted by machine top end application speed."
    Exit Sub
  End If

 ' Hide the form
 frmAirmax.Hide

what are these `TextBox1AM180` things that they have a `Value` property? Are you sure this is VB.NET? The event signature looks like VB6. Whatever the case, it seems unlikely that .Value could be both number (`...>12000`) and string (`...<> ""`) — Ňɏssa Pøngjǣrdenlarp, Jul 01 '15 at 17:18
NB i think you are comparing strings and integers TextBox1AM180.Value > 12000 And TextBox1AM180.Value <> "" — Jay, Jul 01 '15 at 17:23
As @Plutonix pointed out, this does not appear to be VB.NET. The context and comparisons of strings to integers lead me to believe this is an Excel VBA Macro. An Excel cell value *can* hold a string/integer/etc. and using this property as @Kate does is valid, but not best practice; it is generally preferable to use .Text for strings because an empty cell has a value of 0 but a text of "". Anyway, I don't see anything causing the extra message boxes in this code. Do you also have a worksheet_change sub that displays one? If so, you trigger it 4 times in your `With Range("B4")` block. — Josh, Jul 01 '15 at 17:57
You are getting all the message boxes because your code is triggering those lines. It's not clear from your question why you would expect something different to happen? What is the distinction between `first pass` and `checking a formula...`? @Plutonix, this is Excel VBA... I updated the tags. `Range` and `Offset` give it away that this is Excel specific — Byron Wall, Jul 01 '15 at 18:20
@byron Sorry for the confusion, my issue is with the code: { If Range("MaxSpeed1").Value > 30 Then MsgBox } I'm having to click ok on the same message boxes 5 times and I only want it to pop up once — Kate, Jul 01 '15 at 19:36
You should only see it once unless you have other events in place that would trigger this code or other code. Do you have other events in play that we cannot see? Like `Worksheet_Change`? If not, what happens if you add a breakpoint to the line below `With Range...` and use F8 to step through? How many times do you arrive at the `MsgBox` code before it hits an `Exit Sub`? Does the execution of the code go somewhere unexpected while stepping through? — Byron Wall, Jul 01 '15 at 19:43
I do have Worksheet_Calculate, how do I need to get around this? The msgbox comes up 5 times. — Kate, Jul 01 '15 at 19:47
Unrelated, but I'd move the validation code to the Textbox events. — Comintern, Jul 02 '15 at 00:03

score 1 · Answer 1 · answered Jul 02 '15 at 01:56

Use the Application.EnableEvents property to temporarily disable events from firing and then re-enable them when you're done.

Something like this:

Application.EnableEvents = False
With Range("B4")
   .Offset(0, 0).Value = Me.TextBox1AM180.Value
   .Offset(1, 0).Value = Me.TextBox2AM180.Value
   .Offset(5, 0).Value = Me.TextBox3AM180.Value
   .Offset(6, 0).Value = Me.TextBox4AM180.Value
 End With
Application.EnableEvents = True

MsgBox appears multiple times after data is pulled from a sheet

1 Answers1