Userform

[Matt]

I'm using a Userform to collect 4 fields of data e.g. name,date,priority and date finished. I'm then inserting the data into row 7, A7 to D7. I want the userform to ask the user if any more entries are required and if so bring the userform up again but this time insert the data into row 8 and so forth until the user has finished entering all data.

Can someone help me with the code so the userform collects data, inserts the data in the required row, prompts if any more entries are required, if so clears the userform and asks the user to enter details and insert in the next row until the user has no further entries to make and then closes the userform.

Hope someone can understand and help!

thanks

Matt

 

[Dave Hawley]

Hi Matt


Let's say your UserForm has 4 Textboxes called Textbox1 to 4. Try some code like this

Private Sub CommandButton1_Click()
Dim iReply As Integer

If iRow = 0 Then iRow = 1
If iCol = 0 Then iCol = 7

With Sheet1
    .Cells(iRow, iCol) = Textbox1
     Textbox1 = ""
     iRow = iRow + 1
     
     
    .Cells(iRow, iCol) = textbox2
     textbox2 = ""
     iRow = iRow + 1
    
     
    .Cells(iRow, iCol) = textbox3
     textbox3 = ""
     iRow = iRow + 1
   
     
     .Cells(iRow, iCol) = textbox4
     textbox3 = ""
     iRow = iRow + 1
     
     iCol = iCol + 1
End With

iReply = MsgBox("More Entries?", vbYesNo)
         If iReply = vbNo Then Unload Me
         
Textbox1.SetFocus
End Sub

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-08 04:50

 

[Matt]

Thanks Dave