+ Reply to Thread
Results 1 to 10 of 10

Populating problems with User Form

Hybrid View

  1. 07-26-2013, 04:58 PM #1
    clapforthewolfman
    clapforthewolfman is offline
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Populating problems with User Form

    Hi all,

    I am running into some problems with my code for a User Form and I'm hoping to get some help.

    Currently I have it set up so that you type data into one User Form and then based on that data this second User Form pops up (so far no problems with this).

    Now when I fill out the data in the second User Form it's doing a couple of things.

    1. Only the first two lines will populate to the specified worksheet.
    2. When I run the Userform again it writes over the data (doesn't jump to the next blank cell)

    Any insight you might be able to offer into this would be greatly appreciated. I have made a working User Form with all the data that I can revert to but I'd prefer to use multiple User Forms to keep the User experience simple, compartmentalized and clean.

    Thanks!

    Wolfman


    CommandButton2 is the 'OK' button. 


    Public Sub CommandButton2_Click()
Dim RowCount2 As Long
RowCount2 = Worksheets("Permitting and Notification").Range("A3").CurrentRegion.Rows.Count 
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount, 1).Value = Me.txtPermitAgencyName1.Value
    .Offset(RowCount, 2).Value = Me.cmboPermitStatus1.Value
    .Offset(RowCount, 3).Value = Me.txtApplicationDate1.Value
    .Offset(RowCount, 4).Value = Me.txtPermitNumber1.Value
    .Offset(RowCount, 5).Value = Me.txtPermitDate1.Value
    End With

If Not txtPermitAgencyName2.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A3").CurrentRegion.Rows.Count
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount + 1, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount + 1, 1).Value = Me.txtPermitAgencyName2.Value
    .Offset(RowCount + 1, 2).Value = Me.cmboPermitStatus2.Value
    .Offset(RowCount + 1, 3).Value = Me.txtApplicationDate2.Value
    .Offset(RowCount + 1, 4).Value = Me.txtPermitNumber2.Value
    .Offset(RowCount + 1, 5).Value = Me.txtPermitDate2.Value
    End With
 End If
If Not txtPermitAgencyName3.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").CurrentRegion.Range("A3").Rows.Count
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount + 2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount + 2, 1).Value = Me.txtPermitAgencyName3.Value
    .Offset(RowCount + 2, 2).Value = Me.cmboPermitStatus3.Value
    .Offset(RowCount + 2, 3).Value = Me.txtApplicationDate3.Value
    .Offset(RowCount + 2, 4).Value = Me.txtPermitNumber3.Value
    .Offset(RowCount + 2, 5).Value = Me.txtPermitDate3.Value
    End With
 End If
If Not txtPermitAgencyName4.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").CurrentRegion.Range("A3").Rows.Count
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount + 3, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount + 3, 1).Value = Me.txtPermitAgencyName4.Value
    .Offset(RowCount + 3, 2).Value = Me.cmboPermitStatus4.Value
    .Offset(RowCount + 3, 3).Value = Me.txtApplicationDate4.Value
    .Offset(RowCount + 3, 4).Value = Me.txtPermitNumber4.Value
    .Offset(RowCount + 3, 5).Value = Me.txtPermitDate4.Value
    End With
 End If
 If Not txtPermitAgencyName5.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").CurrentRegion.Range("A3").Rows.Count
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount + 4, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount + 4, 1).Value = Me.txtPermitAgencyName5.Value
    .Offset(RowCount + 4, 2).Value = Me.cmboPermitStatus5.Value
    .Offset(RowCount + 4, 3).Value = Me.txtApplicationDate5.Value
    .Offset(RowCount + 4, 4).Value = Me.txtPermitNumber5.Value
    .Offset(RowCount + 4, 5).Value = Me.txtPermitDate5.Value
    End With
 End If
 If Not txtPermitAgencyName6.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").CurrentRegion.Range("A3").Rows.Count
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount + 5, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount + 5, 1).Value = Me.txtPermitAgencyName6.Value
    .Offset(RowCount + 5, 2).Value = Me.cmboPermitStatus6.Value
    .Offset(RowCount + 5, 3).Value = Me.txtApplicationDate6.Value
    .Offset(RowCount + 5, 4).Value = Me.txtPermitNumber6.Value
    .Offset(RowCount + 5, 5).Value = Me.txtPermitDate6.Value
    End With
 End If
Unload Me

End Sub
    Reply With QuoteRegister To Reply
  2. 07-26-2013, 05:30 PM #2
    Tinbendr
    Tinbendr is offline
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Populating problems with User Form

    You will probably have to change the code for calculating the Rowcount2. Here's an article about prop and cons of different methods.
    David
    (*) Reputation points appreciated.
    Reply With QuoteRegister To Reply
  3. 07-31-2013, 12:32 PM #3
    clapforthewolfman
    clapforthewolfman is offline
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Populating problems with User Form

    Quote Originally Posted by Tinbendr View Post
    You will probably have to change the code for calculating the Rowcount2. Here's an article about prop and cons of different methods.
    I took a look but am still having issues. Are you able to work through my code to give me an example?
    Reply With QuoteRegister To Reply
  4. 07-27-2013, 02:00 AM #4
    blue.chio
    blue.chio is offline
    Forum Contributor
    Join Date
    03-14-2012
    Location
    location
    MS-Off Ver
    Excel 2007
    Posts
    170

    Re: Populating problems with User Form

    not sure but perhaps something like that
    Public Sub CommandButton2_Click()
