Excel VBA...please help me..??

[clara]

Dear experts,

I really need your help please...In Excel, I have Sheet1 (as a BOOK), Sheet2 (as a PEN) and Sheet3 (as a Payment). When we open this file, it will show the pop up first which contain as follow: Select type: BOOK or PEN, then enter the code number..For instance, if I choose BOOK, then it will fill all information from another SHEET (Sheet1 / Book) such as Code, Description and Price in the Sheet3 (Payment)....Has anyone knows how to do that please?

To make it clear, I put an attachment for this.

I really appreciate for your helps...thanks

Clara

[direktoren]

Hi and welcome...

Just to make sure I understand you correctly. When you open your workbook you want a userform to pop-up. From this userform you want to choose either PEN or BOOK and then enter a code (some sort of registration number?) and when executed what should it do?

a) Find a match for the code in Sheet BOOK, and then copy it to Sheet PAYMENT

or b) Find a match for the code in Sheet PAYMENT, and then copy it to Sheet BOOK?

Just to make sure

cheers
Nick

[direktoren]

Hi again - made up a little something

Since i'm at work i can't open your attached zip file so i can't see what your sheet looks like, but

If you make a userform on your sheet, and provides it with autoopen via the ThisWorkbook, then the following will work:

Userform should have the following: 2 optionbuttons, one named optbook and one named optpen. 1 textbox named txtcode and 1 commandbutton named cmdgo.

double click the commandbutton to get to the private sub for cmdgo_click and paste the following:

Private Sub CmdGo_Click()
Dim i As Single
Dim j As Single
Dim bSheet As Worksheet
Dim pSheet As Worksheet
Dim ySheet As Worksheet
 
    Set bSheet = ActiveWorkbook.Worksheets("BOOK")
    Set pSheet = ActiveWorkbook.Worksheets("PEN")
    Set ySheet = ActiveWorkbook.Worksheets("PAYMENT")
 
If OptBook = True Then
    For i = 1 To 100
        If TxtCode.Value = bSheet.Cells(i, 1).Value Then
            bSheet.Activate
            bSheet.Rows(i).EntireRow.Copy
            ySheet.Activate
            ySheet.Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial
        End If
    Next i
End If
If OptPen = True Then
    For j = 1 To 100
        If TxtCode = pSheet.Cells(j, 1).Value Then
            pSheet.Activate
            pSheet.Rows(j).EntireRow.Copy
            ySheet.Activate
            ySheet.Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial
        End If
    Next j
End If
End Sub

For this to work there is some things you shold be aware of - in your payment sheet, I take it that you have a header - this should be placed in row 2 (row 1 should be empty). I'm assuming that your code is in column A (this should be formatted as text). Also my for...next statement is limited to 100 rows, just correct this if necessary...

I take it that you know basic VBA (kind like me), so then you should be able to fit it to meet your needs...

cheers
nick