Using a userform to update receipts in sales database

[kenny9002]

We use an Excel UserForm to update a dynamic Sales database.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
A) The UserForm has the following textboxes:


<o></o>

• Invoice No.
• Invoice Date
• Gross Amount
• Vat Amount
• Net Amount
• Type in ‘Unpaid’ (In this box we type in the word ‘Unpaid’ cos we find it helpful for Pivot Table analysis.<o></o>

B) TO UPDATE THE DATABASE WITH SALES<o></o>
When a Sales Invoice is generated, we enter the above details in the UserForm (in respect of each invoice) to enable us to update the Sales database.
<o></o>
To do that, we use the following codes:

[FONT=Arial]Private Sub cmdAdd_Click()<o:p></o:p>[/FONT]
[FONT=Arial]Dim iRow As Long<o:p></o:p>[/FONT]
[FONT=Arial]Dim ws As Worksheet<o:p></o:p>[/FONT]
[FONT=Arial]Set ws = Worksheets("SalesData")<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Arial]'find first empty row in database<o:p></o:p>[/FONT]
[FONT=Arial]On Error Resume Next<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Arial]iRow = ws.Cells(Rows.Count, 1) _<o:p></o:p>[/FONT]
[FONT=Arial].End(xlUp).Offset(1, 0).Row<o:p></o:p>[/FONT]
[FONT=Arial]<o:p></o:p>[/FONT]
[FONT=Arial]'check for Invoice Number<o:p></o:p>[/FONT]
[FONT=Arial]If Trim(Me.txtInv.Value) = "" Then<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtInv.SetFocus<o:p></o:p>[/FONT]
[FONT=Arial]MsgBox "Please Enter Sales Data or Click 'Close Form' to Exit", vbExclamation, "Sales Data Entry"<o:p></o:p>[/FONT]
[FONT=Arial]Exit Sub<o:p></o:p>[/FONT]
[FONT=Arial]End If<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Arial]' Check user input<o:p></o:p>[/FONT]
[FONT=Arial]If Not IsDate(Me.txtDate.Value) Then<o:p></o:p>[/FONT]
[FONT=Arial]MsgBox "You Must enter a Date in the 'Date' Box.", vbExclamation, "Sales Data Entry"<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtDate.SetFocus<o:p></o:p>[/FONT]
[FONT=Arial]Exit Sub<o:p></o:p>[/FONT]
[FONT=Arial]End If<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Arial]'copy the data to the database<o:p></o:p>[/FONT]
[FONT=Arial]ws.Cells(iRow, 1).Value = Me.txtInv.Value<o:p></o:p>[/FONT]
[FONT=Arial]ws.Cells(iRow, 2).Value = Me.txtDate.Value <o:p></o:p>[/FONT]
[FONT=Arial]ws.Cells(iRow, 4).Value = Me.txtGross.Value<o:p></o:p>[/FONT]
[FONT=Arial]ws.Cells(iRow, 5).Value = Me.txtVat.Value<o:p></o:p>[/FONT]
[FONT=Arial]ws.Cells(iRow, 6).Value = Me.txtNet.Value<o:p></o:p>[/FONT]
[FONT=Arial]ws.Cells(iRow, 7).Value = Me.txtDP<o:p></o:p>[/FONT]
<o:p></o:p>
<o:p></o:p>
[FONT=Arial]'clear the data<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtInv.Value = ""<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtDate.Value = ""<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtGross.Value = ""<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtVat.Value = ""<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtNet.Value = ""<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtDP.Value = ""<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Arial]End Sub<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Arial]Private Sub cmdClose_Click()<o:p></o:p>[/FONT]
[FONT=Arial]  Unload Me<o:p></o:p>[/FONT]
[FONT=Arial]End Sub<o:p></o:p>[/FONT]

C) TO UPDATE DATABASE WITH RECEIPTS:
When payment for any of the invoices is received, and in order to update the database with such receipt, we want to be able to key into a UserForm only two pieces of data, namely:


<o></o>

• The Invoice No.
• The Date Paid.

<o></o>
and plan to use a VBA code to find, in the database, the paid Invoice No. , and find the cell against that invoice number containing the word ‘Unpaid’, and then replace the word ‘Unpaid’ with the date the invoice is paid (in “dd/mm/yyyy” format).
<o></o>
We currently do the receipts updating in a cumbersome way – listing on a separate excel sheet (named ‘Receipts’) the ‘invoice numbers’ & ‘the date paid’ in columns A & B, respectively, for all paid invoices. We then start clicking each date with a mouse to achieve the updating. The VBA we use for this purpose is as follows:

