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

 

[Malcolm torishi]

Hello Fluff
you asked me to let you know how I got on, well for some strange reason, I could not get the code to drop the values from either the calendar or TextBox,s into the cell range on my spread sheet. Is it me?..

 

[Fluff]

Does AO7 have anything in it? If so do you have values below that with no blanks anywhere in that column?

 

[Malcolm torishi]

AO7 has nothing in it, AO6, AP6 & AQ6 all have text sub headings in, below this is completely clear

 

[Fluff]

In that case check the very last row of your sheet, you should find the values there.
Once you delete those rows try

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

 

[Malcolm torishi]

In that case check the very last row of your sheet, you should find the values there.
Once you delete those rows try

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

Thank you Fluff , will give it a go tomorrow

 

[Fluff]

Ok