VBA Code to drop values in cells at the same time

[Malcolm torishi]

I have the following code that drops a calendar value and 2 textbox values into a 3 cells on my spread sheet that then triggers an email to be sent if the total of the 3 values that are dropped is below a certain value, but what is happening is when the values get dropped rather than being dropped in one go they get dropped in one at a time, albeit it’s a split second between each drop.
What I would like the code to do is drop all 3 values in the cells at the same time so there’s no time lag, does any know how this can be done please

Thank you

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("OUT").Activate
Range("AC5").Value = 1
Range("AO7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 0) = Calendar1.Value
ActiveCell.Offset(0, 1) = TextBox1.Value
ActiveCell.Offset(0, 2) = TextBox2.Value
Range("AC5").Value = 0
Range("AB4").Select
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Maybe

Private Sub CommandButton1_Click()
   Application.ScreenUpdating = False
   ActiveWorkbook.Sheets("OUT").Activate
   Range("AC5").Value = 1
   With Range("AO7").End(xlDown).Offset(1)
      .Resize(, 3).Value = Array(Calendar1.Value, TextBox1.Value, TextBox2.Value)
   End With
   Range("AC5").Value = 0
   Range("AB4").Select
   Application.ScreenUpdating = True
End Sub

 

[Malcolm torishi]

Maybe

Private Sub CommandButton1_Click()
   Application.ScreenUpdating = False
   ActiveWorkbook.Sheets("OUT").Activate
   Range("AC5").Value = 1
   With Range("AO7").End(xlDown).Offset(1)
      .Resize(, 3).Value = Array(Calendar1.Value, TextBox1.Value, TextBox2.Value)
   End With
   Range("AC5").Value = 0
   Range("AB4").Select
   Application.ScreenUpdating = True
End Sub

I will give it a go tomorrow thank

 

[Fluff]

Ok, let me know how it goes.

 

[Rick Rothstein]

Maybe

Private Sub CommandButton1_Click()
   Application.ScreenUpdating = False
   ActiveWorkbook.Sheets("OUT").Activate
   Range("AC5").Value = 1
[B][COLOR="#FF0000"]   With Range("AO7").End(xlDown).Offset(1)
      .Resize(, 3).Value = Array(Calendar1.Value, TextBox1.Value, TextBox2.Value)
   End With
[/COLOR][/B]   Range("AC5").Value = 0
   Range("AB4").Select
   Application.ScreenUpdating = True
End Sub

Why the With..End With block (you only have the one Resize referencing its object)?

 

[Fluff]

Absolutely no idea, I'll put it down to a blonde moment.

 

[Rick Rothstein]

Absolutely no idea, I'll put it down to a blonde moment.

Yes, but are you really a blonde?

On an aside, I have been also volunteering on the "Excel Forum" forum and there is a volunteer there using the name Fluff13 and they are from Chippenham... is that you by any chance?

 

[Fluff]

It is indeed me & no, I'm not blonde.

 

[Rick Rothstein]

It is indeed me...

So why Fluff13... were Fluff1 through Fluff12 really taken already?

 

[Fluff]

Fluff was taken & my lucky number is 13