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

Thread: VB Code help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VB Code help

    I am trying to get my info from my text boxes in my user form into my worksheet. I have done this multiple time before and had easy success but now I'm getting problems. I either gut a runtime error or there is no data transferred. This below transfers no data to the worksheet
    Code:
    Private Sub CommandButton1_Click()
    Dim cNum As Integer
    Dim X As Integer
    Dim nextrow As Range
    
    
    cNum = 4
    Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    For X = 1 To cNum
    nextrow = Me.Controls("Reg" & X).Value
    Set nextrow = nextrow.Offset(0, 1)
    Next
    
    
    Reg1.Text = ""
    Reg2.Text = ""
    Reg3.Text = ""
    Reg4.Text = ""

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VB Code help

    Welcome to the Board!

    I am not so sure that you can cycle through all the "Reg" controls like this:
    Code:
    nextrow = Me.Controls("Reg" & X).Value
    What happens if you try hard-coding it, like this?
    Code:
    nextrow = Me.Controls("Reg1").Value
    If that works, that confirms that you cannot build the control names dynamically like you have tried.
    Maybe consider creating an array that holds all four control names, and iterating through that?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

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

    Default Re: VB Code help

    Hi & welcome to MrExcel.
    There is nothing obviously wrong with the code.
    Do you have 4 textboxes named Reg1, Reg2 etc?
    What line of code gives the error & what is the error number & message?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Mar 2017
    Location
    India
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB Code help

    Hi,

    replace Reg with textbox and try.

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB Code help

    Hi Fluff, Yes all textboxes are named Reg1 - Reg4. In this case I am getting now error at all. There is just no text being transferred from the userform1 to the excel sheet.

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

    Default Re: VB Code help

    If you add the line in blue, what does the msgbox say
    Code:
    Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    MsgBox nextrow.Address
    For X = 1 To cNum
    - 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
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB Code help

    Same thing Rahneesh. No error but no data

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB Code help

    $a$15

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

    Default Re: VB Code help

    Is that the first blank row in col A?
    Because your data should go into A15:D15 on sheet2
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB Code help

    Got it working! Thanks so much everyone.

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
  •