Dim RowCount2 As Long
RowCount2 = Worksheets("Permitting and Notification").Range("A3").CurrentRegion.Rows.Count 
    With Worksheets("Permitting and Notification").Range("A3")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName1.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus1.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate1.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber1.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate1.Value
    End With
'
'
    blue
    Reply With QuoteRegister To Reply
  5. 07-31-2013, 12:39 PM #5
    Tinbendr
    Tinbendr is offline
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Populating problems with User Form 

    RowCount2 = Worksheets("Permitting and Notification").Cells(.Rows.Count, "A").End(XlUp).Row
    If this doesn't work, we'll have to see a sample.
    Reply With QuoteRegister To Reply
  6. 07-31-2013, 12:54 PM #6
    clapforthewolfman
    clapforthewolfman is offline
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Populating problems with User Form

    Quote Originally Posted by Tinbendr View Post 
    RowCount2 = Worksheets("Permitting and Notification").Cells(.Rows.Count, "A").End(XlUp).Row
    If this doesn't work, we'll have to see a sample.
    What do you mean sample? Sample workbook?
    Reply With QuoteRegister To Reply
  7. 07-31-2013, 12:45 PM #7
    clapforthewolfman
    clapforthewolfman is offline
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Populating problems with User Form

    This code gives me an invalid or unqualified reference error. What am I doing wrong here?
    Reply With QuoteRegister To Reply
  8. 07-31-2013, 01:16 PM #8
    Tinbendr
    Tinbendr is offline
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Populating problems with User Form

    Sorry, that should be
        With Worksheets("Permitting and Notification")
    RowCount2 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
         with .Range("A3")
    Reply With QuoteRegister To Reply
  9. 07-31-2013, 01:24 PM #9
    clapforthewolfman
    clapforthewolfman is offline
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Populating problems with User Form

    ok this works:

    Public Sub CommandButton2_Click()

Dim RowCount2 As Long
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row - 1
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName1.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus1.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate1.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber1.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate1.Value
    End With

If Not txtPermitAgencyName2.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row - 1
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName2.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus2.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate2.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber2.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate2.Value
    End With
 End If
If Not txtPermitAgencyName3.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row - 1
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName3.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus3.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate3.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber3.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate3.Value
    End With
 End If
If Not txtPermitAgencyName4.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row - 1
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName4.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus4.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate4.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber4.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate4.Value
    End With
 End If
 If Not txtPermitAgencyName5.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row - 1
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName5.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus5.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate5.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber5.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate5.Value
    End With
 End If
 If Not txtPermitAgencyName6.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row - 1
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName6.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus6.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate6.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber6.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate6.Value
    End With
 End If
Unload Me


End Sub
    Reply With QuoteRegister To Reply
  10. 07-31-2013, 01:20 PM #10
    clapforthewolfman
    clapforthewolfman is offline
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Populating problems with User Form

    I got this code to work but for some reason it puts a space between each entry. Any idea why?

    Public Sub CommandButton2_Click()

Dim RowCount2 As Long
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName1.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus1.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate1.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber1.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate1.Value
    End With

If Not txtPermitAgencyName2.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName2.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus2.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate2.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber2.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate2.Value
    End With
 End If
If Not txtPermitAgencyName3.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName3.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus3.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate3.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber3.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate3.Value
    End With
 End If
If Not txtPermitAgencyName4.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName4.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus4.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate4.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber4.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate4.Value
    End With
 End If
 If Not txtPermitAgencyName5.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName5.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus5.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate5.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber5.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate5.Value
    End With
 End If
 If Not txtPermitAgencyName6.Value = "" Then
    RowCount2 = Worksheets("Permitting and Notification").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Permitting and Notification").Range("A2")
    .Offset(RowCount2, 0).Value = Me.txtProjectID.Value
    .Offset(RowCount2, 1).Value = Me.txtPermitAgencyName6.Value
    .Offset(RowCount2, 2).Value = Me.cmboPermitStatus6.Value
    .Offset(RowCount2, 3).Value = Me.txtApplicationDate6.Value
    .Offset(RowCount2, 4).Value = Me.txtPermitNumber6.Value
    .Offset(RowCount2, 5).Value = Me.txtPermitDate6.Value
    End With
 End If
Unload Me


End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populating user form combobox with range question
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2013, 05:06 PM
  2. [SOLVED] Populating a text area in a user form from an Array???
    By rreifs68 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2013, 08:25 AM
  3. User Form Basics - Populating Text Boxes
    By T.J. Lanza in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2009, 03:48 AM
  4. populating a listbox on a user form?
    By lrhodes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2006, 07:05 PM
  5. Populating a list box or combo box on a User Form
    By matpj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2006, 08:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1