Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: VBA Code to drop values in cells at the same time
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to drop values in cells at the same time

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,307
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Quote Originally Posted by Fluff View Post
    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,307
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Ok, let me know how it goes.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Quote Originally Posted by Fluff View Post
    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
    Why the With..End With block (you only have the one Resize referencing its object)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,307
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Absolutely no idea, I'll put it down to a blonde moment.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Quote Originally Posted by Fluff View Post
    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?
    Last edited by Rick Rothstein; Sep 11th, 2019 at 12:11 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,307
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    It is indeed me & no, I'm not blonde.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Quote Originally Posted by Fluff View Post
    It is indeed me...
    So why Fluff13... were Fluff1 through Fluff12 really taken already?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,307
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Code to drop values in cells at the same time

    Fluff was taken & my lucky number is 13
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •