VBA Code to drop values in cells at the same time

Malcolm torishi

Board Regular
Joined
Apr 26, 2013
Messages
219
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Maybe
Code:
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
 
Upvote 0
Maybe
Code:
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
 
Upvote 0
Ok, let me know how it goes.
 
Upvote 0
Maybe
Code:
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)?
 
Upvote 0
Absolutely no idea, I'll put it down to a blonde moment. :LOL:
 
Upvote 0
Absolutely no idea, I'll put it down to a blonde moment. :LOL:
Yes, but are you really a blonde? :devilish:

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?
 
Last edited:
Upvote 0
It is indeed me & no, I'm not blonde. :)
 
Upvote 0
Fluff was taken & my lucky number is 13 :cool:
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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
Back
Top