Excel User Form problem

  • Thread starter Thread starter humet
  • Start date Start date
H

humet

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
 
humet said:
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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

Format the cell as mm/dd/yyyy
or use
=Today()
 
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
 
humet said:
Where would I implement these lines of code into this:

-------------------------------------------------------------------------- ----
Click to expand...
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
 
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
 
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"")"
 
Back
Top