Code for RECEIPTS: in Worksheet - Change

[FONT=Arial]Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>[/FONT]
[FONT=Arial]   Dim r As Range<o:p></o:p>[/FONT]
[FONT=Arial]   If Target.Column = 2 Then<o:p></o:p>[/FONT]
[FONT=Arial]       Set r = Sheets("data table").Range("a:a").Find(Target.Offset(, -1))<o:p></o:p>[/FONT]
[FONT=Arial]       If Not r Is Nothing Then<o:p></o:p>[/FONT]
[FONT=Arial]          r.Offset(, 6) = Target<o:p></o:p>[/FONT]
[FONT=Arial]          Else<o:p></o:p>[/FONT]
[FONT=Arial]          MsgBox "invoice " & Target.Offset(, -1) & " is not found"   ' do whatever if invoice is not found<o:p></o:p>[/FONT]
[FONT=Arial]       End If<o:p></o:p>[/FONT]
[FONT=Arial]  End If<o:p></o:p>[/FONT]
[FONT=Arial]  End Sub<o:p></o:p>[/FONT]

Please, we want to be able to use a single excel UserForm for both ‘Sales Invoice Data Entry’ as explained in Paragraph (B) above and also to update the database with ‘Receipts’.
<o></o>
Please can someone help us with a vba ‘Receipts’ code that we can combine with the Sales Invoice Data Entry code in (B) above so that when we enter the ‘Invoice No. paid’ and the ‘Date Paid’ in the excel UserForm, the relevant Invoice No. paid will be found in the database and the cell for that invoice number containing the word ‘Unpaid’ will be replaced with the ‘date paid’.
<o></o>
If the two processes can be achieved using one UserForm, that will be good. I’ll be happy to re-design the current Sales Invoice Data Entry UserForm & change the textboxes listed in (A) above. If it is not possible to achieve this using one UserForm, we will be glad to use two different UserForms if we can get help with the vba code, please for the ‘Receipts’ update, please.
<o></o>
Thank you for your anticipated help.
<o></o>
<o>Kenny</o>

<o></o>

 

[baitmaster]

Hi Kenny, a couple of pointers that might help you

you could try using a multipage object to help separate two distinct sections of one userform. This allows you to have one tab for data entry, and another for data update, with all code still contained in the one place

You can identify in your database, the location of your target invoice number, using application.match([invoice number],[invoice list],0), which follows the same construct as Excel's Match function. Once you have the location, it is simple to update. You will need an error handler in case it doesn't find the target, and to test if the value to be changed is "unpaid" or not.

You are using text boxes for all your data entry, you should investigate the other types of control available to you. For example:

• you could consider either a listbox or a combobox as a way of holding all your invoice numbers in the userform and working with them there: load them in when form initialises or new data is entered
• Tools > references > microsoft calendar control, will allow access to a calendar object, which is more robust than a text field for date entry, and looks better too

If you are using a form for multiple features, you might find it useful to set the form's "showmodal" property to False, as this allows you to do work on the worksheet, without closing the form

hope this helps

 

[kenny9002]

Hi Baitmaster,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thanks for your kind response. I like your ideas in your paragraphs 2 and 3.<o></o>
<o> </o>
I would prefer your suggestion/approach of using a multipage object to separate two sections of the Userform with a tab for data entry and another for data update. How do I do that, please? I’m willing to learn. You know, with Excel & VBA, I’m still not there yet – still learning.<o></o>
<o> </o>
Could you please take me through the steps to re-organise or recreate my userform to achieve your suggestion/approach.<o></o>
<o> </o>
As I indicated in my first post: – for the Sales Data Entry, the Userform requires the following textboxes:<o></o>

