Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
Hello,
I am not sure of how to do this, nor if it is possible. I currently have a form, it has 4 comboboxes, 1 textbox, 3 cmdbuttons. This works great now, but would like to modify to be better. The form is used to enter information into the row, but there must be a blank row with the formulas already there for it to insert into, this is the current code from the form: Option Explicit Private Sub cboProduct_Change() Dim SourceData As Range Dim val1 As String Set SourceData = Range(cboProduct.RowSource) val1 = cboProduct.Value lblProduct = val1 End Sub Private Sub cboBankID_Change() Dim SourceData As Range Dim val1, val2 As String Set SourceData = Range(cboBankID.RowSource) val1 = cboBankID.Value val2 = SourceData.Offset(cboBankID.ListIndex, 1).Resize(1, 1).Value If cboBankID.Value = "TESTME" Then lblBankID = val1 lblProject = "" Else lblBankID = val1 lblProject = val2 End If End Sub Private Sub cboProject_Change() Dim SourceData As Range Dim val1 As String Set SourceData = Range(cboProject.RowSource) val1 = SourceData.Offset(cboProject.ListIndex, 1).Resize(1, 1).Value lblProject = val1 End Sub Private Sub cboServType_Change() Dim SourceData As Range Dim val1 As String Set SourceData = Range(cboServType.RowSource) val1 = SourceData.Offset(cboServType.ListIndex, 1).Resize(1, 1).Value lblServType = val1 End Sub Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cmdClearForm_Click() Call UserForm_Initialize End Sub Private Sub cmdInsert_Click() ActiveSheet.Activate Range("A8").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = lblProduct ActiveCell.Offset(0, 1) = lblBankID ActiveCell.Offset(0, 2) = lblProject ActiveCell.Offset(0, 3) = lblServType ActiveCell.Offset(0, 8) = txtAmount With ActiveCell.Offset(0, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With End Sub Private Sub cmdSelect_Click() Application.CommandBars("Workbook tabs").ShowPopup 500, 200 End Sub Private Sub UserForm_Initialize() txtAmount.Value = "" cboProduct.Value = "" lblProduct = "" cboBankID.Value = "" lblBankID = "" cboProject.Value = "" lblProject = "" cboServType.Value = "" lblServType = "" cboProduct.SetFocus End Sub What i would like: insead of inserting just one row at a time..... I would like to take the 5 user entries, store them, then, when all rows that are needed for the sheeet, add the rows to the proper sheet. The rows in the sheet have formula's, so i would need to copy the row above first, then, i want to store that info into ???? [what should i store into??? listbox? ]....then, add the info, if i needed another row, i would select the add button (not currently used yet), then when all info is collected, insert into the sheet. In other words, before i hit [insert], i want to gather the info first, it might be 5 lines of information, maybe one...but include a button that 'adds' another line, then when all lines or rows are needed and ready, insert it into the worksheet... I hope that i have explained this properly. Hope that someone can help me find the solution. In advance, thanks for any help provided. K~ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
I think what you are asking is can the values entered in a userform be stored until all are entered and then written to the worksheet. If that is not what you are asking for please ignore this post. I created a very simple userform that accomplishes this. The userform has two command buttons and one textbox. In a standard excel module i wrote the following in the declarations Public myarray(1 To 5) As Variant This creates an array that can be shared among procedures and modules. This has to be in a standard module and not the userform module. Userform modules do not allow public arrays. The entry command button has this code. Private Sub CommandButton1_Click() Static counter As Integer counter = counter + 1 myarray(counter) = TextBox1.Text TextBox1.Text = "" End Sub Make sure that counter is declared as with static and not dim so it retains its value. This writes the text in textbox1 to the array. It can only be done five times because that is how the array is dimensioned. The write to worksheet command button has the following code. Private Sub CommandButton2_Click() Dim dest As Range Dim i As Integer Set dest = ActiveSheet.Range("a65536").End(xlUp) For i = 1 To 5 dest.Offset(i, 0).Value = myarray(i) Next End Sub This writes the array to the end of column A. HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
bobbo, thank you, your suggestions are most helpful. It's helped to to
figure some of this out. Question: how do i get the row to add to the 1st activecell that is empty. It always goes down a row??/ i have tried for i = 0 to 9, any help would be appreciated. Private Sub CommandButton2_Click() 'insert row Dim i As Integer ActiveSheet.Activate Cells(8, 1).Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True For i = 1 To 9 ActiveCell.Offset(i, 0).Value = arrProduct(i) ActiveCell.Offset(i, 1).Value = arrBankID(i) ActiveCell.Offset(i, 2).Value = arrProject(i) ActiveCell.Offset(i, 3).Value = arrServType(i) ActiveCell.Offset(i, 8).Value = arrAmount(i) With ActiveCell.Offset(i, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With Next Call UserForm_Initialize 'clear the form End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
FurRelKT wrote: bobbo, thank you, your suggestions are most helpful. It's helped to to figure some of this out. Question: how do i get the row to add to the 1st activecell that is empty. It always goes down a row??/ i have tried for i = 0 to 9, any help would be appreciated. Private Sub CommandButton2_Click() 'insert row Dim i As Integer ActiveSheet.Activate Cells(8, 1).Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True For i = 1 To 9 ActiveCell.Offset(i, 0).Value = arrProduct(i) ActiveCell.Offset(i, 1).Value = arrBankID(i) ActiveCell.Offset(i, 2).Value = arrProject(i) ActiveCell.Offset(i, 3).Value = arrServType(i) ActiveCell.Offset(i, 8).Value = arrAmount(i) With ActiveCell.Offset(i, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With Next Call UserForm_Initialize 'clear the form End Sub try changing this bit of code For i = 1 To 9 ActiveCell.Offset(i -1, 0).Value = arrProduct(i) ActiveCell.Offset(i -1, 1).Value = arrBankID(i) ActiveCell.Offset(i -1, 2).Value = arrProject(i) ActiveCell.Offset(i -1, 3).Value = arrServType(i) ActiveCell.Offset(i -1, 8).Value = arrAmount(i) With ActiveCell.Offset(i-1, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With Next the 0 to 9 did not work because you probably declared your arrays from 1 to 9 so arrproduct(0) and etc. would be empty. So then the first element of the array would be associated with a row offset value of 1. Arrays are like tables so you can declare them with rows and columns. So you could declare arrForm(1 to 9, 1 to 5) basically a table with nine rows and five columns. Then you could call the elements like so arrForm(1,1) . This might just complicate your current project but may be invaluable to a future one. HTH |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
Arrays are
like tables so you can declare them with rows and columns. So you could declare arrForm(1 to 9, 1 to 5) basically a table with nine rows and five columns. Then you could call the elements like so arrForm(1,1) . This might just complicate your current project but may be invaluable to a future one. I would like to understand this more when you have some time to explain this. I am continuing to build on this project. now i have more questions i hope that you will have time to suggest some things to me. Private Sub CommandButton6_Click() '////this is the test commandbutton Dim rng As Range Dim myStr As String Dim fAddr Dim i As Integer myStr = "Total Primary Tasks" Set rng = Cells.Find(myStr, _ LookIn:=xlValues, lookat:=xlWhole) If Not rng Is Nothing Then fAddr = rng.Address rng.Select '< now i want to loop until the cells has values again...?? not sure how to do that '< since i found the cell thats not empty first, then need to go up again and find the '< cells that has some data in it, then select the cell down one that is empty to put the < data in..it gets me confused.. Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(-1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True '< for this part, it's working... however, when there is nothing in say values.. 4-9, '< it erases the contents of the cells?? lets say we enter 2 rows only and not 9. the '< loop still goes to 9 so, if there is only 6 rows between myStr and the available row '< that we put the data in, myStr gets wiped out (cleared) of it's cell value, because '< there is no value in the rest of the values (arrProduct(i), etc...). how can i skip it '< so it's doesn't put in a blank value, therefore erasing myStr.value??? For i = 1 To 9 ActiveCell.Offset(i - 1, 0).Value = arrProduct(i) ActiveCell.Offset(i - 1, 1).Value = arrBankID(i) ActiveCell.Offset(i - 1, 2).Value = arrProject(i) ActiveCell.Offset(i - 1, 3).Value = arrServType(i) ActiveCell.Offset(i - 1, 8).Value = arrAmount(i) With ActiveCell.Offset(i - 1, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With Next Else MsgBox "cannot find value" End If End Sub Thanks again for all your help, i really appreciate it. Keri~ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
Bobbo, Well i actually got the loops working now... just need help with
the second part: basically, if there are not values in the array to add to the list, STOP the array from continueing to add blank values to the rows... Again, thanks for any help you can provide. I really appreciate it. Private Sub CommandButton6_Click() '////this is the test commandbutton Dim rng As Range Dim myStr As String Dim fAddr Dim i As Integer myStr = "Total Primary Tasks" Set rng = Cells.Find(myStr, _ LookIn:=xlValues, lookat:=xlWhole) If Not rng Is Nothing Then fAddr = rng.Address rng.Select If IsEmpty(ActiveCell) = False Then MsgBox "not empty" ActiveCell.Offset(-1, 0).Select Do If IsEmpty(ActiveCell) = True Then ActiveCell.Offset(-1, 0).Select End If Loop Until IsEmpty(ActiveCell) = False ActiveCell.Offset(1, 0).Select '< for this part, it's working... however, when there is nothing in say values.. 4-9, '< it erases the contents of the cells?? lets say we enter 2 rows only and not 9. the '< loop still goes to 9 so, if there is only 6 rows between myStr and the available '< row '< that we put the data in, myStr gets wiped out (cleared) of it's cell value, because '< there is no value in the rest of the values (arrProduct(i), etc...). how can i skip it '< so it's doesn't put in a blank value, therefore erasing myStr.value??? For i = 1 To 9 ActiveCell.Offset(i - 1, 0).Value = arrProduct(i) ActiveCell.Offset(i - 1, 1).Value = arrBankID(i) ActiveCell.Offset(i - 1, 2).Value = arrProject(i) ActiveCell.Offset(i - 1, 3).Value = arrServType(i) ActiveCell.Offset(i - 1, 8).Value = arrAmount(i) With ActiveCell.Offset(i - 1, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With Next End If Else MsgBox "Cannot find [Total Primary Tasks] value" End If End Sub thanks again, Keri~ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
FurRelKT wrote: Bobbo, Well i actually got the loops working now... just need help with the second part: basically, if there are not values in the array to add to the list, STOP the array from continueing to add blank values to the rows... Again, thanks for any help you can provide. I really appreciate it. Private Sub CommandButton6_Click() '////this is the test commandbutton Dim rng As Range Dim myStr As String Dim fAddr Dim i As Integer myStr = "Total Primary Tasks" Set rng = Cells.Find(myStr, _ LookIn:=xlValues, lookat:=xlWhole) If Not rng Is Nothing Then fAddr = rng.Address rng.Select If IsEmpty(ActiveCell) = False Then MsgBox "not empty" ActiveCell.Offset(-1, 0).Select Do If IsEmpty(ActiveCell) = True Then ActiveCell.Offset(-1, 0).Select End If Loop Until IsEmpty(ActiveCell) = False ActiveCell.Offset(1, 0).Select '< for this part, it's working... however, when there is nothing in say values.. 4-9, '< it erases the contents of the cells?? lets say we enter 2 rows only and not 9. the '< loop still goes to 9 so, if there is only 6 rows between myStr and the available '< row '< that we put the data in, myStr gets wiped out (cleared) of it's cell value, because '< there is no value in the rest of the values (arrProduct(i), etc...). how can i skip it '< so it's doesn't put in a blank value, therefore erasing myStr.value??? For i = 1 To 9 ActiveCell.Offset(i - 1, 0).Value = arrProduct(i) ActiveCell.Offset(i - 1, 1).Value = arrBankID(i) ActiveCell.Offset(i - 1, 2).Value = arrProject(i) ActiveCell.Offset(i - 1, 3).Value = arrServType(i) ActiveCell.Offset(i - 1, 8).Value = arrAmount(i) With ActiveCell.Offset(i - 1, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With Next End If Else MsgBox "Cannot find [Total Primary Tasks] value" End If End Sub thanks again, Keri~ There are two ways of doing this first I will give you the quick and dirty way. I used the example userform that I used earlier. Change the commandbutton two procedure to Private Sub CommandButton2_Click() Dim dest As Range Dim i As Integer Set dest = ActiveSheet.Range("a1") For i = 1 To 5 If myarray(i) = Empty Then Exit For Else dest.Offset(i - 1, 0).Value = myarray(i) End If Next End Sub The if statement exits the for next loop when the array does not have a value. I will write and test the more complex way and post it shortly. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
bobbo, your great. I added the code like this... and it works
correctly. yes, whenever you have some time, i really want to know how you could use the 2 dim array. like you suggested earlier. I have really found all your suggestions very helpful and am so grateful. For i = 1 To 9 If arrProduct(i) = Empty Then Exit For Else ActiveCell.Offset(i - 1, 0).Value = arrProduct(i) ActiveCell.Offset(i - 1, 1).Value = arrBankID(i) ActiveCell.Offset(i - 1, 2).Value = arrProject(i) ActiveCell.Offset(i - 1, 3).Value = arrServType(i) ActiveCell.Offset(i - 1, 8).Value = arrAmount(i) With ActiveCell.Offset(i - 1, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With End If Next Keri~ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
bobbo, again thankyou.
another question: I also would like to keep one blank row between the myStr and the empty row (they have the formulas). I have found a way to add the rows, while still keeping the formulas, by searching this site. So the problem is, i want to keep at least one row between, then if there insn't enough 'empty' rows left to input the rows, pop up the question of how many rows to add.... OR another thought, keep the one row between, if there isn't enough rows add another row to the sheet (as many as what is needed for the input), but copy the formula's from the above row...that seems like a better solution to me. what you think? additionally, by your suggestion of using the arrForm(1 to 20,1 to 5). can i also make sure that the columns in the listbox are filled using the columns? I am not sure how this works. listbox1.ListColumn = 5 to get the result like this, can it be done?: product | bankid | project | servtype | amount 1 -- ABC | ABCD | theproj | cross charge | 100.00 2 -- DEF | DEFG | another | vacation | 40.00 instead of what it is now... 1 -- ABC ABCD theproj cross charge 100.00 2 -- DEF DEFG another vacation 40.00 using the code: lbxResults.AddItem (counter & " -- " & cboProduct.Value & " " & cboBankID.Value & _ " " & cboProject.Value & " " & cboServType.Value & " " & txtAmount.Value) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a user form to add data
FurRelKT wrote: bobbo, again thankyou. another question: I also would like to keep one blank row between the myStr and the empty row (they have the formulas). I have found a way to add the rows, while still keeping the formulas, by searching this site. So the problem is, i want to keep at least one row between, then if there insn't enough 'empty' rows left to input the rows, pop up the question of how many rows to add.... OR another thought, keep the one row between, if there isn't enough rows add another row to the sheet (as many as what is needed for the input), but copy the formula's from the above row...that seems like a better solution to me. what you think? additionally, by your suggestion of using the arrForm(1 to 20,1 to 5). can i also make sure that the columns in the listbox are filled using the columns? I am not sure how this works. listbox1.ListColumn = 5 to get the result like this, can it be done?: product | bankid | project | servtype | amount 1 -- ABC | ABCD | theproj | cross charge | 100.00 2 -- DEF | DEFG | another | vacation | 40.00 instead of what it is now... 1 -- ABC ABCD theproj cross charge 100.00 2 -- DEF DEFG another vacation 40.00 using the code: lbxResults.AddItem (counter & " -- " & cboProduct.Value & " " & cboBankID.Value & _ " " & cboProject.Value & " " & cboServType.Value & " " & txtAmount.Value) I think your best bet for dynamically inserting rows would be to name the range that you want to insert rows above and if the row of where the userform writes is one above it insert the row. The other stuff I am not quite sure off yet. I am still a novice with this stuff because I have only been learning it for a little more than a year. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User form to sort data | Excel Programming | |||
displaying the user form data | Excel Programming | |||
user form - data range | Excel Programming | |||
Data from user form to certain cell | Excel Programming | |||
Reading Data from a User Form. | Excel Programming |