Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 hume -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! humet --- Message posted from http://www.ExcelForum.com/ -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form Problem ??? | New Users to Excel | |||
User form problem | Excel Worksheet Functions | |||
User Form Problem | Excel Discussion (Misc queries) | |||
User Form Problem | Excel Discussion (Misc queries) | |||
problem with user form | Excel Programming |