Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm code help needed.
Thanks to all who helped me yesterday. You were so helpful I thought I'
throw my other VBA problem into the ring. What I've got is a Userform for users to input address details, whic then puts those details into an excel sheet when a button is clicked (The intention is eventually to mailmerge the details and print a shee of address labels) Unfortunately, it keeps putting the address details on the same line until you try and close the form, when it gets stuck in a loop. Hope someone can help. Here's the userform code Public Sub InputButton_Click() 'set up variables Dim Check Dim Activerow As Variant Dim StartCellRow As Integer Dim StartCellColumn As Integer Dim Printbutt As Integer Dim Title As String Dim FirstName As String Dim Surname As String Dim Address1 As String Dim Address2 As String Dim Address3 As String Dim Address4 As String Dim Postcode As String Dim Todaydate As String Dim ID As String Dim form As String 'fill variables with info from form Check = True Title = Titlebox FirstName = TxtFirstname Surname = TxtSurname Address1 = TxtAddress1 Address2 = TxtAddress2 Address3 = TxtAddress3 Address4 = TxtAddress4 Postcode = TxtPostcode Todaydate = TxtDate ID = TxtLoginID form = Formbox Today = Format(Date, "d/mm/yyyy") StartCellRow = "1" StartCellColumn = "1" Printbutt = "1" 'check this works Do ActiveCell(StartCellRow, StartCellColumn) = Title StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = FirstName StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Surname StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address1 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address2 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address3 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address4 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Postcode StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Today StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = ID StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = form StartCellRow = StartCellRow + 1 StartCellColumn = "1" 'this bit is to try and clear the form ready for the next input Unload UserForm1 UserForm1.Show Loop Until Check = False End Sub 'when this button is clicked, check should equal false 'and the form should be closed Public Sub PrintButton_Click() Check = False End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm code help needed.
There are a few things that would help:
1) you could specify the cell where you want to start your process, and then select it as the activecell. Then use Activecell.Offset(0,1).value = x where x is the value you wish to assign. You just increase the offset number by 1 to move across the columns, and it would be better to specifically code these, rather than increment them. 2) you seem to be relying on Excel determining the active row to produce the next empty row - this is not a foolproof method. You would be better off determining the last used cell in the sheet where you are writing the details, and then obtaining the rwo, incrementing by one and selecting the first cell on taht row - the function below always works. Call the function like this: Call Goto_LAst("a1",1,1) it will return the last cell address and the numeric column and the number of the row also. Function Goto_Last(strrange, int_col, int_row) On Error Resume Next application.ScreenUpdating = False Cells(Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row, _ Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column).Select If Err.Number < 0 Then MsgBox "No data in sheet" application.ScreenUpdating = True strrange = ActiveCell.AddressLocal int_col = ActiveCell.Column int_row = ActiveCell.Row End Function Hope this is of use Alan -----Original Message----- Thanks to all who helped me yesterday. You were so helpful I thought I'd throw my other VBA problem into the ring. What I've got is a Userform for users to input address details, which then puts those details into an excel sheet when a button is clicked. (The intention is eventually to mailmerge the details and print a sheet of address labels) Unfortunately, it keeps putting the address details on the same line, until you try and close the form, when it gets stuck in a loop. Hope someone can help. Here's the userform code Public Sub InputButton_Click() 'set up variables Dim Check Dim Activerow As Variant Dim StartCellRow As Integer Dim StartCellColumn As Integer Dim Printbutt As Integer Dim Title As String Dim FirstName As String Dim Surname As String Dim Address1 As String Dim Address2 As String Dim Address3 As String Dim Address4 As String Dim Postcode As String Dim Todaydate As String Dim ID As String Dim form As String 'fill variables with info from form Check = True Title = Titlebox FirstName = TxtFirstname Surname = TxtSurname Address1 = TxtAddress1 Address2 = TxtAddress2 Address3 = TxtAddress3 Address4 = TxtAddress4 Postcode = TxtPostcode Todaydate = TxtDate ID = TxtLoginID form = Formbox Today = Format(Date, "d/mm/yyyy") StartCellRow = "1" StartCellColumn = "1" Printbutt = "1" 'check this works Do ActiveCell(StartCellRow, StartCellColumn) = Title StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = FirstName StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Surname StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address1 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address2 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address3 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address4 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Postcode StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Today StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = ID StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = form StartCellRow = StartCellRow + 1 StartCellColumn = "1" 'this bit is to try and clear the form ready for the next input Unload UserForm1 UserForm1.Show Loop Until Check = False End Sub 'when this button is clicked, check should equal false 'and the form should be closed Public Sub PrintButton_Click() Check = False End Sub --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm code help needed.
Thanks, Alan. Unfortunately, I'm an absolute beginner at all this.
I've done what you suggested at point 1 (I think), but don't know how to specify the cell I want to start at. I've tried this - ActiveCell("A2") = Title ActiveCell.Offset(0, 1).Value = FirstName ActiveCell.Offset(0, 2).Value = Surname ActiveCell.Offset(0, 3).Value = Address1 ActiveCell.Offset(0, 4).Value = Address2 ActiveCell.Offset(0, 5).Value = Address3 ActiveCell.Offset(0, 6).Value = Address4 ActiveCell.Offset(0, 7).Value = Postcode ActiveCell.Offset(0, 8).Value = Today ActiveCell.Offset(0, 9).Value = ID ActiveCell.Offset(0, 10).Value = form And as for point 2, (and this is probably a dumb question), where do I put that code? Do I put it as a seperate module? And if I'm specifying a start cell, surely each time the program loops, it'll start at the same place, overwriting the original data? (That's the problem I was having with my original code.) Alan Hutchins wrote:[color=blue] [b]There are a few things that would help: 1) you could specify the cell where you want to start your process, and then select it as the activecell. Then use Activecell.Offset(0,1).value = x where x is the value you wish to assign. You just increase the offset number by 1 to move across the columns, and it would be better to specifically code these, rather than increment them. 2) you seem to be relying on Excel determining the active row to produce the next empty row - this is not a foolproof method. You would be better off determining the last used cell in the sheet where you are writing the details, and then obtaining the rwo, incrementing by one and selecting the first cell on taht row - the function below always works. Call the function like this: Call Goto_LAst("a1",1,1) it will return the last cell address and the numeric column and the number of the row also. Function Goto_Last(strrange, int_col, int_row) On Error Resume Next application.ScreenUpdating = False Cells(Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row, _ Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column).Select If Err.Number < 0 Then MsgBox "No data in sheet" application.ScreenUpdating = True strrange = ActiveCell.AddressLocal int_col = ActiveCell.Column int_row = ActiveCell.Row End Function Hope this is of use Alan --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm code help needed.
THIS IS A CUT DOWN VERSION OF THE CODE YOU HAD AMENDED
USING THE SUGGESTIONS (Note the Function is included but not shown in the code here): Try this in a new workbook with 1 userform with 3 textboxes, and two buttons one to add teh value and 1 to cancel the process. You'll notice that it adds your data in each line going down one after the other. The easiest way to prove it is to run it (don't forget to include the Function code), by just entering the letters of teh alphabet in order into the first textbox only '++++++++++++++++++++++++++++++++++++++++ Private Sub CommandButton1_Click() Unload Me End Sub Private Sub Button_cancel_Click() Unload Me End Sub Public Sub InputButton_Click() 'set up variables Dim x As Integer Dim y As String Dim int_col As Integer Dim int_row As Integer Dim Title As String Dim Firstname As String Dim Surname As String 'fill variables with info from form Title = TitleBox Firstname = TxtFirstname Surname = TxtSurname Worksheets("sheet1").Select int_col = 1 int_row = 1 Call Goto_Last("a1", int_col, int_row) x = int_row + 1 y = "A" & x Range(y).Select Range(y).Value = Title Range(y).Offset(0, 1).Value = Firstname Range(y).Offset(0.2).Value = Surname 'this bit is to try and clear the form ready for the next input Unload UserForm1 UserForm1.Show End Sub -----Original Message----- Thanks, Alan. Unfortunately, I'm an absolute beginner at all this. I've done what you suggested at point 1 (I think), but don't know how to specify the cell I want to start at. I've tried this - ActiveCell("A2") = Title ActiveCell.Offset(0, 1).Value = FirstName ActiveCell.Offset(0, 2).Value = Surname ActiveCell.Offset(0, 3).Value = Address1 ActiveCell.Offset(0, 4).Value = Address2 ActiveCell.Offset(0, 5).Value = Address3 ActiveCell.Offset(0, 6).Value = Address4 ActiveCell.Offset(0, 7).Value = Postcode ActiveCell.Offset(0, 8).Value = Today ActiveCell.Offset(0, 9).Value = ID ActiveCell.Offset(0, 10).Value = form And as for point 2, (and this is probably a dumb question), where do I put that code? Do I put it as a seperate module? And if I'm specifying a start cell, surely each time the program loops, it'll start at the same place, overwriting the original data? (That's the problem I was having with my original code.) Alan Hutchins wrote: [b]There are a few things that would help: 1) you could specify the cell where you want to start your process, and then select it as the activecell. Then use Activecell.Offset(0,1).value = x where x is the value you wish to assign. You just increase the offset number by 1 to move across the columns, and it would be better to specifically code these, rather than increment them. 2) you seem to be relying on Excel determining the active row to produce the next empty row - this is not a foolproof method. You would be better off determining the last used cell in the sheet where you are writing the details, and then obtaining the rwo, incrementing by one[color=blue] and selecting the first cell on taht row - the function below always works. Call the function like this: Call Goto_LAst("a1",1,1) it will return the last cell address and the numeric column and the number of the row also. Function Goto_Last(strrange, int_col, int_row) On Error Resume Next application.ScreenUpdating = False Cells(Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row, _ Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column).Select If Err.Number < 0 Then MsgBox "No data in sheet" application.ScreenUpdating = True strrange = ActiveCell.AddressLocal int_col = ActiveCell.Column int_row = ActiveCell.Row End Function Hope this is of use Alan --- Message posted from http://www.ExcelForum.com/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm code help needed.
I assume that you are trying to add data from your form in
to database format? If so, you could just simply read the user input from the form into a simple array and then add data to your table this way. something like this may work: Private Sub CommandButton1_Click() ReDim mydata(4) For na = 0 To 3 mydata(na) = Controls(na).Text Controls(na).Text = "" Next Dim NewRecordRng As Object Set NewRecordRng = Worksheets("sheet1").Cells(2, 1).CurrentRegion 'Put new values into worksheet just below those rows. newrow = NewRecordRng.Rows.Count + 1 With NewRecordRng For na = 0 To 3 .Cells(newrow, na + 1).Value = mydata(na) Next End With TextBox2.SetFocus End Sub I have only shown 4 textboxes but you can modify to meet your needs. To see if this works for you. create a form & add 4 textboxes then add a button(in that order) Double click the button and past above code. You need a sheet named sheet1. Run the form & enter data. It should add data to sheet1 & clear textboxes for next entry. further entry should be placed on next line. Hope helpful -----Original Message----- Thanks to all who helped me yesterday. You were so helpful I thought I'd throw my other VBA problem into the ring. What I've got is a Userform for users to input address details, which then puts those details into an excel sheet when a button is clicked. (The intention is eventually to mailmerge the details and print a sheet of address labels) Unfortunately, it keeps putting the address details on the same line, until you try and close the form, when it gets stuck in a loop. Hope someone can help. Here's the userform code Public Sub InputButton_Click() 'set up variables Dim Check Dim Activerow As Variant Dim StartCellRow As Integer Dim StartCellColumn As Integer Dim Printbutt As Integer Dim Title As String Dim FirstName As String Dim Surname As String Dim Address1 As String Dim Address2 As String Dim Address3 As String Dim Address4 As String Dim Postcode As String Dim Todaydate As String Dim ID As String Dim form As String 'fill variables with info from form Check = True Title = Titlebox FirstName = TxtFirstname Surname = TxtSurname Address1 = TxtAddress1 Address2 = TxtAddress2 Address3 = TxtAddress3 Address4 = TxtAddress4 Postcode = TxtPostcode Todaydate = TxtDate ID = TxtLoginID form = Formbox Today = Format(Date, "d/mm/yyyy") StartCellRow = "1" StartCellColumn = "1" Printbutt = "1" 'check this works Do ActiveCell(StartCellRow, StartCellColumn) = Title StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = FirstName StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Surname StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address1 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address2 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address3 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Address4 StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Postcode StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = Today StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = ID StartCellColumn = StartCellColumn + 1 ActiveCell(StartCellRow, StartCellColumn) = form StartCellRow = StartCellRow + 1 StartCellColumn = "1" 'this bit is to try and clear the form ready for the next input Unload UserForm1 UserForm1.Show Loop Until Check = False End Sub 'when this button is clicked, check should equal false 'and the form should be closed Public Sub PrintButton_Click() Check = False End Sub --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NEED A MACRO CODE TO USE IN A USERFORM | Excel Discussion (Misc queries) | |||
Amount of code in UserForm | Excel Programming | |||
Userform Formula Help Needed | Excel Programming | |||
VBA code for Userform | Excel Programming | |||
A pointer or two needed using userform | Excel Programming |