Userform VlookUp

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Userform VlookUp

Post by adam »

Hi Anyone

I'm using a form in Excel 2007 which I have a textbox name as txtSerialNo.

What I want is the serial number to appear in the textbox txtRequestNo & txtSerialNo when I type the serial number in the txtbox.

I hope I have made the question clear.

I know this would be simple. But I couldn't figure it out for myself.

Thanks in advance.

Regards
Adam
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

I'm not sure I understand correctly. Do you want to concatenate the values of txtRequestNo and txtSerialNo in a third text box, say txtConcatenate? If so, you can use the After Update event of txtSerialNo:

Code: Select all

Private Sub txtSerialNo_AfterUpdate()
  Me.txtConcatenate= Me.txtRequestNo & " - " & Me.txtSerialNo
End Sub
Do the same for txtRequestNo.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

I don't want a third Textbox. What I want is the value that I enter into the textbox with name "serial number" to appear on both the serial number box and request number box.

Suppose I write Serial number as 0001 I want the request number also automatically be 01 when I press the tab.
if I write 0025. I want the request number to be 25.

I hope I have made the question clear now.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

Ok, use this then:

Code: Select all

Private Sub txtSerialNo_AfterUpdate()
  Me.txtRequestNo = Right(Me.txtSerialNo, 2)
End Sub
Last edited by HansV on 14 Mar 2010, 21:28, edited 1 time in total.
Reason: to correct mistake - thanks to Steve (sdckapr) for pointing it out!
Best wishes,
Hans

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Userform VlookUp

Post by sdckapr »

Shouldn't that be:
Me.txtRequestNo = Right(Me.txtSerialNo,2)

Steve

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

Your code works the way as I want. Thanks for the help guys
In the same form I have a field to enter the Customer ID txtCustomerID.
What I want is when I enter the customer ID, the fields’ txtAddress, txtname, txtage, cbosex to be automatically filled by data taken from the fields (A6: G1048576)
of the sheet “PatientMasterList”

In the PatientMasterList;
Name is in column 2
Address is in column 3
Age in column 5
Sex in column 6

Regards
Adam
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

You'd use code like this:

Code: Select all

Private Sub txtCustomerID_AfterUpdate()
  Dim rng As Range
  If Me.txtCustomerID = "" Then
    Me.txtName = ""
    Me.txtAddress = ""
    ...
  Else
    Set rng = Worksheets("PatientMasterList").Range("A6:G1048576")
    Me.txtName = Application.VLookup(Me.txtCustomerID, rng, 2, False)
    Me.txtAddress = Application.VLookup(Me.txtCustomerID, rng, 3, False)
    ...
  End If
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

Thanks Hans. The code Works.
I have a text box in the form to write the bill No txtBillNo.

the format I use to write the bill number is as 001/10 where 001 is the serial number and 10 is the year.

I want the bill number to appear as 0001/10 when I write 1, 0002/10 when I write 2.

Also I have a "form serial number" in the form which I want the number to be in the format 0001 which keeps on changing sequentially when I press the save command button in the form.

I would be happy if you could help me with the above mentioned.

Thanks in advance.

regards
Adam
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

Add the following code to the txtSerialNo_AfterUpdate event procedure:

Code: Select all

  Me.txtBillNo = Format(Me.txtSerialNo, "0000") & "/" & Format(Year(Date), "yy")
You'll have to store the form serial number somewhere - in a cell on a worksheet, or in a text file, for example.
Let's say that you store it in cell A1 on a sheet Data (which may be hidden). Add the following code to the On Click event procedure of the save button:

Code: Select all

  With Worksheets("Data").Range("A1")
    .Value = .Value + 1
    Me.txtFormSerialNo = Format(.Value, "0000")
End With
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

Where should I place the code provided by you to change the serial number automatically after each save. following is the code I use under Save command

Code: Select all

      With Worksheets("Data").Range("A1")
        .Value = .Value + 1
        Me.txtFormSerialNo = Format(.Value, "0000")
    End With

Code: Select all

Private Sub cmdSave_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("NewSheet")

'find  first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a Customer ID
If Trim(Me.txtCustomerID.Value) = "" Then
  Me.txtCustomerID.SetFocus
  MsgBox "Please enter Customer ID"
  Exit Sub
