Help with forms 2

[JONNEWBY]

So i Have a Form,

all the data from the form is successfully placed into the assigned work sheet.

My question is

What is the code i have to Assign so it automatically adds a row down?

right now it adds the info but every time I try and put something new it overrides what was placed before..

thanks for the help,

-JONNEWBY

 

[VoG]

Please post your code.

 

[JONNEWBY]

Private Sub button_Click()


Sheets("uvpl").Range("A5").Value = Me.box1.Text
Sheets("uvpl").Range("B5").Value = Me.box2.Text
Sheets("uvpl").Range("C5").Value = Me.box3.Text
Sheets("uvpl").Range("D5").Value = Me.box4.Text
Sheets("uvpl").Range("E5").Value = Me.box5.Text
Sheets("uvpl").Range("F5").Value = Me.box6.Text
Sheets("uvpl").Range("G5").Value = Me.box7.Text
Sheets("uvpl").Range("H5").Value = Me.box8.Text

SHEET.Hide


End Sub

 

[VoG]

Try

Private Sub button_Click()
Dim NR As Long
With Sheets("uvpl")
    NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & NR).Value = Me.box1.Text
    .Range("B" & NR).Value = Me.box2.Text
    .Range("C" & NR).Value = Me.box3.Text
    .Range("D" & NR).Value = Me.box4.Text
    .Range("E" & NR).Value = Me.box5.Text
    .Range("F" & NR).Value = Me.box6.Text
    .Range("G" & NR).Value = Me.box7.Text
    .Range("H" & NR).Value = Me.box8.Text
End With
SHEET.Hide
End Sub

 

[JONNEWBY]

No luck it keeps on doing the same operation. Itt adds the info but after it's doesn't add a new row.

 

[VoG]

I find that very strange. If you run this does it give the next blank row on the sheet?

Sub test()
Dim NR As Long
With Sheets("uvpl")
    NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
End With
MsgBox NR
End Sub

 

[JONNEWBY]

HEY VoG thanks for your help..
it is working great.......

many thanks

-JONNEWBY

 

[JONNEWBY]

help with form

thanks to VoG that helped me with this code........so far i have this which just to insert the data from form,

is there a method to add automatic fill color to the whole row that matches a certain criteria?


Private Sub button_Click()
Dim NR As Long
With Sheets("uvpl")
NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NR).Value = Me.box1.Text
.Range("B" & NR).Value = Me.box2.Text
.Range("C" & NR).Value = Me.box3.Text
.Range("D" & NR).Value = Me.box4.Text
.Range("E" & NR).Value = Me.box5.Text
.Range("F" & NR).Value = Me.box6.Text
.Range("G" & NR).Value = Me.box7.Text
.Range("H" & NR).Value = Me.box8.Text
End With
SHEET.Hide
End Sub</pre>

 

[wigi]

Re: help with form

is there a method to add automatic fill color to the whole row that matches a certain criteria?

You can use conditional formatting if it should be automatic, that's built-in functionality in Excel.

 

[wigi]

Re: help with form

Private Sub button_Click()
Dim NR As Long
With Sheets("uvpl")
NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NR).Value = Me.box1.Text
.Range("B" & NR).Value = Me.box2.Text
.Range("C" & NR).Value = Me.box3.Text
.Range("D" & NR).Value = Me.box4.Text
.Range("E" & NR).Value = Me.box5.Text
.Range("F" & NR).Value = Me.box6.Text
.Range("G" & NR).Value = Me.box7.Text
.Range("H" & NR).Value = Me.box8.Text
End With
SHEET.Hide
End Sub

Alternatively:

Private Sub button_Click()
    Sheets("uvpl").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 8).Value = _
        Split(box1.Text & "|" & box2.Text & "|" & box3.Text & "|" & box4.Text & "|" & _
        box5.Text & "|" & box6.Text & "|" & box7.Text & "|" & box8.Text, "|")
    Sheet.Hide
End Sub