We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel 2010 - Userform that will populate worksheet


oo7juk

Likes # 0

I'm looking to create a form that sits on a worksheet, where I can populate fields on the form, that populates a worksheet within the same workbook.

I have 3 sets of invoices and once scanned would like to enter invoice data, that will record the data. After a certain period of time 'field on userform' the 'hard copy' invoices will be disposed of. Hopefully the form I create can record this data for historic purposes and reduce storage. Once I have the data, would also like to run a basic report if possible.

I'm using excel 2010 and don't have access, nor can I get access to it, so excel is my only option.

I would like the form to consist of 11 fields and some of them to do particular things if possible.

Field 1 (ID No.): Would like this to generate an auto number, each time document is opened. Field 2 (Scandate): user would enter a date DD/MM/YYYY Field3 (Maildate): as above Field4 (Invoicetype): *see below Field5 (Batchname): user would enter a date DD/MM/YYYY hh:mm:ss Field6 (Number): user would enter a number Field7 (Anticipated disposal date): would like this field to automatically be calculated. This is 3 months from the "Scandate" re Field 2 Field8 (Actual disposal date); user would enter a date DD/MM/YYYY Field9 (Disposed By): user would enter their name. would it be possible to have a dropdown to select, maybe use excel's validation faeture? Field10 (A button to add a new record) Field11 (A button to close the form)

*(Invoice type): would it be possible to either have a dropdown within this field, with 3 options 1. Local, 2. Regional, 3. Other. The actual worksheet would be labelled as this and once select from the form would populate the spreadsheet. If not possible, could 2 additional buttons be created labelled, Local, etc and populate worksheet. If I had the later option, how would I know the correct "invoice type" was selected for input?

I'm not asking for someone to create the above for me, but just some help getting started, especially as I think some coding might need to be done. I have created a small userform before, only populating one worksheet, at the time was given some coding and managed to configure to my needs, with the help from people on this board.

Thanks in adavance for all your help and assistance.

Like this post
lotvic

Likes # 0

May I point you to click here mrexcel.com forum that will give more indepth help for what you want to achieve.

Like this post
tasslehoff burrfoot

Likes # 0

Field 1 - you'll need to store the most recent invoice number somewhere on the spreadsheet and increment this field by one when the form loads, normally quite simple, you would check the last filled column and grab the relevant cell value. If I understand correctly though this would be cumbersome because different invoice types are on different sheets? Easier to store it in a cell somewhere, increment by 1 and then set the value of the field to the value of the cell. Or something similar. I imagine you would want this to be non user editable.

Field 7 - let the spreadsheet do the calculation and pull the cell value for the field.

Field 9 - yes you can use a drop down. Either list the names on a worksheet somewhere and use that for the data source or do it programmatically -

with listbox1 .additem ("name1") .additem ("name2") end with

field 10 - write the record to the spreadsheet; preface the range with the worksheet eg

Worksheets(ListBox1.Value).Range("A1").Value = ListBox1.Value

would copy the value of listbox1 to cell A1 of the spreadsheet with the value of listbox1 for its name - make sense?

field 11 - the code for the button would be unload me

Hope that's enough to get you started, sorry if I've missed something or made an error

Like this post
tasslehoff burrfoot

Likes # 0

oops any listbox1.value should be listbox1.text

Like this post
oo7juk

Likes # 0

Hi tasslehoff burrfoot,

Thanks for your prompt reply.

I have a spreadsheet set-up at the moment utilsing a userform/code. My plan was to basically make a copy and amend for my new spreadsheet.

Within my copy I copied a text box and pasted to my sheet, I then entered the code, but excel did'nt like it.

Do I need to add a new text box, rather than copy. In relation to populating the worksheet with the contents of new text box, how do I do that.

The copy spreadsheet has 3 text boxes and I think this is the code for them below. I've tried to edit code by entering the following:

Edited code:

ws.Cells(iRow, 1).Value = CDate(Me.txtcallv.Value) ws.Cells(iRow, 2).Value = Me.txtcallv2.Value ws.Cells(iRow, 3).Value = Me.txtcallv3.Value ws.Cells(iRow, 4).Value = Me.txtcallv3.Value (I added this row)

Me.txtcallv.Value = "" Me.txtcallv2.Value = "" Me.txtcallv3.Value = "" Me.txtcallv4.Value = "" ( I added this row) Me.txtcallv.SetFocus

Full coding:

Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("VolumeAnalysis 2012")

iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row

If Trim(Me.txtcallv.Value) = "" Then Me.txtcallv.SetFocus MsgBox "Please enter call data" Exit Sub End If

ws.Cells(iRow, 1).Value = CDate(Me.txtcallv.Value) ws.Cells(iRow, 2).Value = Me.txtcallv2.Value ws.Cells(iRow, 3).Value = Me.txtcallv3.Value

Me.txtcallv.Value = "" Me.txtcallv2.Value = "" Me.txtcallv3.Value = "" Me.txtcallv.SetFocus

End Sub

Private Sub cmdClose_Click() Unload Me End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Thank you

Like this post
tasslehoff burrfoot

Likes # 0

I don't see anything obviously wrong - I take it the bits you've edited are causing an error? What is the error message?

Have you changed the names of the new textboxes?

Like this post
oo7juk

Likes # 0

Hi,

I managed to get the data from the textbox to populate spreadsheet. It was the names, as you suggested.I just changed the number from the textbox i copied, rather than the whole name.

Can I pick your brains re field 1 please. I've decided just to populate one workbook rather than 3. All I want is for each time the userform is opened it populates the text box with an automatic number, e.g. first time its opened the textbox displays 1 and this populates spreadsheet too. Next I open userform the textbox displays 2 and so on.

Like this post
tasslehoff burrfoot

Likes # 0

Presumably, the records are listed by that number in ascending order?

You already have the code to find the last one stored in the iRow variable so something like

NewRow = ws.cells(iRow, 1) +1 me..value = NewRow

A word to the wise - the code is a little clumsy, which doesn't necessarily matter as it's only quite short, but if it's going to grow it might be a good idea to tidy it up a bit

Like this post
oo7juk

Likes # 0

Hi,

Yes, they will be after I close the form and the data is populated on the spreadsheet.

I inserted the code (see below) on top of existing code, but the textbox is not populating with a number. I have 2 records on the spreadsheet at the moment, so after applying code I was hoping the textbox would display the number "3".

NewRow = ws.cells(iRow, 1) +1 me..value = NewRow iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row

I also tried adding the name of the textbox (see below)

iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row NewRow = ws.cells(iRow, 1) +1 me.txtcallv.value = NewRow

It might be me not explaining what I want. If you want I can send you code and/or full code. Then I could post revisons, etc.

Thanks again.

Like this post
oo7juk

Likes # 0

Oops, meant "send you workbook and or full code", thanks.

Like this post
tasslehoff burrfoot

Likes # 0

yeah, end me an email. Bit busy so may not be able to take a look until tomorrow

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Best Christmas 2014 UK tech deals, Boxing Day 2014 UK tech deals & January sales 2015 UK tech...

IDG UK Sites

LED vs Halogen: Why now could be the right time to invest in LED bulbs

IDG UK Sites

Christmas' best ads: See great festive spots studios have created to promote themselves and clients

IDG UK Sites

Stop running out of cellular data on your iPhone, see which apps use the most data