End If

    If Not IsDate(Me.txtBillDate.Value) Then
        MsgBox "The Bill Date box must contain a date.", vbExclamation, "Service Memo"
        Me.txtBillDate.SetFocus
        Exit Sub
    End If
    
        If Not IsDate(Me.txtReceiptDate.Value) Then
        MsgBox "The Receipt Date box must contain a date.", vbExclamation, "Service Memo"
        Me.txtReceiptDate.SetFocus
        Exit Sub
    End If
    
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCustomerID.Value
ws.Cells(iRow, 2).Value = Me.txtSerialNo.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value
ws.Cells(iRow, 4).Value = Me.txtAge.Value
ws.Cells(iRow, 5).Value = Me.txtAddress.Value
ws.Cells(iRow, 6).Value = Me.txtUserID.Value
ws.Cells(iRow, 7).Value = Me.txtReceiptDate.Value
ws.Cells(iRow, 8).Value = Me.cboBillUser.Value
ws.Cells(iRow, 9).Value = Me.txtBillDate.Value
ws.Cells(iRow, 10).Value = Me.txtReceiptNo.Value
ws.Cells(iRow, 11).Value = Me.txtBillNo.Value
ws.Cells(iRow, 12).Value = Me.txtRequestNo.Value
ws.Cells(iRow, 13).Value = Me.txtCode.Value
ws.Cells(iRow, 14).Value = Me.txtCategory.Value
ws.Cells(iRow, 15).Value = Me.txtDescription.Value
ws.Cells(iRow, 16).Value = Me.txtRate.Value
ws.Cells(iRow, 17).Value = Me.txtQty.Value
ws.Cells(iRow, 18).Value = Me.txtValue.Value
ws.Cells(iRow, 19).Value = Me.cboInsured.Value
ws.Cells(iRow, 20).Value = Me.CboPayType.Value
ws.Cells(iRow, 21).Value = Me.cboLocation.Value
ws.Cells(iRow, 22).Value = Me.cboPriceType.Value
ws.Cells(iRow, 23).Value = Me.cboSex.Value

'clear the data
Me.txtCustomerID.Value = ""
Me.txtSerialNo.Value = ""
Me.txtName.Value = ""
Me.txtAge.Value = ""
Me.txtAddress.Value = ""
Me.txtUserID.Value = ""
Me.txtReceiptDate.Value = ""
Me.cboBillUser.Value = ""
Me.txtBillDate.Value = ""
Me.txtReceiptNo.Value = ""
Me.txtBillNo.Value = ""
Me.txtRequestNo.Value = ""
Me.txtCode.Value = ""
Me.txtCategory.Value = ""
Me.txtDescription.Value = ""
Me.txtRate.Value = ""
Me.txtQty.Value = ""
Me.txtValue.Value = ""
Me.cboInsured.Value = ""
Me.CboPayType.Value = ""
Me.cboLocation.Value = ""
Me.cboPriceType.Value = ""
Me.cboSex.Value = ""
Me.txtCustomerID.SetFocus
End Sub
Regards
Adam
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

And also what I want with the bill Number is when I enter 1 in the textbox txtBillNumber to be 0001/10.

when I'm entering bill number by the code provided you, im getting 0001/05 instead of 0001/10. I dont want the billnumber to have any relationship with the serial number. I hope I have made my question clear

Regards
Adam
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

adam wrote:Where should I place the code provided by you to change the serial number automatically after each save.
I'd place it at the end of the macro, above End Sub. You should probably use txtSerialNo instead of txtFormSerialNo.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

Try

Code: Select all

Private Sub txtBillNo_AfterUpdate()
  Me.txtBillNo = Format(Me.txtBillNo, "0000") & "/" & Format(Date, "yy")
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

Your last cord works Thanks.

But there's a problem in one of the previous code.
If I write a customer ID that is not saved in the sheet; "PatientMasterList" and click the tab button I get the debug message highlighting the following line

Code: Select all

Me.txtName = Application.VLookup(Me.txtCustomerID, rng, 2, False)

Instead I could only enter the customer ID's that already exits in the PatientMasterList. How can I overcome this.

Aegards
Adam
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

I'd use a combo box from which the user can select only existing CustomerIDs.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

Your suggestion is correct. I forgot about the form that I have as the add new customer. Thanks for the reminder.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

can a print preview button be assigned to a userform so that the user can preview the form before printing
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

The user is already previewing the form, isn't it?
There is no preview feature for userforms.
You can print preview a worksheet though.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Userform VlookUp

Post by adam »

When I Hide the unwanted columns in my excel sheet, the size of the excel file increases from 300kb to up to 2Mb.

Can you specify a reason for this? Are there any alternatives for this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform VlookUp

Post by HansV »

Are you sure you're not doing anything else? Hiding columns shouldn't make a difference in the file size.
Best wishes,
Hans