Excel User Form problem
 

Hi,

I am currently making a spreadsheet with a user form following the
guide on exceltip.com. I have creted the form and that works fine. But,
I have come to a problem.

What I want to do it when the user has filled the form in and it
inserts the data into the spreadsheets, i want the cell in column A, on
the row it has created with the new data, to have a number in it which
is consecutive to the number above it (123, 124, 125). How do I do
this?

Also, in one of the cells on the new row it has created is a formula
that is linked with another cell on that row, can you please tell me
if, as the rows of data go down the spreadsheet, the formula will
change itself to work on a new row. Can you tell me how to make it
work?

How do I get the current date displayed without the time showing, as
=now() displays the time as well as the date.

I think that is all,

thanks for any help,

humet


---
Message posted from http://www.ExcelForum.com/

Excel User Form problem
 

"humet" wrote in message
...
Hi,

I am currently making a spreadsheet with a user form following the
guide on exceltip.com. I have creted the form and that works fine. But,
I have come to a problem.

What I want to do it when the user has filled the form in and it
inserts the data into the spreadsheets, i want the cell in column A, on
the row it has created with the new data, to have a number in it which
is consecutive to the number above it (123, 124, 125). How do I do
this?

Cells(rw,1).Value = Cells(rw-1,1).Value + 1


Also, in one of the cells on the new row it has created is a formula
that is linked with another cell on that row, can you please tell me
if, as the rows of data go down the spreadsheet, the formula will
change itself to work on a new row. Can you tell me how to make it
work?

Cells(rw,2).Value = "=Sum(" & cells(rw,6).Resize(1,4).Address & ")"




How do I get the current date displayed without the time showing, as
=now() displays the time as well as the date.

Format the cell as mm/dd/yyyy
or use
=Today()


I think that is all,

thanks for any help,

humet

--
Regards,
Tom Ogilvy




---
Message posted from http://www.ExcelForum.com/

Excel User Form problem
 

Where would I implement these lines of code into this:

------------------------------------------------------------------------------

Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("Datalist").Activate

Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value =

ActiveCell.Offset(0, 1) = TextBox1.Value

ActiveCell.Offset(0, 2) = TextBox2.Value

ActiveCell.Offset(0, 3) = ComboBox1.Value

ActiveCell.Offset(0, 4) =

ActiveCell.Offset(0, 5) = "=TODAY()"

ActiveCell.Offset(0, 6) = "=YEAR(K13)"

ActiveCell.Offset(0, 7) = TextBox3.Value

ActiveCell.Offset(0, 8) = TextBox4.Value

Call CommandButton2_Click

Range("A1").Select

ActiveWorkbook.Save

ActiveWorkbook.Sheets("Title Page").Activate

End Sub
---------------------------------------------------------------------------------

Column A is where I want the consecutive number to appear, and Column
is where I want the formula from the above cell to appear, that formul
is :

=IF(F12<=$L$3,"Y","N")

I need the "F12" part to change as it goes down the list.

Thanks for the fast reply, and i hope you can clear it up for me
:rolleyes:

hume

--
Message posted from http://www.ExcelForum.com

Excel User Form problem
 

"humet" wrote in message
...
Where would I implement these lines of code into this:

--------------------------------------------------------------------------
----

Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("Datalist").Activate

Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = ActiveCell.Offset(-1,0).Value + 1

ActiveCell.Offset(0, 1) = TextBox1.Value

ActiveCell.Offset(0, 2) = TextBox2.Value

ActiveCell.Offset(0, 3) = ComboBox1.Value

ActiveCell.Offset(0, 4) = "=IF(" & ActiveCell.offset(0,5).Address(0,0) & _
"<=$L$3,"Y","N")


ActiveCell.Offset(0, 5) = "=TODAY()"
' if you don't want this value to change, you can use
' ActiveCell.Offset(0, 5) = Date

ActiveCell.Offset(0, 6) = "=YEAR(K13)"

ActiveCell.Offset(0, 7) = TextBox3.Value

ActiveCell.Offset(0, 8) = TextBox4.Value

Call CommandButton2_Click

Range("A1").Select

ActiveWorkbook.Save

ActiveWorkbook.Sheets("Title Page").Activate

End Sub
---------------------------------------------------------------------------
------

Column A is where I want the consecutive number to appear, and Column E
is where I want the formula from the above cell to appear, that formula
is :

=IF(F12<=$L$3,"Y","N")

I need the "F12" part to change as it goes down the list.

Thanks for the fast reply, and i hope you can clear it up for me!
:rolleyes:

humet


---
Message posted from http://www.ExcelForum.com/


--
Regards,
Tom Ogilvy

Excel User Form problem
 

Thanks again for the extremely fast reply.

But, when I enter this line of code:

ActiveCell.Offset(0, 4) = "=IF(" & ActiveCell.offset(0,5).Address(0,0
& _
"<=$L$3,"Y","N")

I get a compile error: expected: End of statement. It highlights th
Y.

Can this be fixed?

Thanks in advance,

hume

--
Message posted from http://www.ExcelForum.com

Excel User Form problem
 

My fault, I forgot to double the double quotes within the string. (I was
editing your formula).

ActiveCell.Offset(0, 4) = "=IF(" & _
ActiveCell.Offset(0, 5).Address(0, 0) & _
"<=$L$3,""Y"",""N"")"

--
Regards,
Tom Ogilvy


"humet" wrote in message
...
Thanks again for the extremely fast reply.

But, when I enter this line of code:

ActiveCell.Offset(0, 4) = "=IF(" & ActiveCell.offset(0,5).Address(0,0)
& _
"<=$L$3,"Y","N")

I get a compile error: expected: End of statement. It highlights the
Y.

Can this be fixed?

Thanks in advance,

humet


---
Message posted from http://www.ExcelForum.com/