• Invoice No.<o></o>
• Invoice Date<o></o>
• Gross Amount<o></o>
• VAT<o></o>
• NET<o></o>
• Enter ‘Unpaid’ (In the database, the ‘Unpaid’ entered in this textbox appears in the ‘Date Paid’ column.<o></o>

<o></o>
And for the Receipts Data Entry (i.e. data update), the Userform requires the following textboxes:<o></o>

• Invoice No.<o></o>
• Date Paid<o> </o>

In your paragraph 3 regarding the location of the target Invoice No. – all the invoice numbers are shown in Column A of the database, and the word ‘Unpaid’ is located in a cell in Column G of the database. This column has the title ‘Date Paid’. The database columns and headings are as follows:<o></o>
<o> </o>

• Invoice Number - Column A<o></o>
• Invoice Date – Column B<o></o>
• Gross Amount – Column D<o></o>
• VAT Amount – Column E<o></o>
• NET Amount – Column F<o></o>
• Date Paid – Column G<o> </o>

(column C is used for a formula and therefore disregarded here)<o></o>
<o> </o>
I’ll be glad if you can, please take me through the steps of re-creating or reorganising my userform (using your suggested multipage object), PLUS the VBA codes to achieve your paragraphs 2 & 3 suggestions.<o></o>
<o> </o>
I have confidence that your idea is exactly what I’m looking for and will help me complete the task and achieve my aim.<o></o>
<o> </o>
Regarding your paragraph 4, I’ll use textboxes for now and explore and learn, in future, the other available controls you indicated (e.g. listboxes & comboboxes).<o></o>
I look forward to your further help on this.<o></o>
<o> </o>
Thanks.<o></o>
<o> </o>
Kenny<o></o>

 

[baitmaster]

To reorganise your form, a good start is to make some space. Drag the edges out so you have more area to work with

On your controls toolbox (VBA > View > Toolbox), you will see one called multipage. Add a multipage to your form

select all your other controls and simply drag them onto the first page of your multipage. Rename the page if you want, and change the caption - use the Properties window to achieve these changes

Add any other controls to the second page, and add any other pages as you want. Have a play, it's pretty simple and a useful tool for userforms

All your existing code should be able to stay as it is, its just the layout that is changing at the moment

Assuming you have, on page 2, added txtInvUpdate, txtPaidDate, and btnUpdate button, the following code will be along the right lines:

Private Sub btnUpdate_Click()
 
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("SalesData")
 
' check data entered
If txtInvUpdate = "" Then
    MsgBox "no invoice number"
    Exit Sub
End If
 
If txtPaidDate = "" Then
    MsgBox "no date"
    Exit Sub
End If
 
' find row
On Error GoTo errReport
iRow = Application.Match(txtInvUpdate, ws.Columns(1), 0)

' check cell contents and update
If ws.Cells(iRow, 7) = "unpaid" Then ws.Cells(iRow, 7) = txtPaidDate
 
Exit Sub
 
errReport:
Select Case iRow
    Case 0
        MsgBox "the invoice number could not be found in your data"
    Case Else
        MsgBox "an unknown error occurred. Check whether cell K" & iRow & " has been updated"
End Select
 
End Sub

This line may cause you problems:
iRow = Application.Match(txtInvUpdate, ws.Columns(1), 0)
because you are using text boxes. If you have invoice numbers in column A, then match won't spot them, because it is comparing a text string with a numeral and hence decides they are different. EIther convert them totext strings, e.g. by adding INV to the start, or convert txtInvUpdate to a long by using CLNG(txtInvUpdate)

 

[kenny9002]

Hi Baitmaster,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your help and for your brilliant idea and approach. I knew that if you had a look at the problem, you’d have a brilliant solution.<o></o>
<o></o>
I have followed your instructions and set up the multipage Userform. It looks very nice. I have a few questions, please:<o></o>
<o></o>
1. Renaming the Multipage – I renamed each of the pages as you instructed. Is it possible to make the fonts for the page names bigger and bold? I could not locate where to do that in the properties window.<o></o>
<o></o>
2. On page 2 of the Multipage, I have added the following text boxes:<o></o>
<o></o>
a) txtInvUpdate – for Invoice No.<o></o>
b) txtPaidDate – for Date Paid; and<o></o>
c) a command button (btnUpdate) – for Update<o></o>
<o></o>
Please may I know where (in which of the vba editor windows) I should copy and paste your code. With the multipage now being used, my existing code is now on the following vba editor window –<o></o>
<o></o>
Multipage 1 - Change<o></o>
<o></o>
Is it in the same window that I should paste your code?<o></o>
<o></o>
3. Concerning Command buttons – In order to re-name command buttons in the previous userform, I used the following:<o></o>
<o></o>
a) CmdAdd - for the ‘Add’ button<o></o>
b) CmdClose – for the ‘Close’ button.<o></o>
<o></o>
You suggested to name the ‘Update’ button on page 2 as ‘btnUpdate’. May I know if ‘btn’ functions the same as ‘Cmd’ in naming a command button.<o></o>
<o></o>
4. There is a ‘Close’ button on page 1 (i.e. the Sales Data Entry page). Can I also add a ‘Close’ button on page 2 (i.e. the Receipts Update page)? Meanwhile, I tried to do this, but it qwould not accept a second ‘Close’ button with the same name ‘CmdClose’. With your own idea, I changed the name of the second close button to ‘btnClose’. It accepted it. But I don’t know if this is allowed<o></o>
<o></o>
5. You added a comment as follows:<o></o>
<o></o>
“This line may cause you problems:
iRow = Application.Match(txtInvUpdate, ws.Columns(1), 0)
because you are using text boxes. If you have invoice numbers in column A, then match won't spot them, because it is comparing a text string with a numeral and hence decides they are different. EIther convert them totext strings, e.g. by adding INV to the start, or convert txtInvUpdate to a long by using CLNG(txtInvUpdate) “<o></o>
<o></o>
The Invoice Nos. in column A of the database are not only numeric. They are a combination of alphabet and numbers, eg. W1302. With this information, will the following line still cause problems?: iRow = Application.Match(txtInvUpdate, ws.Columns(1), 0)<o></o>
<o></o>
6. Date formatting: I want users of the form to be able to enter dates on the userform as e.g. 5/9/11 but when it goes into the database it should show as 05/09/2011 (i.e. in the dd/mm/yyyy format). Is this possible please, and may I know what code I should add (and to where in the vba code) in order to achieve this.<o></o>
<o></o>
7. When data is entered on the userform, they appear a bit tiny, and I s’poose, in a pre-determined font and font size. I would like to know if there is a code to change the fonts and font sizes when they appear on the data base. This particular one is not something urgent, although I would like to learn that also.<o></o>
<o></o>
Again, thanks very much for all your help.<o></o>
<o></o>
Kenny<o></o>

 

