How to Create a Form for Search and Print through VBA in Microsoft Excel

In this article, we are going to learn how to create a user form for search and print through VBA in Microsoft Excel.

 

Let’s understand with a simple exercise:-

We have data in Sheet 1 in which column A contains Agent Id, column B contains Name, Column C Address, column D city, column E region, column F country and column G contains postal zip.

image 1

 

In the next sheet (sheet 2), we want to retrieve the details according to the below-mentioned image:-

image 2

 

If we search the Agent Id, then all details should appear in the range A11:D11. And then give the printout.

Follow below given steps to Search and Print the details:-

  • Press the key F11 to open the Visual Basic Editor
  • Insert the module to write the code for search button
  • Write the below-mentioned code:-

 

Sub Searchdata()
Dim Lastrow As Long
Dim count As Integer

Lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row

    For X = 2 To Lastrow
        If Sheets("Data").Cells(X, 1) = Sheet3.Range("B3") Then
            Sheet3.Range("A11") = Sheets("Data").Cells(X, 1)
            Sheet3.Range("B11") = Sheets("Data").Cells(X, 2)
            Sheet3.Range("C11") = Sheets("Data").Cells(X, 3) & " " & Sheets("data").Cells(X, 4) _
             & " " & Sheets("data").Cells(X, 5) & " " & Sheets("Data").Cells(X, 6)
            Sheet3.Range("D11") = Sheets("Data").Cells(X, 7)
        End If
    Next X
End Sub

 

Code Explanation:- First, we will define the variables and then we have defined which row will be the last row, and then we have defined what and where the Agent Id will be searched and then how the range will be updated for the searched Agent ID.

Now, we will assign this macro to the Search button.

  • Right click on the button
  • Click on Assign Macro
  • Here, our macro subject is Searchdata, click on searchdata and then click on OK

Run the Macro:-

  • To run the macro, click on Search button
  • If the Agent Id will be there then details will get updated otherwise details will not appear

To give the print out, assign the below-mentioned macro:-

  • Insert the button from Developer tab>Insert>Button(form control)
  • Write the macro in the same model where we have written the macro for Searchdata, with the subject name of Printout

 

Sub PrintOut()    
    Sheet3.Range("A1:D12").PrintPreview
    Sheet3.Range("A1:D12").PrintOut
End Sub

 

  • Assign the macro to button as we have assigned to searchdata

Image_SearchData

 

In this way, we can search the data and then can give the printout to the searched details through VBA in Microsoft Excel.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube