Userform VlookUp

[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

[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.

[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.

[HansV]

Ok, use this then:

Code: Select all

Private Sub txtSerialNo_AfterUpdate()
  Me.txtRequestNo = Right(Me.txtSerialNo, 2)
End Sub

[sdckapr]

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

Steve

[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

[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

[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

[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

[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

[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

[HansV]

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.

[HansV]

Try

Code: Select all

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

[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

[HansV]

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

[adam]

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

[adam]

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

[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.

[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?

[HansV]

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