using a user form to add data

[FurRelKT]

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~

 

[bobbo]

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

 

[FurRelKT]

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

 

[bobbo]

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

 

[FurRelKT]

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~

 

[FurRelKT]

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~

 

[bobbo]

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.

 

[FurRelKT]

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~

 

[FurRelKT]

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)

 

[bobbo]

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.