[baitmaster]

Is it possible to make the fonts for the page names bigger and bold?
- I don't believe so, never tried, and as you say, there's nowhere to do this in the properties window. Your users will be familiar with this type of form feature anyway though

my existing code is now on the following vba editor window – Multipage 1 - Change
- no, it isn't. It's still in the code module of the userform. The two drop downs at the top of the window relate to EVENTS. If you select new values, you will be taken to the applicable event. If that event does not exist, it will be automatically created. In this case, it says multipage1 - change, because the multipage was active when you hit F7 to go to the code (or double-clicked, etc)

May I know if ‘btn’ functions the same as ‘Cmd’ in naming a command button
- the name of any object does not affect how that object works. Everyone else in the world uses cmd for command button. I am different, I use btn. I'm not sure why, I just do. If I told you to use cmd, its probably some old code, or something I originally learnt from someone else. Code evolves...

There is a ‘Close’ button on page 1...
- comment above covers naming conventions. You can't have two objects with the same name, that is all. What I would do, is move general buttons back off the multipage, and have them sitting below it. That way it doesn't matter what page you are on, you use the same buttons for things like form close

With this information, will the following line still cause problems?
- No. It is only where you are using a text box (VBA sees the contents only as a text string), to search for other data types, such as Long, Integer, Date. In some cases it tries to decide what to do, but usually fails. In this case, having text and numbers in cells, means VBA sees text strings there too, so no problem

Data formatting
- this is a minefield. I suggest you use the date control which will handle this better. E.g., are you using US or UK date formats in your entered data? What if they write a date that doesn't exist? you need more error-handling, that's what. That said, you could use CDATE(txtPaidDate) to convert the value to a date, and manage cell formats in the worksheet

Fonts and sizes
- in textboxes these are controlled via the Font property. In the database, again use the worksheet settings to control appearance

 

[kenny9002]

Thanks for your kind clarifications. I have noted all your views and suggestions.

Concerning the 'Close' button, you suggested that I should 'move general buttons back off the multipage, and have them sitting below it". How do I do this, please?

With all these, I'll complete the task and get set to try it all out.

Thanks so much.

Kenny

 

[kenny9002]

Sorry, I missed out something in the post that I sent out a few minutes ago (the one before this):

Concerning where I should paste your code - is it correct to click on the page 2 'btnUpdate' botton, view code, and paste your code there?

Re the 'Date' format, are you suggesting I should change the name in the Properties window from 'txtPaidDate' to 'CDATE(txtPaidDate)'?

Thanks.

Kenny

 

[kenny9002]

Hi Baitmaster,

Please I'm sorry for the myriad of posts. I have sorted out the 'Close' button issue - moving it back off the multipage - sitting below. That is sorted. I'll await your advice on the other two points.

Thanks.

Kenny

 

[kenny9002]

Hi Baitmaster,

Please I hope you can find time to review the abridged database that I sent to you by email. I'm deeply sorry for all the bother.

Kenny