User Form

[a m spock]

I need user to interactively enter sales data into six non-contiguous columns
each of which is a named range i.e. Date, Quantity, Rate, Net Amount,
SalesTax, Service
Tax. The rest of the columns are calculated from this data and are locked.
Data is filled as each transaction takes place.

Afer entry user should review and confirm before data is saved. Can someone
please help! I had posted this earlier but no success so far. Someone did
say it can be done. But not how!!

 

[Mike H]

Hi,

I don't know how far along the road you are in your goal but lets start from
scratch. Create a userform and put the textboxes and a command button on it.
Right click the command button and view code and paste the code below in.

You now have some (not a lot of) functionality. When the command button is
pressed the user is asked whether thay want to commit the data and if OK is
pressed the data in the 3 textboxes are written to the first empty cell in 3
columns.

There are still challenges for you. For example there may be nothing in the
textboxes or the data may be wrong and you need to write tests for this but I
hope this gets you moving along.

Private Sub CommandButton1_Click()
response = MsgBox("Are you sure you want to commit the data?", vbOKCancel)
If response = vbCancel Then GoTo getmeout

With Sheets("Sheet2")
lastrowC = .Cells(Rows.Count, "C").End(xlUp).Row
lastrowF = .Cells(Rows.Count, "F").End(xlUp).Row
lastrowG = .Cells(Rows.Count, "G").End(xlUp).Row
.Range("C" & lastrowC + 1).Value = TextBox1.Text
.Range("F" & lastrowF + 1).Value = TextBox2.Text
.Range("G" & lastrowG + 1).Value = TextBox3.Text
End With
getmeout:
Unload Me
End Sub



Mike

 

[Mike H]

Hi,

I should have added that my example is for three textboxes only but it
should be fairly intuative what you need to do to increase this to the six
you require.


Mike

 

[a m spock]

mike

many thanks. i am afraid i have no idea how to create a userform. so i am i
don't know many steps behind where you start. can you help an ignorant novice?

spock